Passing a value from a variable to an update query

  • Thread starter mukudu99 via AccessMonster.com
  • Start date
M

mukudu99 via AccessMonster.com

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
 
J

John W. Vinson

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
Click to expand...

You'll need to call a VBA Function. VBA variables are not exposed to the query
engine, and query parameters and fields are likewise not directly available in
VBA. You could have just a silly little wrapper function like

Public Function GetLngMaxID()
GetLngMaxID = lngGetMaxID
End Function

if the variable is dimmed as a Public variable.

As an alternative, you can use the builtin DMax() function in a query:

UPDATE tbl_Table1 SET tbl_Table1.ID = DMax("ID", "someothertable", "<optional
criteria string>")
 
D

Dale Fye

Or you could create a parameter query and execute the query. This involves a
little bit more code, but you could wrap the code from step 2 below in a
subroutine and pass it the name of the query, and the value of the
parameters. This way you could reuse it for updating various fields/tables

1. define the query

PARAMETERS NewValue Long;
UPDATE tbl_Table1 SET ID = [New Value];

2. In your code add something like:

Dim qdf as DAO.Querydef
set qdf =currentdb.querydefs("yourQueryName")
qdf.Parameters("NewValue") = lngGetMaxID
qdf.execute
qdf.close
set qdf = nothing


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Top