1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need combo box to search multiple fields

Discussion in 'Business Applications' started by terabacktyl, Dec 20, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    Hello!

    I have a combo box that pulls from a query. The query pulls 5 fields from the same table. The first TWO fields are the primary key for the table. There are many item numbers, but only one item number/version combination. However, when I make a choice from the combo box, if there are more than one of that item number it reverts to the first in the list. It doesn't let me choose between the different version numbers. Below is an example:
    Item NumberVersion1016-010003011016-011002031016-011002051016-011003011016-011006011016-013002051016-013003011016-013006031016-014002071016-014003-1016-01400301

    Here is the code for the combo box after I switched it from a macro:

    '------------------------------------------------------------
    ' Combo16_AfterUpdate
    '
    '------------------------------------------------------------
    Private Sub Combo16_AfterUpdate()
    On Error GoTo Combo16_AfterUpdate_Err
    DoCmd.SearchForRecord , "", acFirst, "[Item Number] = " & "'" & Screen.ActiveControl & "'"

    Combo16_AfterUpdate_Exit:
    Exit Sub
    Combo16_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo16_AfterUpdate_Exit
    End Sub


    How do I get this to allow me to choose from every record, i.e. specific item number/version pairs?

    Thanks!
     
  2. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    Well that example didn't convert well.... Looked like:

    Item number Version

    111 1
    111 2
    222 1
    222 2
    222 3

    etc.
     
  3. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Why not make cascading combo boxes?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The code for the combo "Finds" the first record that matches and only displays that record.
    If you choose 111 2 it sould find 111 2, not 111 1.
    What do you select and what result do you actually get?
     
  5. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    The combo box shows all items in the table, as needed. But when I choose Item number 111 version 2, it displays the same item version 1. This form and query are going to be used to look up the most recent version of an item and click a check box associated to the record that makes it "active". Once active it will be available to be viewed and printed in a different form.

    I am trying to get cascaded combo boxes to work. Here is my code so far:

    Private Sub Form_Current()
    On Error Resume Next
    [Item Number Combo] = DLookup("[Item Number]", "Table1", "Version='" & Version_Combo.Value & "'")
    Version_Combo.RowSource = "Select Table1.Version " & _
    "FROM Table1 " & _
    "WHERE Table1.[Item Number] = '" & Item_Number_Combo.Value & "' " & _
    "ORDER BY Table1.Version;"
    End Sub

    Private Sub Item_Number_Combo_AfterUpdate()
    On Error Resume Next
    Version_Combo.RowSource = "Select Table1.Version " & _
    "FROM Table1 " & _
    "WHERE Table1.[Item Number] = '" & Item_Number_Combo.Value & "' " & _
    "ORDER BY Table1.Version;"
    End Sub


    The second combo box (Version Combo) is supposed to display the versions available (which it does) and also a second row with a yes/no if that version is already active or not (which it does not). If I choose an Item number, the second Combo box does what it's supposed to and only shows the corresponding version numbers, however when I select the version I want, it does not update the other fields in the query this form is based on.

    I will try adding a me.requery on the after_update of the second combo box. Other ideas?

    Thanks!
     
  6. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    It looks like when I choose an item number it limits the list for the second combo box. But when I choose a version, it changes the item number in the first combo box to the sequentially first item number with that available version number.

    Still without updating the rest of the form...
     
  7. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    So I figured out something that works! (For now) In the query for my form I did a concatenate on the item number and version. This is then the row chosen from in only one combo box. It will pull the specific record I want, and update the rest of the form to that record.

    In the future when this database gets larger, it would be nice to have the cascading boxes, though. Even though they are in order, it will be a pain to scroll through 500 items...
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Spot on answer to your problem, sorry that I didn't get back to you.
    I have posted many versions of cascading combos and I know Rockn has posted a few as well.
    The basic approach is to have the second combo use the first combo selection as it's Filter in it's SQL Statement.
    The other item you need is to have the first combo After Update Event Procedure "Requery" the second combo using
    forms![formname]![comboname]
    where formname is the actual name of the form and comboname the name of the combo.
     
  9. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    So I changed my previous coding for the first combo box to:


    Private Sub Form_Current()
    On Error Resume Next
    [Version Combo] = DLookup("[Version]", "Table1", "Item_Number='" & Item_Number_Combo.Value & "'")
    Version_Combo.RowSource = "Select Table1.Version " & _
    "FROM Table1 " & _
    "WHERE Table1.[Item Number] = '" & Item_Number_Combo.Value & "' " & _
    "ORDER BY Table1.Version;"
    End Sub

    This limits what will be in the second combo box correctly (although It would be nice if it didn't show the same item multiple times). I don't understand you to requery based on the second combo though. I tried typing in Forms![Active?]![Version Combo] into the properties list for the first combo box AfterUpdate, and in the VBA code window for the AfterUpdate as well. It said object Forms!Active? doesn't exist...

    What did I do wrong!

    And thanks so much for all of the help!
     
  10. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is this entire form bound to the underlying table or tables?
     
  11. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    The record source for this form is a query, that pulls from only one table (Table1).
     
  12. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    If the second combo is based off of a query as well set it's criteria to be a column value from the first combo box and on the first combo box put an afterUpdate event to requery the second combo.
     
  13. terabacktyl

    terabacktyl Thread Starter

    Joined:
    Dec 19, 2011
    Messages:
    17
    That will make the first combo box still have all if the item/version combinations shown, right? (since I will have an invisible, or visible, column for the verion number) I am looking for a way to show only the item number in the first box (with no duplicates), and then the available corresponding version numbers for the chosen item number in the second box. Then the record for the item number and version choice from the boxes with populate the form from the underlying query.

    For now I am fine with scrolling through all of the available choices in just one combo box searching through the field I created stringing the item number and version number together, but in the future, after more and more versions are added, the list will get very long. That is why I was hoping for the two separate boxes. I just am too green in Access to know how to do it. :)
     
  14. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Can you post a sample database?
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1032081

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice