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
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
Last edited: