Paramater to check multiple fields in a table 3

[Dophia]

Hello everyone:

I have a form where the user would enter various data known about a person to enable them to find that person's record. The table has a seperate field for three phone numbers....eg. Home_Phone_No, Work_Phone_No and Cell_Phone_No.

Is it possible to enter any of the phone numbers (home, work or cell) on the form in one field, but have it search all three fields of the table to find the person?

Currently, I have it searching only one field of the table, as follows:


Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "

If Not IsNull(Me.txtHomePhoneNumber) Then
strWhere = strWhere & " And [Home_Phone_No]= '" & _
Me.txtHomePhoneNumber & "' "
End If

If Not IsNull(Me.txtWorkPhoneNumber) Then
strWhere = strWhere & " And [Home_Phone_No]= '" & _
Me.txtWorkPhoneNumber & "' "
End If
DoCmd.OpenForm "LkkpPeople", acNormal, , strWhere

End Sub

Any help would be appreciated.
Sophia

 

[straybullet]

I would simplify things by having form LkkpPeople use a query as the recordsource. The query would then reference the single phone number textbox using OR...

Something along the lines of:

Select * from tblPeople
Where (Home_Phone_No = Forms!SearchFormName.txtSinglePhone) OR (Work_Phone_No = Forms!SearchFormName.txtSinglePhone);


This way, Sub OK_Click would merely need to open LkkpPeople:

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenForm "LkkpPeople", acNormal
End Sub

Let them hate - so long as they fear... Lucius Accius

 

[dhookom]

I much prefer the code method rather than placing references to controls in your query criteria. If I understand correctly, you want to enter one phone number into a text box and search all three phone fields.

Try something like:

Private Sub OK_Click()
Me.Visible = False
Dim strWhere As String
strWhere = "1=1 "
 
If Not IsNull(Me.txtPhoneNumber) Then
    strWhere = strWhere & " And ([Home_Phone_No]= '" & _
        Me.txtPhoneNumber & "' OR [Work_Phone_No]= '" & _
        Me.txtPhoneNumber & "' OR [Cell_Phone_No]= '" & _
        Me.txtPhoneNumber & "') "
End If

DoCmd.OpenForm "LkkpPeople", acNormal, , strWhere
  
End Sub

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[PHV]

Another way:

If Trim(Me!txtPhoneNumber & "") <> "" Then
    strWhere = strWhere & " AND '" & Me!txtPhoneNumber & "' In (" _
      & "[Home_Phone_No],[Work_Phone_No],[Cell_Phone_No]) "
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dophia]

Thank you Straybullet, Duane and PH!!! All of your suggestions work great.

Sophia