How to Pass Parameter to Query?

[Bee]

Hi

I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.

Any suggestion/example on who to do that will be very much appreciated.

Thanks,
B

 

[tehNellie]

Simple way if you want the user to supply the detail is edit the query in design view and in the criteria section of the column in question enter:

like "*"&[Please Supply a Name]&"*"

(assuming its a name column.)

When the query is opened, the user will be presented with a dialogue box.

Wrapping the prompt in Like "*" & "*" means that if they don't know the full name, for example, they could just enter "Smith" which will return
John Smith
Fred Smith
Ben Smith etc into the results.

If you want explicit information included just exclude the like "*" & &"*"

 

[Bee]

That's brilliant, but will not work for me as I need to pass the value of a variable automatically to the query.

 

[tehNellie]

Use the form_open() event and define your SQL there, then set the rowsource to the SQL:

Private sub formName_open()
  Dim strSQL as string

  Strsql = " SELECT something FROM aTable WHERE aColumn = " & chr(34) & strCriteria & Chr(34) & ";"
  
  me.controlname.rowsource = strSQL
  me.controlname.requery
end sub

You possibly don't explicitly need to requery the control at this point, but it's something I like to include whenever I change the rowsource SQL.

The & chr(34) & wraps the variable in double quotes (for a string), I find this much clearer to read than trying to just use combinations of double and single quotes.

 

[Bee]

I get this compile error 'Method or Data Member not found' on the following line:
me.controlname.rowsource = strSQL

What do you mean by control name? Is that the form name? The parameter I need to pass is for the form, so I replaced controlname with the name of my form. I also took the Me. part off, but these did not help.

Can you give an example please?