Update Multiple Table Records With VBA Query

[123dstreet]

Hi All,

I have a table with many records, using a form with an update button click event, I would like to update ALL records where the Item in the table = the Item in the form.

There are 6 checkboxes and 6 text boxes that will need to be updated, but right now I am just trying to test if i can even change one text box to keep it simple. I have tried to start by using this:

    Dim mySQL As String
    Dim ItemNo As String
    Dim SO As String

mySQL = "UPDATE BT200 SET Part  = '" & SO & "' WHERE Item = " & ItemNo
DoCmd.RunSQL mySQL

So every time i run this command button, it gives me a prompt "You are about to update 0 row(s)". Nothing gets updated at all, I am clearly missing something vital here, is there any suggestions out there?

Any help is greatly appreciated!
D

 

[pbaldy]

Use this to see if the SQL is coming out as expected:

http://www.baldyweb.com/ImmediateWindow.htm

In the posted code, you never populate ItemNo or SO.

 

[123dstreet]

Hey thanks for your prompt reply!

I tried the debug and this is what it gives me in the immediate window:

mySQL = "UPDATE BT200 SET Part  = '" & SO & "' WHERE Item = " & ItemNo
UPDATE BT200 SET Part  = '21515' WHERE Item = 21515-01

The Part and Item are both coming out as I intended them to.

Could you explain how to populate?

Thanks!

 

[pbaldy]

Since your Item is text, it would need delimiters. Try

mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = '" & ItemNo & "'"

 

[123dstreet]

Yes this works! Thank you kindly!

So moving forward, is there anything different I will need to put in the SQL statement to update checkboxes from the same form?

 

[pbaldy]

Sounds like you just need to add fields to the SET clause.

 

[123dstreet]

pbaldy, thank you very much for your help!

 

[pbaldy]

No problem!