VBA variables in queries (2000)

Topic

New Reply

I want to use a VBA-variable as a criteria in a query.

Now I read a field from a form (form!form1.field1), and set the value of the field from VBA (me!field1=12 e.g.), but that’s slow, I guess, and a bit clumsy I think.

Can anyone help?

rettnuc.

Reply | Quote

Replies

Ofcourse I can use:

Function GetVariable() as variant
GetVariable=(fill in name of variable)
End Function

But isn’t there an easier way to do this?

Reply | Quote

I don’t think there is an easier way. Standard SQL doesn’t know about VBA, forms etc. Microsoft extended SQL in Access to recognize VBA functions and references to controls on forms or reports. Variables – not.

Maybe, if you explain why you want to refer to a variable (and not to a control or a function), somebody will come up with a bright idea.

Reply | Quote

Parameterquery is not an option because of the reason I want to use that variable in a query:

I use a combobox on a form to select a writer. On that form I have different buttons which open different forms which show information about that writer. Using subforms has come into my mind, but is not what I want.
Therefore I use different queries, having one item in common: writerID. Catching that ID from the main-form is slow, so I want to set a public variable and use that as a criterium in the different queries for the forms.

Q: IS there anyone having bright ideas? Otherwise I’ll stick with the GetVariable function HansV and me mentioned before.

Reply | Quote

Have you considered using the WhereCondition argument of the OpenForm method?

It would work more or less like this:

Base your information forms on queries without criteria. The queries should contain WriterID, however.

The OnClick handler of the command buttons on your main form could look like this:

Private Sub cmdMyButton_Click()
DoCmd.OpenForm FormName:=”frmDetailInfo”, WhereCondition:=”WriterID=” & [cboWriter]
End Sub

where cboWriter is the name of the combo box used to select the writer. I assumed that WriterID is numeric; if it’s text, replace the last part of the instruction by

WhereCondition:="WriterID='" & [cboWriter] & "'"

Reply | Quote

You could also open the forms using the OpenArgs argument to pass the writerID. There is NO way to directly reference variables in a query. You can’t even use built-in variables in a query, only their numeric equivalents. So either use Hans’s functional approach or make it easy on yourself and use either the WhereCondition or OpenArgs to open the forms in the first place.

Reply | Quote

Why not just use a parameter query?

Reply | Quote