Share

Iterate and Search for Values in a Table, Access VBA

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:

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:


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:
Table with 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:
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:
Result

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

See also:

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

Leave a Reply

Your email address will not be published. Required fields are marked *