Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: Using DoCmd.OpenQuery with query parameters

    I need to open a query when a person clicks a button on a form. This query requires a couple of parameters which will be entered into the text boxes on the form.

    How do I include the parameters as part of the query?

    I was thinking along the lines of:

    DoCmd.OpenQuery( "SELECT * FROM Table WHERE Param1 = 'text'" )

    Any suggestions as to how this can be done?

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    Create the Query and point the where to the open form text box

    save the Query

    then DoCmd.openQuery("QueryName")
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    You mean like:

    SELECT * FROM Table WHERE Field = Forms("FormName")!ControlName

    That kind of thing?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    I would have a text box on the form (say it's named: FCity). If you need a query to populate this value on the form, a dlookup function might be the best here to lookup a specific value in a query (read up on the dlookup function.) You can use the dlookup in the sourceobject of the control (ie. =dlookup("[MyValue]","MyTableToLookupValue",<criteria if needed>)

    Then for the query based on that textbox value on the form, the query would look something like this (in the code view):

    Select * from MyTable where MyField = '" & Forms!myFormName!FCity & "'" (for text comparisons)
    You can also use:
    Select * from MyTable where MyField Like """ & Forms!myFormName!FCity & """"

    or (for integer comparisons)

    Select * from MyTable where MyIntegerField = " & Forms!myFormName!FIntegerFieldName & ""

    If it's a "Like" statement on a text field on the form, I would put criteria (in the criteria row like...)
    Like Forms!MyFormName!FCity & "*"
    But keep in mind, using the like statement may not return blank value records.

    Often, I will create different queries with the different criteria based on 2 or more "criteria" textboxes on the form. If they put a value in one of the textboxes, I will set the recordsource so that the query with criteria is populated.
    Private sub FCity_AfterUpdate()
    me.recorsource = "qryBasedOnCity"

    and in my qryBasedOnCity query, I have the criteria under City: Like Forms!MyFormName!FCity & *

    You may want to look in the code bank under search examples. There are lots of them which can give you some good ideas.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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