Here is my actual code that I have written based on the info I have understood so far. The reason I am confused is that I keep getting an error when I try to compile this code. The error is in the place where you put "Combo1" in your example. As I said, I am using a toggle button, so I used the name for each toggle button and set them equal to 1, 2, or 3. I get an error when it gets to the name of the button. It says "sub or function not defined" and highlights the name of the first toggle button (where you had combo1 in your example).
Sub SetFormSQL()
Dim strSQL
Select Case optControl
Case 1
strSQL = "SELECT tblLocation.strLocationName1, tblLocation.strRetailLocatorAddress1, tblLocation.strRetailLocatorAddress2, tblLocation.strRetailLocatorCity, tblLocation.strRetailLocatorState, tblLocation.strRetailLocatorZip5, tblLocation.strRetailLocatorZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.strMailingAddress1, tblLocation.strMailingAddress2, tblLocation.strMailingCity, tblLocation.strMailingState, tblLocation.strMailingzip5, tblLocation.strMailingZip4, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.FIREARMS, tblPurchase.AMMO, tblPurchase.ACCESSORIES, tblPurchase.FISHLINE, tblPurchase.TARGETS, tblPurchase.[PARTS REPAIR] 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 = 1 & ";"
Case 2
strSQL -"SELECT tblLocation.strLocationName1, tblLocation.strRetailLocatorAddress1, tblLocation.strRetailLocatorAddress2, tblLocation.strRetailLocatorCity, tblLocation.strRetailLocatorState, tblLocation.strRetailLocatorZip5, tblLocation.strRetailLocatorZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.strMailingAddress1, tblLocation.strMailingAddress2, tblLocation.strMailingCity, tblLocation.strMailingState, tblLocation.strMailingzip5, tblLocation.strMailingZip4, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.FIREARMS, tblPurchase.AMMO, tblPurchase.ACCESSORIES, tblPurchase.FISHLINE, tblPurchase.TARGETS, tblPurchase.[PARTS REPAIR] 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 = 2 & ";"
Case 3
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70 & Me.FindByBusinessName & ";"
End Select
Me.RowSource = strSQL
Me.Requery
End Sub
Also, you will note what I have in Case 3. I don't really think this is going to work but I haven't gotten that far in debugging this code to see what I need to do there. I'm just trying to call the find function off of the menu.
I really appreciate all your help.
Melanie