Help With SQL Code and Access Database

[Dawn]

What I’m trying to do is get the field from the Audits table named AuditID to automatically carry over into the Audits Details table in the AuditID field


Name of Access Database: AUDITS
1st Table Name: Audits with the fields:
AuditID (Primary Key)
PlantLocation
Shift
Area
AuditDate
AuditTime

2nd Table: AuditDetails with the fields
DetailID (Primary key)
AuditID
RiskFactor
BCFactor
BCDesc
ESResolution


Below is the code:

"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"

[Neosoft Support]

Have you tried executing those SQL commands with DBPro's dbpExecSQL action?

What type of field is AuditID?

[Dawn]

I have tried to use that but I keep getting a missing quotations error. The AuditID field is the primarykey in the table named Audits.

[Neosoft Support]

Can you post the dbpExecSQL code you're using?

[Dawn]

"AUDITS" "UPDATE* "AuditDetails" INNER JOIN "Audits" ON AuditDetails.AuditID = Audits.AuditID Set AuditDetails.AuditID
= Audits.AuditID, AuditDetails.sAuditID = Audits.AuditID WHERE Audits.AuditID = "[AuditID]"

[Neosoft Support]

I'm not a SQL expert and some of those commands can be pretty difficult to get right. However, since double quotes are used as delimiters for NeoBook's scripting language, you can't use them in your SQL commands. Instead of double quotes, try using single quotes. For example:

dbpExecSQL "AddrBook" "SELECT * FROM Contacts WHERE State = 'NY'" ""

[Dawn]

Looks as though I'm stuck in neutral on this! I can't get anything to work.

[Neosoft Support]

You could manually populate the AuditDetails table. For example:

SetVar "[Audit.AuditDetails.DetailID]" "[Audit.Audit.AuditID]"

This will copy the contents of the Audit table's current record AuditID field to the AuditDetail table's current record DetailID field.

I'm sure it's possible to get your SQL code to work also, but without actually having access to your database, pub file, etc. it's difficult for us to make any specific suggestions.