Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Carol W.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='WSC' post='796147' date='03-Oct-2009 12:45']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.[/quote]
    Why do you want to do that, do you want to reference a different form?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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)

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='796151' date='02-Oct-2009 20:49']Why do you want to do that, do you want to reference a different form?[/quote]

    patt,

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

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796163' date='03-Oct-2009 02:33']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)[/quote]

    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.
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •