Update Multiple Table Records With VBA Query

123dstreet

Registered User.
Local time
Today, 15:34
Joined
Apr 14, 2010
Messages
122
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:
Code:
    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
 
Hey thanks for your prompt reply!

I tried the debug and this is what it gives me in the immediate window:
Code:
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!
 
Since your Item is text, it would need delimiters. Try

mySQL = "UPDATE BT200 SET Part = '" & SO & "' WHERE Item = '" & ItemNo & "'"
 
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?
 
Sounds like you just need to add fields to the SET clause.
 

Users who are viewing this thread

Back
Top Bottom