Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2001
    Location
    Rapid City, South Dakota, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query using ComboBox as criteria

    Need help in how to reference combobox column0 and column1 in a query. I have searched but have not had any success.

    I have a combo box based on a query that has 3 fields (columns). I need to reference column0 (orgsh) and column1 (TypeAcct) as criteria for another report query.

    I can reference the bound column but can't figure out how to reference each column in the combobox.
    Lloyd

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using ComboBox as criteria

    try something like this
    Forms!your form name!List200.Column(0)
    Forms!your form name!List200.Column(1)

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using ComboBox as criteria

    sorry just noticed it was a combo box
    try this
    Forms!your form name!combo200.Column(0)
    Forms!your form name!combo200.Column(1)

  4. #4
    New Lounger
    Join Date
    May 2001
    Location
    Rapid City, South Dakota, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using ComboBox as criteria

    Tried that but it didn't work.
    Came back with:
    Undefined function in 'Forms!frmEquipLabels!combo71.column' in expression
    Lloyd

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query using ComboBox as criteria

    What version of Access are you using? I've noticed that Access 2000 is much fussier about using combobox references in query criteria than its predecessors. It seems to be OK with just the combobox itself, but the column references seem to throw it. You don't normally need to reference column(0) anyhow, if that's the bound column, since a reference to the combobox alone will return that value.
    Charlotte

  6. #6
    New Lounger
    Join Date
    May 2001
    Location
    Rapid City, South Dakota, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query using ComboBox as criteria

    I am using Office 97.
    I did find a solution. I created two hidden fields on my form. Then on the Combo Box AfterUpdate I put the following code in:

    Private Sub Combo71_AfterUpdate()

    On Error GoTo Combo71_AfterUpdateError

    Me!txtOrgShop = Me![Combo71].Column(0)
    Me!txtAcctType = Me![Combo71].Column(2)

    Combo71_AfterUpdateExit:

    Exit Sub

    Combo71_AfterUpdateError:

    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

    Resume Combo71_AfterUpdateExit

    End Sub

    Then used the hidden fields as references in the query.
    Thanks to all for your help!
    This is a great forum that I check on a regular basis for help in solving problems.

    Lloyd

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query using ComboBox as criteria

    I never had a problem referencing columns in 97, but the solution you found will work in either version and it is probably a better method anyhow. Another method is to create the SQL for the query in code and pass it the values in the columns or to create a parameter query and pass the values to the parameters.
    Charlotte

Posting Permissions

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