Using a Text Box to Find a Specific Record

[n_lindsey]

Hello All.

I am a reporting/database analyst and have been working with Access but fairly new to macros and vba code.

I built a form from a table with several records and added a Transaction ID # column as the primary key. I want to give the end user an option to search for any record. For example, if they need to view Transaction ID# 288, they can enter it in a text (search) box and retrieve that record immediately.

I have read and tried several suggestions from the site but don't quite understand how to make it work. I continue to get error debug messages or it changes the current assigned ID# which I DEFINITELY don't want to do.

Unfortunately, since I am new, I would need specific details on how to make it work from beginning to end (after adding unbound text box)and how I can stop the current ID#'s from being changed in error. The end user will also have the option to add records.

Thank you in advance for your help. :banghead:

 

[Trevor G]

Welcome to the forum,

May I suggest using a combo box which finds the record, they can type in the combo and it will search and present the record.

 

[Wiz47]

Welcome to the forum,

May I suggest using a combo box which finds the record, they can type in the combo and it will search and present the record.

I would suggest that as well. Typically, I use the name of a person to search for a record, then display it. But they could use the record number directly if they preferred.

The code below is what I use for that purpose.

Private Sub Cborecord_AfterUpdate()
On Error GoTo Err_Record_AU

Me.RecordsetClone.FindFirst "[ID] = " & Me![Cborecord]
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Record_AU:
    Exit Sub

Err_Record_AU:
    If Err = 3077 Then
        Resume Exit_Record_AU
     Else
         MsgBox Err.Description
            Resume Exit_Record_AU
End If
End Sub