Even though there are many methods in MS Access to find records in a form to carry out the process of finding specific records in Visual Basic like GoToRecord but personally, I prefer the below mentioned method due to its effectiveness and flexibility.
Firstly, create a text field to hold the text that you need to find it within Microsoft Access Database. Then, create another command button to hold the VBA (Visual Basic for Application) code. Lastly, right click on the command button, click on Build Event, and paste either of the following VBA codes.
Note that you will need to change the names of the fields that are shown at the below VBA codes to match the fields that you created in your own application.
MS Access Find Records in Form Method #1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Dim rst As Recordset Dim strSearch, strNav As String strSearch = Nz(Me.txtFindNo, "NA") If strSearch = "NA" Then MsgBox "Search field can't be empty.", vbCritical, "Find" Exit Sub End If Set rst = Me.RecordsetClone rst.FindFirst "CountrySN= " & strSearch If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark Else MsgBox "Not Found!" End If strSearch = Null rst.Close Set rst = Nothing |
Method # 2 – Efficient and short
This method is much easier and shorter than the above and sufficient to rely on.
|
1 2 3 4 5 6 7 |
Dim strSearch As String strSearch = Nz(Me.txtFindCountry, "NA") With Me.Recordset .FindFirst "Country LIKE '*" & strSearch & "*'" If .NoMatch Then MsgBox strSearch & " was not found" End With |
The downloadable program demonstrates the process of the above VBA codes in both ways.
For more pro MS Access Programs, please visit our home page here.
To use Microsoft Access Programs.
You’ll need to have Microsoft Office installed including the bundle of MS Access or to download the free Microsoft Access 2016 run-time.