MS Access Combo box search

Search Records by using Combo Box

In this lesson I am going to explain how to add a combo box to search data on a table with the help of a combo box.You can add a combo box on a Form under the design view and you have to add only a small vba code to accomplish this task.

Here is a example data table that I have used to test my method.

Columns used are as follows;
ID     =Number
StuName  = Text
StuAddress = Text
StuDOB  = Date
StuMobile = Text

table name is tblMain


Once you have created the table you have to create a form based on it.To do this quickly you can use Form Wizard.Once you select form Wizard,It will give you a option to add desired filed to appear on the form.this time select all the table fields to create a Form.

Once you created the form under the Form Wizard then you have to add combo box to this form.This can be done under the design view of the form.Please see following screen shot to understand this.

You have to select ID column of our table as the Row Source for this combo Box.Form Name is frmMain

Then You have to put small VBA code behind the Combo Box to make this task sucess. Then by right clicking Combo Box select "Build Event" then select Code Builder,Finaly you have to enter following code under the "Private Sub cboFind_AfterUpdate()" event.

Following is the code you should use to make this tack success

Option Compare Database

Private Sub cboFind_AfterUpdate()

Dim strCriteria As String

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

strCriteria = "[ID] = " & Me![cboFind]

rst.FindFirst strCriteria

Me.Bookmark = rst.Bookmark

End Sub

Finally, Once you entered above code Run your access form to check this.

When you select desired record from the Combo Box it will automatically load the relevant record to the Form from Our Table.

Down Load Sample File by clicking following link