How do you/I modify a query via VBA? 2

[HMJ]

I am not sure if this forum is the one I should use, but I will start here anyway. Currently I am trying to automate a routine that requires me to modify a query. I need to change two values in the query to reflect the type of training and another to reflect the FY and Quarter the training was taken.

I know that I can use DoCmd.OpenQuery ("Z2-Update_Roster&quot to open the query. But how do I pass Training_Type = 6 and Training_Quarter = 2003 to the query? These two items actually come from a combo (Combo1 and Combo2).

Oh yes, this query will update an existing roster (table) in the database.

Thank you in advance!

Harry Jessen
HMJessen@Yahoo.com

 

[GoDawgs]

You have to use a QueryDef instead of the actual query. Check that out in help and come back if still confused.

Kevin

 

[ALEKSJAY]

It should work if you have those combo boxes in the criteria part of the query

 

[willir]

HMJ

GoDawgs and ALEKSJAY have pointed you in the right direction.

If you go over some of the other questions in this forum and others such as the Access Modules and Forms, you may see some code where the query or select statement is built by assuembling strings.

For example...

Dim strSQL as string
Dim strTainingType as interger

strSQL = "select * from tblRoaster where "

'Then append to the query

strSQL = strSQL & "tblRoaster.Training_Type = " & me.Training_Type

This logic builds an SQL statement. You can use the same process to build an QueryDef.

Use "If" and other VBA decision making commands to build a very complex query if required.

One more thing on coding is the use of quotes.

Strings to be encapuslated with quotes...
Say TrainingType is a text field.

strSQL = strSQL & "tblRoaster.Training_Type = " & "me.Training_Type"

... but this wont work because the string variable will look like...

"...tblRoaster.Training_Type = COED"
but needs to look like
"...tblRoaster.Training_Type = "COED""

There are several ways around this. I prefer to use...

Dim strQ as string

strQ = chr$(34) ' Ascii for double quote

and your statement looks like...
strSQL = strSQL & "tblRoaster.Training_Type = " & strQ & "me.Training_Type" & strQ

Dates need to be encapuslated with "#"

&quot;... where dtTemp < &quot; & #me.dtSelectDate#
which is equivalent to

&quot;... where dtTemp < &quot; & #08/13/2003#

Richard