Results 1 to 13 of 13
  1. #1
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185

    Parameter Query Using A Combo Box

    Is it possible to set up a parameter query so that it uses a combo box for it's data entry?

    EXAMPLE:
    Table = tblMasterData
    Table Field = Category (This field is calculated data type returning values of "Less than 40", "50 to 59", "60 to 69", "70 to 79", "80 to 89", and "90 Plus")

    Query = qryAvailability
    Query Criteria Field Category = [Which Category Would You Like To Choose From?]

    When the parameter query pop up.... well pops up, I would like to be able to choose from a combo box the options that are in the table field. The field in the table can contain duplicates, and will contain many but in the combo box I would like to see only one of each category.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes - but do it in the opposite order.
    Just use the combobox result in the query criteria: Forms!MyFormName.MyComboName

    Or if the values are in the 2nd column then Forms!MyFormName.MyComboName.Column(1) as combo columns are zero based

    Then add code to the combo after update event to run the query

    Note that doing this means you won't actually see a parameter box

    If you are basing the combo values on a table/query, use SELECT DISTINCT to only get one of each type (set Unique values = Yes)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Access query object will not recognize the Column property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    Ahhh yes. I see what you are saying ridders52. And thanks for the input, you and June7.
    However I decided to go a different direction altogether. Gonna mark it solved though because what your solution is was exactly what I was looking for!

    Thanks again!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Call me Sylvester, but I'm curious. If you can't use the column property in a query (to which I agree) and that was the proposed solution, how did you solve it? I can think of 2 or 3 ways you could have solved this, and would be interested to know if you came up with something new or not.
    FYI - if you're curious too, those ways are
    1) a hidden control on the form that holds the column value. Refer to that control in the query
    2) pass the column value to the tag property if you're not already using it, and use in the query
    3) get the column value with a function and call the function in the query
    Last edited by Micron; 04-25-2018 at 10:40 PM. Reason: Corrections
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oops.
    I meant to add a follow up line about setting a variable equal to the value if it was column(1) then referencing that using a function in the query.
    Thanks June and Sylvester(!) for correcting my omission

    Also I use the tag property for other purposes but had never thought of using it in this way. Will investigate
    Last edited by isladogs; 04-26-2018 at 05:33 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting. I had that as an answer but went back to edit because I meant to suggest a function. During that edit it occurred to me that a query couldn't make use of a variable itself. Did you test it, and if it worked, how did you refer to it from a query?

  8. #8
    Desstro's Avatar
    Desstro is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2010
    Posts
    185
    Just for thread purposes I will explain the route I chose to go. Because the category is a calculated field and will provide data that has a specific naming convention and there will never be any other option than that naming convention I chose to just provide an example of those options in the parameter query pop up as seen below. I realize that is the lazy way out but, this is a very small database that will have only one end user ever.

    [Please Choose A Category - Example - Less Than 40 - 40 To 49 - 50 To 59 etc or 90 Plus]

    I am glad my question sparked a back and forth for the issue though. I may go back later and make changes. I like the idea of a hidden control on the form.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I tend to avoid value lists as it is easy to lose items if the allowed items change
    For example if you add a record with range 60 to 90 you also need to update your value list.
    Whereas using a query means the list is updated automatically
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    Interesting. I had that as an answer but went back to edit because I meant to suggest a function. During that edit it occurred to me that a query couldn't make use of a variable itself. Did you test it, and if it worked, how did you refer to it from a query?
    I didn't test it in this case but use the idea on a fairly frequent basis

    e.g. strSelection = Me.ComboBox.Column(1)
    strCriteria=Me.ComboBox.Column(2)

    Then in query criteria, I can use '" & strSelection & "' or the equivalent for strCriteria

    Of course I have to declare both strings as Public variables - a practice which some users will disagree with

    In fact I use these strings often enough to have functions GetSelection & GetCriteria ....

    Code:
    Function GetSelection()
    
    GetSelection = strSelection
    
    End Function
    I can then use that function instead in the query criteria which is what I think you were referring to
    Haven't tried your Tag idea yet - do you have an example to hand?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    e.g. strSelection = Me.ComboBox.Column(1)
    strCriteria=Me.ComboBox.Column(2)
    I think you are referring to a vba constructed sql statement, not a stored query, which I (and I think the OP) was dealing with. I say that because of "Query = qryAvailability" in the first post, and the question about a parameter query. I can't see how this would work in a query but I've never tried.

    As for how to use the tag property, in query design, Forms!frmMyFormName.Tag in the appropriate field in query design view.
    Of course, you need an event, such as the AfterUpdate of said combo box, to set the tag property before running the query.

    Me.Tag = cmbComboName.Column(1) or whatever the proper column reference would be.
    Last edited by Micron; 04-26-2018 at 12:47 PM. Reason: clarification

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    I think you are referring to a vba constructed sql statement, not a stored query, which I (and I think the OP) was dealing with. I say that because of "Query = qryAvailability" in the first post, and the question about a parameter query. I can't see how this would work in a query but I've never tried.
    I was referring to both ...BUT ....
    As previously mentioned, I use variables to get various columns from both listboxes & comboboxes
    Sometimes I assign these to functions but not always

    For example in the following VBA SQL statements, I use both the variable & a function based on that variable
    The number variable intFileID was column(1) of a combo

    Code:
       CurrentDb.Execute "UPDATE tblJSONStructure SET tblJSONStructure.SubArrayName = '', tblJSONStructure.TableName = '" & strTableName & "'" & _
                    " WHERE (((tblJSONStructure.FileID)= " & intFileID & "));"
                    
                CurrentDb.Execute "DELETE tblJSONSubArrays.*, tblJSONSubArrays.FileID" & _
                    " FROM tblJSONSubArrays WHERE (((tblJSONSubArrays.FileID)=GetFileID()));"
    Code:
    Function GetFileID()
        GetFileID = intFileID
    End Function
    I have found plenty of examples of queries using one or more functions as criteria (including those based on combo column data). For example from the same section of that database:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	9 
Size:	16.4 KB 
ID:	33755

    However, so far I can't find one where I've used the variable itself in the query criteria ... so perhaps my memory is playing me tricks
    Indeed I just tested replacing criteria containing a function with the source variable & it failed.
    If I do find one with that working I will post it later
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Query object will not recognize VBA global variables or constants. However TempVar should work. I have never used them.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Parameter query not working with combo box
    By JonathanT in forum Queries
    Replies: 6
    Last Post: 11-02-2017, 03:40 PM
  2. Unbound Combo Box Query Parameter
    By brezja in forum Queries
    Replies: 3
    Last Post: 04-16-2014, 02:29 PM
  3. Replies: 4
    Last Post: 09-09-2013, 05:34 AM
  4. Replies: 3
    Last Post: 10-07-2011, 07:49 AM
  5. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums