Solved Combo search with phone number (1 Viewer)

OfficialChad

New member
Local time
Today, 14:20
Joined
Feb 13, 2020
Messages
23
So i am trying to get my "sub form" to change to the data the user enters i know this is something you can do when you use a forum as a sub form but my "sub form" is coming from a qry as senn in "Annotation 2020-03-04 004253.png". Im not sure why i keep getting this see "Annotation 2020-03-04 003725.png"
this is the code.

if anyone would kindly be of help i am new to access and some what new to coding

Here is the code:
Code:
Private Sub numsearch_AfterUpdate()
Dim PhoneSearch As String
PhoneSearch = "select * form contacts where ([Mobile Phone]= " & Me.numsearch & ")"
Me.qryContactsExtended.Query.RecordSource = PhoneSearch
Me.qryContactsExtended.Query.Requery

End Sub
 

Attachments

  • Annotation 2020-03-04 003725.png
    Annotation 2020-03-04 003725.png
    18.3 KB · Views: 134
  • Annotation 2020-03-04 004253.png
    Annotation 2020-03-04 004253.png
    122.4 KB · Views: 145

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
Hi. Need to double check the name of your subform control.
 

OfficialChad

New member
Local time
Today, 14:20
Joined
Feb 13, 2020
Messages
23
Hi. Need to double check the name of your subform control.
but it seems to be the right name im not sure why im getting that error see i copied and pasted it
 

Attachments

  • nameofqry.png
    nameofqry.png
    84.2 KB · Views: 70

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
but it seems to be the right name im not sure why im getting that error see i copied and pasted it
No, you're showing me the name of the query, not of the subform control on your form (Me). You need the name of the subform control. Then, normally, it would look like this (I'm not sure if you're supposed to use Query):

Code:
Me.SubformControlName.Form.RecordSource=PhoneSearch
 

bastanu

AWF VIP
Local time
Today, 11:20
Joined
Apr 13, 2010
Messages
1,401
Try:
Code:
Private Sub numsearch_AfterUpdate()
Dim PhoneSearch As String
PhoneSearch = "select * form contacts where ([Mobile Phone]= ''" & Me.numsearch & "'')"
Me.sfrmContactsExtended.RecordSource = PhoneSearch
End Sub

As dbGuy said replace sfrmContactsExtended with the name of your subform control.

Please note that I assumed the Mobile Phone field is short text, if indeed is number remove the single quotes.

Cheers,
Vlad
 

Cronk

Registered User.
Local time
Tomorrow, 05:20
Joined
Jul 4, 2013
Messages
2,770
That code is wrong.

Should be

Private Sub numsearch_AfterUpdate()
Dim PhoneSearch As String
PhoneSearch = "select * form contacts where ([Mobile Phone]= [COLOR=rgb(184, 49, 47)][B]'[/B][/COLOR]" & Me.numsearch & "[COLOR=rgb(184, 49, 47)][B]'[/B][/COLOR])" 'Single quotes not double
Me.sfrmContactsExtended[COLOR=rgb(184, 49, 47)][B].Form[/B],[/COLOR]RecordSource = PhoneSearch
End Sub



Also, I find users are not consistent in entering phone numbers with some not including spaces, others with spaces in different places. Accordingly, I strip out spaces in the search and also allow for Like searches where a somewhat garbled phonenumber has been left on an answering service

Code:
PhoneSearch = "select * form contacts where (replace([Mobile Phone]," ", "") Like '*" & replace(Me.numsearch," ","") & "*')"
 

bastanu

AWF VIP
Local time
Today, 11:20
Joined
Apr 13, 2010
Messages
1,401
I swear I put single quotes in there.....I even mentioned them in the following note.
 

OfficialChad

New member
Local time
Today, 14:20
Joined
Feb 13, 2020
Messages
23
Thank you guys i figured it out was something to do with the way it was set up by the previous coder
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:20
Joined
Oct 29, 2018
Messages
21,358
Thank you guys i figured it out was something to do with the way it was set up by the previous coder
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom