This article will explain how to perform a search operation on an Access table using VBA.

Note: A faster approach for doing searches would be to use queries. For more information on this please see the link below:

• Recordset Query Search , Access VBA

The article uses the ADODB.Recordset object. In order to use this object you may need to add reference to ADODB object library. For more information on this topic please see the link below:

• VBA Access Recordset, Object Library

How To?

Previously in the article Read Data from Table, Access VBA I’ve explained how to  read data from an Access table using VBA. The code used in that article will also be used here.

Assume we have a table with the following data:

As you can see the table has 4 fields:

• MyField1
• MyField2
• MyField3
• MyField4

Example:

Assume we want to search the values in the first field for the number 17:

The code below will achieve this:

Sub Example1()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
'move recordset object to 14th record
While objRecordset.EOF = False
    'check for match
    If objRecordset.Fields.Item(0).value = 17 Then
        'your code here
    End If
    objRecordset.MoveNext
Wend


Example:
Assume we want to search the values in the first field for the number 17. Once a match has been found we want the value in the second field to be displayed:

Sub Example2()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
Dim value As Variant

'initated recordset obejct
objRecordset.ActiveConnection = CurrentProject.Connection
objRecordset.Open ("MyTable1")
'move recordset object to 14th record
While objRecordset.EOF = False
    'check for match
    If objRecordset.Fields.Item(0).value = 17 Then
        MsgBox (objRecordset.Fields.Item(1).value)
    End If
    objRecordset.MoveNext
Wend
End Sub


Result:

You can download the file and code used in this article from the link below:

• Search.Zip

See also:

• Simple Recordset Query Search , Access VBA

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com