Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    7

    Unanswered: Multicolumn Combobox that uses FindFirst to go to specific record.

    Using the following code my combo box goes to a specific record.

    Code:
    Private Sub Combo110_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[Item] = '" & Me![Combo110] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    The issue I am having, the query that I am running to populate the combobox has two columns. The search is only paying attention to the first column and therefore bringing me to the same record every time (if I have duplicate names in first column).

    If I select from the drop down "K003 23-8080" it will go to "K003 23-4285" every time... same with "K003 001-4003-000-000-000".

    How can I make my after update event take both columns into consideration?

    Thank you all for your time in advance...
    Attached Thumbnails Attached Thumbnails search_example.png  
    Last edited by Varadin; 10-06-11 at 14:28.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try (with the proper substitutions):
    Code:
    rs.FindFirst "[Item] = '" & Me!Combo110.Column(0) & "' AND [SomeOtherColumn] = '" & Me!Combo110.Column(1) & "'"
    Remember that the first column of a combobox or of a listbox is Column(0).

    Notice however that this problem can (not always) be the sign of a flaw in your design. Ideally the first row (that is usually hidden) should contain the primary key or another unique identifier for the row it helps to find. This simplifies the coding and avoid the use of complex criteria for the FindFirst method of the Recordset.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    7
    Thank you Sinndho -

    If I wanted to add the ID to the query and have it hidden, how should I change my code?

    I know that alphanumeric needs quotes, and numbers don't. How would I change the formatting of the original private sub to accomplish going to the ID of the record instead of going to the Item.

    Code:
    Private Sub Combo110_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[Item] = '" & Me![Combo110] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Change the RowSource property of the combo to include the ID in the first position of it.
    2. Change the ColumnCount property of the combo, adding one to the value it has now, i.e. if the value was 2 (2 columns) is becomes 3.
    3. Change the ColumnWidths property of the combo and specify zero (0) for the width of the first column, e.g. (in centimetres): 0; 2; 4 means that the fist column has a width of zero cm (i.e. it is hidden), the second column has a width of 2 cm and the third has a width of three cm.
    4. The BoundColumn property (1) does not change.
    5. The criteria becomes (with ID replaced by the proper name of the Identity column):
    Code:
    "[ID] = " & Me![Combo110]
    or if ID is not numeric:
    Code:
    "[ID] = '" & Me![Combo110] & "'"
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    7
    Absolutely perfect, thank you very much Sinndho!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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