Update Error

[Lez]

Hi Guys,

Can anyone see a problem with this update code:

strSQlCS = "UPDATE tblinventory " & vbCrLf
strSQlCS = strSQlCS & " INNER JOIN tblinventorydetail " & vbCrLf
strSQlCS = strSQlCS & " ON tblinventory.productID =
tblinventorydetail.productID SET tblinventory.Sumofqty =
[tblinventory]![SumOfqty]+[tblinventorydetail]![qty]" & vbCrLf
strSQlCS = strSQlCS & " , tblinventory.Sumofweight =
[tblinventory]![SumOfweight]+[tblinventorydetail]![weight]" & vbCrLf
strSQlCS = strSQlCS & " WHERE
((tblinventorydetail.productID)=[tblinventory]![ProductID]) " & vbCrLf
strSQlCS = strSQlCS & " AND
((tblinventorydetail.batchid)=[tblinventory]![batchid]);" & vbCrLf
strSQlCS = strSQlCS & " AND
((tblinventorydetail.name)=[tblinventory]![name]);"

When I run this for the first time it adds just the first record and nothing
else, if I then try and run it a second time I keep getting a Run-time
error'3142':
Characters found at end of SQL statement.

Any suggestions or help would be appreciated.

TIA
Lez

 

[smk23]

Hi Lez,
Right after you finish defining strSQLCS, insert in your code :

msgBox strSQlCS

to read your SQL statement (before you execute). That's a quick way to
diagnose problems. Just looking at this, it looks to me like you will need to
make some changes like:

tblinventory.productID = " & tblinventorydetail.productID & " SET

 

[Lez]

Cheers Thanks for that.

Made the changes and all working fine now

Lez

Hi Lez,
Right after you finish defining strSQLCS, insert in your code :

msgBox strSQlCS

to read your SQL statement (before you execute). That's a quick way to
diagnose problems. Just looking at this, it looks to me like you will need
to
make some changes like:

tblinventory.productID = " & tblinventorydetail.productID & " SET
--
sam

Hi Guys,

Can anyone see a problem with this update code:

strSQlCS = "UPDATE tblinventory " & vbCrLf
strSQlCS = strSQlCS & " INNER JOIN tblinventorydetail " & vbCrLf
strSQlCS = strSQlCS & " ON tblinventory.productID =
tblinventorydetail.productID SET tblinventory.Sumofqty =
[tblinventory]![SumOfqty]+[tblinventorydetail]![qty]" & vbCrLf
strSQlCS = strSQlCS & " , tblinventory.Sumofweight =
[tblinventory]![SumOfweight]+[tblinventorydetail]![weight]" & vbCrLf
strSQlCS = strSQlCS & " WHERE
((tblinventorydetail.productID)=[tblinventory]![ProductID]) " & vbCrLf
strSQlCS = strSQlCS & " AND
((tblinventorydetail.batchid)=[tblinventory]![batchid]);" & vbCrLf
strSQlCS = strSQlCS & " AND
((tblinventorydetail.name)=[tblinventory]![name]);"

When I run this for the first time it adds just the first record and
nothing
else, if I then try and run it a second time I keep getting a Run-time
error'3142':
Characters found at end of SQL statement.

Any suggestions or help would be appreciated.

TIA
Lez

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George