Passing a value from a variable to an update query

[jonamukundu]

Hi everyone
I have a variable that captures a maximum value
How do i pass it to an update query?
I am am doing the following and i am getting the pass parameter dialog box:
UPDATE tbl_Table1 SET tbl_Table1.ID = lngGetMaxID;

any ideas

Thanks

[Stewart Ross]

Hi. If you are running your query from the query editor the database engine cannot access local variables defined in VBA code. Although it is possible in VBA to define and set parameters for queries, it is much easier simply to define the SQL for your query within the VBA code and run the query using DoCmd.RunSQL.

In defining the SQL string you include the value of the variable, not its name, as shown below:

DIM strSQL as String
strSQL = "UPDATE tbl_Table1 SET tbl_Table1.ID = " & lngGetMaxID & ";"
DoCmd.RunSQL strSQL

This works for numeric values; if you have to do the same for strings, the string literal passed must be enclosed by single quotes:

"UPDATE tbl_Table1 SET tbl_Table1.somestringfield = '" & strsomething & "'"

-Stewart