What is the syntax for an update table field to a variable based on another variable

[stonward]

Greetings All.

I am trying to find the syntax for an SQL Update Table instruction. They're quite straightforward it seems until you try to use variables as criteria.

So, I want to update the Diameters field of my Products Table to the Diameter value on my current form, only updating the record of the ProductID from my current form.

"UPDATE tblProducts SET Diameter =" & intDiameter

will naturally update all the diameters to the same value. How do I add the criteria of my ProductID?

Thanks again,


Stonward

[Seth Schrock]

What I find easiest to do is to make a string varible to assign the query to and then run the query variable. So something like:

Dim strQuery as String
 
strQuery = "UPDATE tblProducts SET Diameter = " & intDiameter & _
           " WHERE ProductID = My_Value"

Your WHERE clause can also use a variable like

" WHERE ProductID = " & My_Value

You can then execute your query using strQuery instead of the actual SQL code.

[stonward]

Hi Seth,

I can understand using the query window to get the SQL and then convert to VBA format (tried it!), but I don't quite get what it is you're doing in your example. What is the difference between strQuery, your variable and, say, strSQL as a string variable for the SQL?

Stonward

[Seth Schrock]

None. The variable name means nothing. I just didn't see that you were assigning the SQL code to a variable.

Here is an example of the difference I was trying to make. Instead of writing

Dim db as DAO.Database
Set db = CurrentDb
 
Db.Execute "UPDATE tblProducts SET Diameter = " & intDiameter & _ 
                        " WHERE ProductID = " & My_Value, dbFailOnError

I would write

Dim strQuery as string
Dim db as DAO.Database
set db = CurrentDb
 
strQuery = "UPDATE tblProducts SET Diameter = " & intDiameter & _
           " WHERE ProductID = " & My_Value
 
db.Execute strQuery, dbFailOnError

Even if you are using a different method to run your query, the same logic applies. If you were already assigning the SQL code to a variable, then all you have to do is concatenate the WHERE clause onto the end of your string (don't forget to make sure that there is a space before the WHERE).

[stonward]

hi Seth,

Now I didn't know about the space! I saw it in an example just before I read it in your answer. And guess what, it makes a difference!

Thanks yet again.

Stonward

[Seth Schrock]

Something that can help you in the future when you are having issues to SQL strings (or any variable for that matter), you can put a Debug.Print Variable_Name right after you assign the variable a value and then run your code. It will put the completed string (what the Jet SQL engine sees) in the immediate window (Ctrl + G to open it). This often helps to make sure you have your SQL correct. You can even copy and paste it into a querydef and see if it runs. However, I think that in this case you would have noticed that there wasn't the space that was needed.

Just a tip that can help you down the road.

[NeoPa]

Seth:
I would write

...
CurrentDb.Execute strQuery, dbFailOnError

Please don't Seth. There are many problems associated with treating CurrentDb() as a variable instead of the function that it is. Each calling of that function can return a different object. Each object refers to your current database to be sure, but that is not the same as being the same object. You can do a lot worse than get into the habit (as I have now) of using a DAO.Database variable for every reference you have in code to your database.

[Seth Schrock]

Oops. I declared the variable db, but didn't use it. I have gotten into the habit of using db instead of CurrentDb, but my first thought is still to use CurrentDb. I just usually catch myself.

Thank-you for pointing this out. I will edit my previous post so that future viewers see the correct (and what I meant to put) code.

[NeoPa]

Good for you. Especially impressive that you edited the original post. I had considered requesting that you do for that very reason but it seems you're getting ahead of the game. I can feel a PM coming on in the next few days. Must rush now, but more details then ;-) (I know. Cruel to leave it hanging like that :-D Look out for a PM by the end of the week.)