问题描述:

I get this error when running the code below:

Dim sqlSCIQty As String

Dim sqlSCQty As String

sqlSCIQty = "UPDATE [products/stock] INNER JOIN [Stock Conversion Items] " & _

"ON [products/stock].[Product Code] = [Stock Conversion Items].[Result PC] " & _

"SET [Stock Level] = ([Stock Level] + [Quantity]) " & _

"WHERE [Stock Conversion Items].[CutID] = " & Me.txtPCutID.Value & ";"

sqlSCQty = "UPDATE [products/stock] INNER JOIN [Stock Conversion] " & _

"ON [products/stock].[Product Code] = [Stock Conversion].[Source PC] " & _

"INNER JOIN [Stock Conversion Items] " & _

"ON [Stock Conversion].[SCID] = [Stock Conversion Items].[SCID] " & _

"SET [Stock Level] = ([Stock Level] - [Stock Conversion].[Quantity]) " & _

"WHERE [Stock Conversion Items].[CutID] = " & Me.txtPCutID.Value & ";"

Set db = CurrentDb

db.Execute sqlSCIQty, dbFailOnError

db.Execute sqlSCQty, dbFailOnError

Set db = Nothing

I know that the first statement works and runs fine, but the second one throws up that error, i'm not familiar with such complex update statements so can anyone with any SQL knowledge spot anything?

Thanks in advance,

Bob P

网友答案:

You could try like this

sqlSCQty = "UPDATE (([products/stock] INNER JOIN [Stock Conversion] " & _
"ON [products/stock].[Product Code] = [Stock Conversion].[Source PC]) " & _
"INNER JOIN [Stock Conversion Items] " & _
"ON [Stock Conversion].[SCID] = [Stock Conversion Items].[SCID]) " & _
"SET [Stock Level] = ([Stock Level] - [Stock Conversion].[Quantity]) " & _
"WHERE [Stock Conversion Items].[CutID] = " & Me.txtPCutID.Value & ";"

You need brackets when you have more than 1 join in ms-access

相关阅读:
Top