Inconsistent Updating of records (access 2002 bug?)

[sammylou]

I have a form Create_Invoicing_Details which opens from another form Reports (table behind this is Report Details) and creates new Invoicing details. After the invoicing record has been saved (into a table called Invoicing Details) by clicking on a button (this table entry works fine) the following sql is supposed to update Report Details as follows by setting the field Invoice_details_setup to true and setting the field InvoiceID to equal that of the current form Invoice ID

Code as follows:
mysql3 = "UPDATE [Report Details] SET [Report Details].InvoiceID =" & InvoiceID & ", [Report Details].Invoice_details_setup = True WHERE ((([Report Details].ReportID)=[Forms]![Create_invoicing_details]![ReportID]));"
DoCmd.RunSQL (mysql3)

The problem with it is that sometimes it works fine but 7 times out of 10 it doesn't and I have to keep going into the background for the users and updating the Report Details table manually by ticking the field Invoice_details_setup to true and setting the field InvoiceID to equal the unique number that is stored in Invoicing Details table (i.e. linking the two records). Also when it doesn't update properly, the users can't see the linked Invoice number on the Reports form once they close the Create_Invoicing_Details and then they keep entering the Invoicing details two or three times, wich means I have to delete the extra records later from the Invoicing Details table!

This is baffling me as there is no logical reason why sometimes it works and sometimes it doesn't. Also this worked perfectly fine without error before I converted the Database to v2002 from v97. I am beginning to hate v2002 and would appreviate any advice

Thanks

Sam

 

[FoFa]

Ref - SET [Report Details].InvoiceID =" & InvoiceID

What is InvoiceID? I assume it is a numeric value, but from the appeqrnce of the code you gave, it must be a variable in your code. What is the source of it's value? Have you tried displaying the SQL statement to see if it is getting formatted properly? I usually throw a MSGBOX mysql3,,"Testing" in there before the execute of the sql statment to verify.
I have not had any issues with 2002 doing what you are stating. It always turns out to be something else.

 

[sammylou]

InvoiceID is a unique numeric number i.e. 4048 which I know is working OK because that number is the record ID in the Invoicing Details table which is being updated correctly. What it is not doing is storing 4048 in the InvoiceID field in the Report Details table, and ticking the field Invoice_details_setup to true (-1). So I know that the number is working OK it's just the relational field in table Report Details which links to the correct record in Invoicing Details table that is failing

Thanks