UPDATE QUERY PRODUCING RUN-TIME ERRORS

[Bill3129]

The following code continues to give me error messages. I have made several attempts to get it to work but I am not having any luck. I continue to get either run-time error 3044 or 3075.

Dim sqlUpdt As String

sqlUpdt = "UPDATE Correspondence_Log INNER JOIN Corresp_Log_Backup ON Correspondence_Log.CARD_NUMBER = Corresp_Log_Backup.CARD_NUMBER"
sqlUpdt = sqlUpdt & " SET Correspondence_Log.STATUS = Corresp_Log_Backup.STATUS"
sqlUpdt = sqlUpdt & " WHERE Correspondence_Log.REF_NUMBER = Corresp_Log_Backup.REF_NUMBER"
DoCmd.RunSQL sqlUpdt

BRIEF HISTORY:

I have two tables that need to be updated. Table1 is Monthly Problems and Table2 is Correspondence Log. The data will not permit me to assign a Primary Key. Would be unique identifiers are repeatedly used. This can't be avoided. What I have done is to set up a form that performs changes to Table1. The records from Table1 are appended in a temporary table (Corresp_Log Backup). I my code to use the records in the temporary table to Update the records in the Correspondence Log.

Any and all help will be greatly appreciated. Thank you.

 

[PHV]

The data will not permit me to assign a Primary Key
Isn't (CARD_NUMBER,REF_NUMBER) unique ?
If not you're definitively in trouble.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bill3129]

No. The tables in question are used to log information in regard to activity therefore the Card Numbers and Reference Numbers are often listed more than once. Is there any way to get around this?