Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run a parameter query from an Access form (VBA Access2003/WindowsXP)

    How do I pass a parameter from a form that requests needed data (like a name) to a query that needs that data? Writing a parameter query is straight forward, but its method of requesting its parameter is by the pre-defined use of its little parameter request window. Running a query from a form is also straight forward, but I can see no way, after the form has requested and received the parameter data, of passing it to the query. (The DoCmd OpenQuery Method, as an example, has no capability to pass along the parameter information.)

    Thank you for your time and help,

    John Littell

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a parameter query from an Access form (VBA Access2003/WindowsXP)

    To have a query refer to the value referred to on the form, in the criteria use a statement like this:


    IIf(IsNull([forms]![YOURFORM]![INPUT SOURCE CONTROL HERE]),[FILTERED FIELD NAME HERE],[forms]![YOURFORM]![INPUT SOURCE CONTROL HERE])
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run a parameter query from an Access form (VBA Access2003/WindowsXP)

    Jeremy's reply lets the query return all records if the parameter is left blank. If you simply want to replace the parameter prompt with a reference to the form, change the criteria from something like

    [Enter name]

    to

    [Forms]![frmMyForm]![txtName]

    where frmMYForm is the name of the form, and txtName is the name of the text box on the form in which the user enters the name information.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a parameter query from an Access form (VBA

    Jeremy and Hans,
    Thank you for your replys. Once I realized that the form would still be open and accessable, it all fell into place. Works fine.

    John

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a parameter query from an Access form (VBA

    John,
    If you run into the case where you must (or want to) close the form, you could do one of two things...create a hidden form that will store the value of the box, and refer to that, or store the value as a global variable.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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