Friday 6 May 2011

Using a Combo Box to Search for a Record

In this tip we are going to look at how we can use a Combo Box control on a form to search for a particular record.  It works by clicking an item from the Combo Box's drop down list.  This activates a Visual Basic for Applications (VBA) Procedure using the forms ON CHANGE Event. Once the code executes, the form seamlessly displays the selected record.

Figure 1: Products Form with a Combo Box Search facility.  Selecting an item
searches for the relevant records and displays in on the form.
Lets begin by creating our Combo Box from scratch.

Create Combo Box from Scratch
  1. Open your form in Design View.
  2. Ensure the Wizard icon is deactivated.  If it is, just click the Wizard Icon so that it is no longer highlighted.
  3. Click the Combo Box Icon.
  4. When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.
You now have a empty Combo Box Control on your form.  If you wish, you can resize the box and add some text to the label.  The next task is for us to enter the ROW SOURCE property.  This is the source of the data which will appear in the Combo Box's drop down list (take care not to confuse this property with the combo box CONTROL SOURCE, as we are going to keep our control unbound).

The Row Source Property
  1. Select the Combo Box Control by clicking it with the mouse.
  2. Click the PROPERTY SHEET icon. This brings up the Combo Box's PROPERTY SHEET.
  3. Select the DATA tab.
  4. Enter the ROW SOURCE property.
When you  enter the Row Source property, you can select a Query/Table from the Drop down list, or write an SQL Statement directly onto the property grid. Another option is to click the three dots symbol at the end of the row to bring up the QUERY DESIGN Window. Then it is just a case of creating your query.

Figure 2: The Combo Box PROPERTY sheet for
the form in figure 1.
In our example shown in figure 1, I used tblProducts as the ROW SOURCE for the Combo Box. This, of course, is the same table that is used as the RECORD SOURCE for the main form.  I set the COLUMN COUNT PROPERTY (from the FORMAT tab) to 2, so that we get two columns in the drop down list - that is, the ID field, and the ItemName field.  I also set the BOUND COLUMN property (from the DATA TAB) to 1, so our Combo Box stores the value from the first column (ie the ID field) when the user makes a selection from the drop down list.

The next stage is to enter the VBA code.  We are going to use the Combo Box's ON CHANGE event.  This
event triggers as soon as the user selects an item from the drop down list.

Enter VBA Code
  1. Select the Combo Box Control by clicking it with the mouse.
  2. Open the PROPERTIES window.
  3. Select the EVENT tab.
  4. Select the ON CHANGE event by clicking its row in the grid.
  5. Click the three dots symbol on the far right of the row. This opens the CHOOSE BUILDER dialogue box.  
  6. Select CODE BUILDER and click the OK button.  This opens the VISUAL BASIC editor.
  7. Copy and Paste the code (listed below) between the PRIVATE SUB and END SUB statements.   You may need to edit the FINDFIRST Statement on line 4 (replace ID with the field you are searching for).
On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!ctlSearch
Me.Bookmark = rst.Bookmark
leave:
Me!ctlSearch = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave

Figure 4: The VBA Editor
The main section of code works by cloning the forms's record set, which is stored in an object variable called rst. The FINDFIRST method is then used to search the cloned record set for the item selected in the Combo Box by the user.  Once found, the forms Bookmark property is then set to that of the cloned recordset.  This results in the Products Form seamlessly displaying the record selected from the drop down list.

25 comments:

  1. Hai,

    When I Try this it shows record "not found". But i have dats in my records. Can u guide me pls.

    ReplyDelete
    Replies
    1. Hi Vajeeth

      I would suggest you first check the NAME property of the combo box control. It should be called ctlSearch. Then check the name of the ID field that you are searching on. It should be called ID.

      This is so the following line of VBA code (from the example snippet) matches:

      rst.FindFirst "ID = " & Me!ctlSearch

      Delete
  2. Hai Justin,

    Thanks for your help now i have got it. But one problem in that. I cant type the text in my Combobox. When i type it shows "results not found" can you help me with that please.

    ReplyDelete
    Replies
    1. Hi Vajeeth

      If you want to type text into the combo box (in addition to selecting a list item), then you will need to move the VBA code from the ON CHANGE event to the AFTER UPDATE event.

      Otherwise, the code will run as soon as you enter the first character in the combo box. However, by using the AFTER UPDATE event, the code will not run until you hit the ENTER key or move out of the combo box control. This gives you chance to type in the full search term.

      Delete
  3. Hai Justin,

    Thank you soo much.... Now its fine.

    ReplyDelete
  4. Hi,

    I am using this as a name dropdown menu, when I select the list I get the correct names within the menu, but when I click on the name to see the records, it comes up saying results not found, even though the combo list has populated..can you help?

    Phil

    ReplyDelete
    Replies
    1. Hi Phil

      You need to make sure that the line of code responsible for constructing the search term contains the correct reference to the ID field in your table, and to the name property of your combo box on the form.

      In my example the NAME property of the combo box control is called ctlSearch; and the name of the ID field is called "ID". I would suggest you look at this line of code from my example snippet, and amend it as necessary for your own particular database:

      rst.FindFirst "ID = " & Me!ctlSearch

      Delete
  5. Hi,

    I have checked the name properties of the combo box and the name of the id field, but I am still getting the record not found message box. Do you have any ideas?

    ReplyDelete
    Replies
    1. Just in case you would like to see the VBA script. This is what I have:

      Private Sub RecSearch_Change()
      On Error GoTo myError
      Dim rst As Object

      Set rst = Me.RecordsetClone
      rst.FindFirst "[Player ID] = " & Me.RecSearch
      Me.Bookmark = rst.Bookmark
      Me.RecSearch = Null

      leave:
      If Not rst Is Nothing Then Set rst = Nothing
      Exit Sub

      myError:
      MsgBox "Record Not Found"
      Resume leave

      End Sub

      The combo box name is "RecSearch", and the Primary Key I am wanting to look up is "Player ID".

      Delete
    2. Hi Zack

      I have just tried out the code you have supplied and the good news is that the code itself does actually work. This suggests that the problem might be with how you have set up your combo box.

      Is your combo box list populated with data when you try to select an item? If so, is it showing data from the Id field (ie Player Id) and/or is it set up to display a description of what the ID field actually is? It is important that if the Id field you are searching on is the first field the table, that the BOUND FIELD property (DATA TAB) of the combo box is set to 1. You can then opt to display other field values from the record by adjusting the COLUMN COUNT and COLUMN WIDTH properties (FORMAT TAB)accordingly.

      Also, is your Player Id field numeric? (My sample code is set up to search on a numeric field).

      Delete
    3. Hi Justin,

      First, let me say thanks for the help. I haven't had to mess with Access too much since college, and I am a little rusty. Especially in Access 2007.

      The combo box does populate the data (which is Player ID, Player Lastname, and Player Firstname). I checked the Bound field property, and it is set at one. My column count is set to three. The Player ID is a numeric field; set as a autonumber.

      Just a brief description of what I am trying to do is:

      I have a multiple page form for players for our local little league. I am wanting to find and reopen a certain players form to add or adjust information during the season. We have several to come in during registration and not have all the required documentation such as birth certificate, proof of residency, and etc.

      Any advice would be greatly appreciated.

      Delete
    4. If you would like I could zip the file up and use a program like dropbox to send it you a copy of it.

      Delete
    5. Hi Zack

      I think I know what the problem could be. Can you confirm that there is a reference to the Microsoft Office 12 Access database engine Object Library. You can check this by opening the VBA editor, clicking the TOOLS menu, and then REFERENCES.

      If there is not a reference then you just need to tick the relevant check box. There is more information about this procedure in my blog post on DAO Recordsets.

      Delete
    6. I checked to make sure the Microsoft Office 12 Access database engine Object Library was checked. The options I have checked are:

      Visual Basics for Applications
      Microsoft Access 12.0 Object Library
      OLE Automation
      Microsoft Office 12 Access database enigne Object Library

      I have changed the code to:

      Dim rst As DAO.Recordset

      , but still getting the message box. Could it be some the wrong with the Row Source SQL query? I have blown away the combo box a couple times trying different ways of setting up the box, but never have got it to work.

      Delete
    7. All I can really suggest, in that case, is that you try going through the example exercise from my main blog post above. Just follow the instructions I gave to create the form with the combo box. It takes you right through the process. Once you have got it working for yourself, you might find it easier to work out what is going wrong in your own particular database. Sorry I can't be of more help.

      Delete
    8. Hi

      You need to modify the 4th line of my code from the example from:

      rst.FindFirst "ID = " & Me!ctlSearch

      To:

      rst.FindFirst "ID = '" & Me!ctlSearch & "'"

      This will ensure the ID text value being searched for is enclosed within quotation marks. (Note I have used a single quotation mark here ie ' because the search value is already nested within a string within this line of code). In your case, the code will search for ID = "10x-7" rather than ID = 10x7. It's only a subtle difference, but it does make the difference between the code working and not working.

      Delete
  6. hi I need help on how to populate from a drop down list.i have a list of 200 entries and a report is generated once i click on a button updating ten sheets. i am trying to find a way where i dont have to selct the entries from the drop down manually and the report is generated for all 200 from the macro. can anyone help?

    ReplyDelete
    Replies
    1. Hi Archie

      Sorry I am not quite sure what you mean. What is it you want to populate from the drop down list?

      Delete
  7. Hi Justin,

    Thanks for replying.Lets take a hypothetical example. i have a list with alphabets from A to Z.Each alphabet is a name of a person.i have a workbook which has ten sheets. there is a macro which updates all the ten sheet. so as of now each alphabet is to be manually choosen from the drop down list and then the macro is run to update all ten sheets.a new sheet appears for each alphabet which is then saved at desired location.so i was looking for a code where the macro selects each alphabeet name ,runs the inbuilt macro, saves the new workbook and then changes the alphabet name to the next one.is there any such code to automatically selct entries from the drop down list and save it.

    ReplyDelete
    Replies
    1. Thanks for the additional info Archie. From what what you are saying it sounds like you are using an MS Excel spreadsheet rather than an MS Access Database. Unfortunately, I can only help you with questions relating to the Access Database.

      Delete
  8. Anyone know why I'm getting an error "Type Mismatch" using multiple criteria?
    Private Sub Combo9_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst '"[Name] = '" & Me![Combo9] & "'" _
    And"[Date]=#" & Me![Combo9] & "#"

    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    ReplyDelete
    Replies
    1. Nvm figured it out. Had to change the properties to date and add a quote:

      rs.FindFirst "[NAME] = '" & Me![Combo9] & "'" & "And [DATE] = #" & Me![DATE] & "#"

      Delete
  9. when row in table is primary key search dont work

    ReplyDelete
    Replies
    1. Hi Mili

      I think the problem you are describing may relate to how you have set the combo box properties. Make sure the BOUND COLUMN property is set to 1 (the primary key in my exercise). Then make sure the COLUMN COUNT property is set to to 2. This ensures that although two columns are displayed in the Combo box drop down list, the value being search is the first of those columns.

      There is more information on Combo Box properties in my post on Customizing a Combo Box.

      Delete
  10. Hi I was hoping for some help. I'm creating a db (sports related) and I want to add a combo box to the form in order to search for players. I've tried the different pointers on here, but am still having trouble. Anyone willing to take a look? Any help would be greatly appreciated.

    ReplyDelete