Synthax Error (1 Viewer)

Hi,
can someone help me with to check why this VBA synthax is wrong and give me the correct synthax to Update multiple records in 2 or more tables based on criteria on current form:

CurrentDb.Execute "UPDATE tblItem INNER JOIN (tblClient INNER JOIN tblOutbound ON [tblClient].[ClientID] = [tblOutbound].[ClientID]) ON [tblItem].[ItemID] = [tblOutbound].[ItemID]" _
& "SET [tblClient].[ClientID] = [forms]![frmPoS]![cboClient], [tblItem].[UnitSale] = [tblOutbound].[ExtendedPrice], [tblItem].[QtyAvail] = [tblItem].[QtyAvail]-[tblOutbound].[QtyOut], _
& "[tblClient].[ClientAcc] = [tblClient].[ClientAcc]+[tblOutbound].[AmountRecorded]" _
& "WHERE [tblOutbound].[InvoiceNo]= forms!frmPoS!InvoiceNo ;"

I am using DAO, I have used recordset, but when the records I am updating is more than 30 to 50, error of there is another user trying to update the same records you are updating arises.

Thank you in advance

First question: Is this a shared database? Even if it is a front-end/back-end split case, is it shared? Because if it is and (as you say) a number of records to be updated is greater than some limit, it is a matter of simple probability that two users doing the same sort of thing at the same time might "step on each other."

I will further comment that if this works without error for a smaller numbers of records to be updated then you have no syntax error. Getting a syntax error in SQL statements is an "all-or-nothing-at-all" situation. Either it works or it fails. And the error you are claiming to see is NOT a syntax-related error.

I don't see anything obvious, syntax-wise, except some missing quote marks in what you cut/pasted - in particular on the segment that starts with "SET [tblClient].[ClientID]=..." you are missing the trailing quote on the "...[tblOutbound].[QtyOut]" piece. But if this works correctly even once, then this post is almost certainly a case of a faulty cut/paste rather than a persistent syntax error.

The only other place I can see where you might have issues is that you have a couple of forms references ([cboClient] and InvoiceNo), so be sure before you try to run the .Execute that the values in those form references are valid.

I might suggest that you check for the possibility of whether you have pessimistic locking or optimistic locking set. I would recommend "optimistic locking" if you have that choice.

If you are getting an error about another user updating the record and there are no other users using the system it may be because the form this is being run from is bound to the table that being updated. So if the form is dirty the form is the other user. I've found that if I save the current record before run this sort of code it helps. I suggest trying a


or


before the update

Moore71,

Add a space to your sql before the WHERE clause

......
& " WHERE [tblOutbound].[InvoiceNo]= forms!frmPoS!InvoiceNo ;"

Thank you all for your contributions.
I have sort myself out now. For records sake, I used DAO recordset and all I did was add error checker
Thanks