Form Search Issue - Right Place

Oops typo on my part:

Replace this:
Me.rSource = rSource

With this:
Me.RecordSource = rSource
 
Const rSource = "SELECT Contacts.CustomerID, Contacts.FName, Contacts.SName, Contacts.Title, Contacts.OrgName, Contacts.AddrL1, Contacts.AddrL2, Contacts.TownCity, Contacts.PCode, Contacts.CountryRegion, Contacts.PhoneLL, Contacts.PhoneMob, Contacts.Fax, Contacts.EMail, Contacts.AssociationNumber, Contacts.Association FROM Contacts WHERE (((Contacts.FName) Like [First Letter of First Name] & " * ") And ((Contacts.SName) = [Surname]))"
Me.RecordSource = rSource
Me.Requery
 
I've rewritten the sql string for you:
Code:
Const rSource = "SELECT CustomerID, FName, SName, Title, OrgName, AddrL1, AddrL2, TownCity, PCode, " & _
            "CountryRegion, PhoneLL, PhoneMob, Fax, EMail, AssociationNumber, Association FROM Contacts " & _
        "WHERE (((FName) Like [First Letter of First Name] & '*') And ((SName) = [Surname]))"
 
When i click debug its showing like this:

Private Sub Searchcontacts_Click() <--This highlighted in Yellow.

Const rSource = "SELECT Contacts.CustomerID, Contacts.FName, Contacts.SName, Contacts.Title, Contacts.OrgName, Contacts.AddrL1, Contacts.AddrL2, Contacts.TownCity, Contacts.PCode, Contacts.CountryRegion, Contacts.PhoneLL, Contacts.PhoneMob, Contacts.Fax, Contacts.EMail, Contacts.AssociationNumber, Contacts.Association FROM Contacts WHERE (((Contacts.FName) Like [First Letter of First Name] & " * ") And ((Contacts.SName) = [Surname]))"
Me.RecordSource = rSource
Me.Requery

End Sub

Everything is showing fine.
 
Our message crossed again. I know where the problem is and I amended it in my last post. Copy and paste it into yours.

Also you may find that Me.Requery will cause the boxes to pop-up twice.
 
Great! Glad it's working.

If you feel you want to do things more effeciently, have a look at this later:

http://baldyweb.com/BuildSQL.htm

There's a sample search form from pbaldy - a VIP on this forum and also an MVP.
 
No need to run the query and prompt for anything.

Create two textboxes as previously mentioned and put them on the HEADER section of your form. Give them meaningful names.

Once you've done that, tell me the names of the textboxes and the name of your form.

I would like to know what your next step would have been as this is the exact thing I am trying to accomplish. I am only searching on Last Name and I want the continuous for to refresh with only those records whose last name i Like the name entered in the text box
 
Look through the examples we've created. What you probably need is the LIKE operator. Explain more what your problem is?
 
Look through the examples we've created. What you probably need is the LIKE operator. Explain more what your problem is?

I was having a major brain fart. I was able to accomplish it with the like statement. My big problem was actually figuring out where to put it. By placing it in the criteria row of the record sorce of the form it worked perfectly.
 

Users who are viewing this thread

Back
Top Bottom