Is it possible to pass a numeric variable to a criteria parameter in a query?

[Eddie]

I have a form that I use for entering data. One of the fields in each record is an autonumbered field called "RecordID."

There is a button on the form that (on click) executes a VBA script that saves the current record and then executes a command to print a report containing the data from a single record. That report is, of course, based on a query.

Currently, the criteria parameter for the RecordID field of that query is "[Enter ID:]" which requires the user to manually enter the RecordID of the current record.

I used "Me.RecordID" in the VBA script to store the RecordID of the current record as a variable called "CurrentRecordID". However, I cannot figure out how to get the query to recognize CurrentRecordID as a valid criteria parameter under RecordID.

Am I on the right track? I would appreciate any help with this.

 

[bob fitz]

No need for the variable. You can reference the text box control on the form directly from the query. Should be something like:
Forms!NameOfForm.NameOfTextBox

 

[plog]

That report is, of course, based on a query. Currently, the criteria parameter for the RecordID field of that query is "[Enter ID:]"

I wouldn't have any criteria in the query, have it show all records. To filter your report, you should pass it the filter in the DoCmd.OpenReport call:

https://msdn.microsoft.com/en-us/library/office/ff192676.aspx

 

[Eddie]

Should be something like:
Forms!NameOfForm.NameOfTextBox

This did the trick quite nicely, thank you. I did have to tweak it slightly because my form is actually a subform (sorry I didn't mention that in my original post). So the criteria parameter came out like this:

Forms!NameOfParent!NameOfSubform.Form!NameOfFormField

 

[Eddie]

To filter your report, you should pass it the filter in the DoCmd.OpenReport call:

I'm actually "printing" the report straight into an email message as a pdf file using DoCmd.SendObject which apparently doesn't allow for filter criteria. But thank you for that idea. I appreciate the help.