One button to search multiple fields on a form

[SallyB]

Hi there, I have a form that allows for 3 mobile contact numbers. I understand how to create a search button that will open the specific record to display the data when only one field is being used in the search.

Is it possible to have the command button search Mobile1, Mobile2 and Mobile3 and return the record that contains the number?

I hope I'm making sense here and thanks in advance.

[gnawoncents]

If you are asking what I think you are, the answer is yes. Here is what I think you want (If not, please clarify):

Three fields with data
Mobile 1: 5555555555
Mobile 2: 1111111111
Mobile 3: 2222222222

If you want to find the record that has 2222222222 as a mobile contact number but do not care which of the three fields it is in, you could use code similar to the example below.

This code assumes the form is already connected to the source table and executes when a button (btnSearchNumber) is pressed. The search for information is entered into a text field named txtNumber but could easily be swapped for an input box or something else.

Private Sub btnSearchNumber_Click()
 
'Use phone number entered into field "txtNumber" as search criteria
Dim strSearchNumber As String
strSearchNumber = Me.txtNumber
 
Dim strGoToRecord As String
 
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveFirst
 
'Find the first record that matches the search number in Mobile1, 2, or 3
strGoToRecord = "[Mobile1] = " & strSearchNumber _
& " OR [Mobile2] = " & strSearchNumber _
& " OR [Mobile3] = " & strSearchNumber
rst.FindFirst strGoToRecord
 
Set rst = Nothing
 
End Sub