How to change form reference in query parameter programatically

Topic

New Reply

We have a query that references a form field in its parameters. The reference is [Forms]![frm1-2-0 ConferQualifications]![Badge Number]. There is a button on form frm1-2-0 ConferQualifications whose On Click event contains code to open a report. The query in question is the record source for that report.

How can we change the form name programatically in the query parameters?

Thanks, in advance.

Reply | Quote

Replies

We have a query that references a form field in its parameters. The reference is [Forms]![frm1-2-0 ConferQualifications]![Badge Number]. There is a button on form frm1-2-0 ConferQualifications whose On Click event contains code to open a report. The query in question is the record source for that report.

How can we change the form name programatically in the query parameters?

Thanks, in advance.

Why do you want to do that, do you want to reference a different form?

Reply | Quote

Why do you want to do that, do you want to reference a different form?

patt,

Yes, we want to open the same report from a second form.

Reply | Quote

I’d remove the condition from the query and save it.
So if you open the report by itself, it will display all records.
In the On Click event of the command button, open the report with a where-condition:

DoCmd.OpenReport ReportName:=”rptMyReport”, View:=acViewPreview, WhereCondition:=”[Badge Number]=” & Me.[Badge Number]

where rptMyReport is the name of the report and Badge Number is the name of the relevant field in its record source.

This assumes that Badge Number is a number field. If it is a text field, you need to enclose the value in quotes “, I use Chr(34) for that:

DoCmd.OpenReport ReportName:=”rptMyReport”, View:=acViewPreview, WhereCondition:=”[Badge Number]=” & Chr(34) & Me.[Badge Number] & Chr(34)

Reply | Quote

I’d remove the condition from the query and save it.
So if you open the report by itself, it will display all records.
In the On Click event of the command button, open the report with a where-condition:

DoCmd.OpenReport ReportName:=”rptMyReport”, View:=acViewPreview, WhereCondition:=”[Badge Number]=” & Me.[Badge Number]

where rptMyReport is the name of the report and Badge Number is the name of the relevant field in its record source.

This assumes that Badge Number is a number field. If it is a text field, you need to enclose the value in quotes “, I use Chr(34) for that:

DoCmd.OpenReport ReportName:=”rptMyReport”, View:=acViewPreview, WhereCondition:=”[Badge Number]=” & Chr(34) & Me.[Badge Number] & Chr(34)

Hans,

Thanks, as always.

This is the way that we tried it originally (before we put the badge number into the query as a parameter) but we had the incorrect variable name in the WhereCondition. Your suggestion helped track down the incorrect variable name. It now works perfectly!

Thanks again.

Reply | Quote