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

 

[chrissie1]

look carrefully the cahnges are small but important

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
"What a wonderfull world" - Louis armstrong

 

[KenReay]

Hi

You do not explain what 'does not work' means, but I think you will find the problem is the line:

WHERE (((tblLocation.strRetailLocatorZip5) = [Enter Zip Code])) & Me.FindByZipCode & ";"

which should be

WHERE (((tblLocation.strRetailLocatorZip5) = '" & Me.FindByZipCode & "'));"

This is assuming strRetailLocatorZip5 is a string, if it is defined as a numeric, you do not need the ' bounding it


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[melaniews]

I am pulling my hair out over this one. I see the subtle difference Chrissie and I've tried to get that " out between the end of the FROM and WHERE, and the stupid thing puts it right back in there. It forces a line break there and puts the " at the end of the FROM part. It won't let me delete it. When I do delete it, it comes right back.

Are there too many characters in this statement perhaps? I did delete some of the fields in the query for the purpose of posting here.

Ken, I'm looking at how you changed the code and I tried that also. I got an "unexpected expression" error that highlighted the single quote in the code.
Melanie

 

[chrissie1]

keep the " before the where and add this just before the where

strsql = strsql & "WHERE .... "What a wonderfull world" - Louis armstrong

 

[melaniews]

I really appreciate your time on this.

The " is actually at the end of the previous line, not before the WHERE. I tried to put it there and of course it will not let me do it.

It insists on that " at the end of the previous line.

TIA
Melanie

 

[chrissie1]

it was that " i was talking about leave it at the end "What a wonderfull world" - Louis armstrong

 

[melaniews]

Okay, that looks better. I'm still getting an error though.

Compile error: expected sub, function, or property

and highlights the first part of the statement

strSQL

Melanie

p.s. any recommended books would be helpful. I haven't found anything useful to learn what the rules are with VBA and I feel like I'm shooting in the dark most of the time.

 

[chrissie1]

it should be

dim strsql as string "What a wonderfull world" - Louis armstrong

 

[melaniews]

Got that, still same error. Here's the whole thing, nothing taken out (hope it's not too long)


Sub SetFormSQL()

Dim strSQL As String

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], tblLocation.strBusinessArea FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID"
strSQL = strSQL & "WHERE (((tblLocation.strRetailLocatorZip5) = [Enter Zip Code]))" & Me.FindByZipCode & ";"

Case 2

Case 3

End Select
Me.RowSource = strSQL
Me.Requery

End Sub


TIA
Melanie

 

[chrissie1]

Dim strSQL As String

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], tblLocation.strBusinessArea FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID"
strSQL = strSQL & " WHERE (((tblLocation.strRetailLocatorZip5) = " & Me.FindByZipCode & &quot);"

"What a wonderfull world" - Louis armstrong

 

[melaniews]

Yeahhhhh, I got all the way to the end that time.

Got another error though

Method of datamember not found

at the

Me.RowSource = strSQL

TIA
Melanie

 

[jebry]

Hi Melanie!

You need to set Me.RecordSource assuming you are changing the form which is indicated by using the keyword Me.

hth
P.S. Please note, the final solution given to you by chrissie1 is the same solution Ken gave you!

Jeff Bridgham
bridgham@purdue.edu

 

[melaniews]

Hi Jeff,

Thanks for your info. I added the line

me.recordsource = qry.....

with the name of the query where the info comes from. Is that what I was supposed to do?

I still get the same error on me.requery, but it accepts the new line with no problem.

tia,
Melanie
p.s. I see the similarity now in the answers of chrissie1 and ken. Don't know what I did wrong the first time

 

[jebry]

Hi Melanie!

Well it should work with

Me.RecordSource = strSQL
Me.Requery

Though it does seem a little odd to use a text box on the form to limit the record source of the same form. One quick question. What is it you are ultimately trying to do with the option group on the form? What it looks like you are doing is filtering the records by zip, location, etc depending on the choice of the user and on the input by the user. Then, by the code, the records will then be displayed on the same form.

Is this what you are trying to do?

Jeff Bridgham
bridgham@purdue.edu

 

[melaniews]

I have a form that brings up information on a particular customer. I want the user to be able to query the database and come up with a group of records based on either the zipcode, location id or any part of the company name. That is my ultimate goal. Is there an easier way?

Three buttons in the option group. One brings up a parameter query on the zip code, the second a parameter query for the ID and the third I would like to query the name of the company and use wildcards.

In each case it will bring up a group of records meeting the criteria. We're trying to find out if a record existsfirst of all -- and the address information may or may not match exactly. It's kind of complicated.

I would appreciate any ideas or suggestions on how to approach it differently.

TIA,
Melanie

 

[jebry]

Hi Melanie!

Well the easiest way in my opinion is to use two forms. The first form you will base on the table in question or on a standard select query and will display the data you want the user to see. The second form will have the option group and an invisible text box and button. In the After Update event procedure of the option group, use the following code:

Dim strLabel As String

Select Case YourOptionGroup.Value
Case 1
strLabel = "Please Enter the Zip Code"
Case 2
strLabel = "Please Enter the Location ID"
Case 3
strLabel = "Please Enter the Company Name"
End Select

txtData.Visible = True
lblData.Visible = True
lblData.Caption = strLabel

This will tell the user what to enter. Then, in the button click event procedure use the following:

Dim strCriteria As String

Select Case YourOptionGroup.Value
Case 1
strCriteria = "strRetailLocatorZip5 = '" & txtData & "'"
Case 2
strCriteria = "lngLocationID = " & txtData
Case 3
strCriteria = "YourNameField = '" & txtData & "'"
End Select

DoCmd.OpenForm "YourDataForm", , , strCriteria

This will set the criteria to limit the number of records returned to your general form.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[melaniews]

Hi Jeff,

Thanks for the above idea. It will be a couple of days before I get back in that office to work on this project. I'll try it out then. I may have questions about what goes where in the code. It looks much easier than what I was trying to do.

Thanks again,
Melanie

 

[jebry]

No problem Melanie. If you have any problems with the code I provided, let me know and we will work it out.

Jeff Bridgham
bridgham@purdue.edu

 

[melaniews]

Hi Jeff,

Thank you ever so much. I cannot believe how easy it was to make that code work. I've been working on it the other way for days. Thank you so much. I added a gotocontrol command and need to find the code for clearing the box (I have it written down somewhere), but other than that it works like a charm.

I have at least one other question. Is there a way to make the company name search a wildcard search?

TIA
Melanie