Changing an update query through a module?

[Mashieman9]

Is it possible to change an update query through a module? I have a variable in the module that I need to use in the query. If so, could someone help me out?

Thanks,
Nathan

 

[zevw]

You sure can!

lets see you update query and your code

 

[dhookom]

I use DAO in a generic function.

Function ChangeSQL(pstrQuery As String,
pstrSQL as String) as String
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
ChangeSQL = qd.SQL
qd.SQL = pstrSQL
Set qd = Nothing
Set db = Nothing
End Function


Duane
MS Access MVP

 

[zevw]

Where is the variable and the SQL String.

You need to concatenate the SQL String with you varaible value if that is a criteria.

 

[dhookom]

If you are referring to my posting, I use the function like:

Dim strSQL as String
Dim strOldSQL as String
strSQL = "Select ... FROM... where...."
strOldSQL = ChangeSQL("qselMy",strSQL)


Duane
MS Access MVP

 

[Mashieman9]

So far it makes sense, I just can't see how the code is referencing the query. The query's name is "UpdateStock".

Thanks again.

--
N.Hunt

 

[dhookom]

And I was supposed to know that how? Try replace "qselMy" and strSQL with "UpdateStock" and your SQL.

Duane
MS Access MVP

 

[Mashieman9]

Thanks for all the help... I feel kinda stupid, I found a much easier way to do it.

Thanks.

--
N.Hunt