Code doesn't work 3

[melaniews]

Will someone please tell me why this doesn't work? I can't find any information to tell me what I'm doing wrong. I copied the SQL directly from the query and it does work. I

Private Sub optFindStore_AfterUpdate()
Call SetFormSQL
End Sub

Sub SetFormSQL()

Dim strSQL

Select Case optControl
Case 1
strSQL -"SELECT tblLocation.strLocationName1, tblLocation.strAddress1, tblLocation.strAddress2, tblLocation.strCity, tblLocation.strState, tblLocation.strZip5, tblLocation.strZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.firearms, tblPurchase.ammo, tblPurchase.accessories, tblLocation.strBusinessArea FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID"
WHERE (((tblLocation.strRetailLocatorZip5) = [Enter Zip Code])) & Me.FindByZipCode & ";"
Case 2
strSQL -"SELECT tblLocation.strLocationName1, tblLocation.strAddress1, tblLocation.strAddress2, tblLocation.strCity, tblLocation.strState, tblLocation.strZip5, tblLocation.strZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.firearms, tblPurchase.ammo, tblPurchase.accessories FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID"
WHERE (((tblLocation.lngLocationID) = [Enter Location ID])) & Me.FindByLocationId & ";"
Case 3


End Select
Me.RowSource = strSQL
Me.Requery

End Sub


I'm aware that Case 3 is blank. But until I get case 1 and 2 working it doesn't matter

TIA
Melanie

 

[jebry]

Hi Melanie!

Try this in Case 3:

strCriteria = "YourNameField Like '*" & txtData & "*'"

I haven't tested this, but if it will work at all, it will work like this.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[melaniews]

Hi Jeff,

Perfect. It works!!!!!

Thanks,
Melanie

 

[jebry]

Hi Melanie!

Glad it works!

Jeff Bridgham
bridgham@purdue.edu