Basing a query of a text field and combo box in a form

[JBrinovcar]

Hi there,

I have a form that has a text field and a combo box (address number and street name) that I'd like the user to use as a way to query all records from that particular combination. I've create the text box and combo box and named them txEAN and txESN. The form is names CustomerServiceCalls. In the criteria of the query, I've entered the following code for the address number and street name fields using the expression builder:

[Forms]![CustomerServiceCalls]![txEAN]

[Forms]![CustomerServiceCalls]![txESN]

When I enter an address number and a street name in the form and press the "run query" button, I get a popup that asks me to enter the parameter values for the address numbers and street names. It won't seem to run the criteria to pull that information from the form.

Any thoughts?

[zmbd]

You may have something mis-spelled.
Open the query editor
Switch to SQL view
Copy and paste the text there into a post here....
Please format the SQL script using the [CODE/] format in the tool bar.

You might find this an interesting read while you wait for us to take a look at what you post: Access Tutorial 10: Parameter Queries (PDF)
Section 10.3.3 starts showing how to reference the form in the parameters.

[JBrinovcar]

SELECT CustomerServiceCalls.[Customer First Name], CustomerServiceCalls.[Customer Last Name], CustomerServiceCalls.[Address Number], CustomerServiceCalls.[Street Name], CustomerServiceCalls.[Phone Number], CustomerServiceCalls.[Date of Call], CustomerServiceCalls.[Time of Call], CustomerServiceCalls.[Description of Complaint/Issue], CustomerServiceCalls.[Crew Responding], CustomerServiceCalls.[Supervisor Notified?], CustomerServiceCalls.[Date of Notification], CustomerServiceCalls.[Time of Notification], CustomerServiceCalls.[Action Taken], CustomerServiceCalls.[Additional Information Attached to Hardcopy], CustomerServiceCalls.Pressure, CustomerServiceCalls.[Flow Rate], CustomerServiceCalls.[Further Action Required?], CustomerServiceCalls.[Description of Further Action], CustomerServiceCalls.[Additional Information Attached to Hardcopy_1], CustomerServiceCalls.[Complaint Resolved?], CustomerServiceCalls.[Date of Resolution], CustomerServiceCalls.[Date of Resolution], CustomerServiceCalls.[Time of Resolution]
FROM CustomerServiceCalls
WHERE (((CustomerServiceCalls.[Address Number])=Forms!CustomerServiceCalls!txEAN) And ((CustomerServiceCalls.[Street Name])=Forms!CustomerServiceCalls!txESN));

[zmbd]

+ From your post I gussed that the form was open. What I didn't ask, and I should have just to verify, is if the form is open when the code or query is executed. If you close the form then the query will choke. The form may be hidden; however, it must be open.

++Double check the spelling here against your control name in the WHERE clause of the SQL:
"txEAN" ... should this be "txtEAN"

A few other things that strike me:
+ Your form name is CustomerServiceCalls
And your table name is CustomerServiceCalls
This may be confusing the SQL interpreter because of the way the SQL is written - shouldn't however, stranger things have happened (more about this follows).

+ In the query you have things such as
CustomerServiceCalls.[Customer First Name]
and
FROM CustomerServiceCalls

Ideally, IMHO, these should be fully qualified with square brackets so that you have:
[CustomerServiceCalls].[Customer First Name]
and
FROM [CustomerServiceCalls]

This includes the conditional in the WHERE clause of the SQL with a special notice that I do NOT have the square braces around the Forms part of the statement because this refers to an Access Object collection
CustomerServiceCalls.[Address Number])=Forms![CustomerServiceCalls]![txEAN]
(at minimum you should try altering the SQL so that the WHERE clause has this formatting for the form reference

So you would have (in part) (also note here, I've changed the dot (.) to a bang (!) - denotes part of a collection and not part of an object property.

SELECT [CustomerServiceCalls]![Customer First Name]
   , [CustomerServiceCalls]![Customer Last Name]
   , (...)
FROM [CustomerServiceCalls]
WHERE ((([CustomerServiceCalls]![Address Number])=
   Forms![f_CustomerServiceCalls]![txEAN])
   (...) ;

I would advise changing your form name to:
f_CustomerServiceCalls, at least from a human perspective it makes it less confusing as to what you are referring to - I have done so above as an example.

I notice that you are using spaces and special characters within the field names. Although allowed, this is not the best practice and may cause you other issues. ESPECIALLY the special characters such as the question mark, the exclamation mark etc... as often these have significance to the SQL engine

-Problem names and reserved words in Access (article by Allen Browne)

Learn about Access reserved words and symbols (Article by MS)
Access reserved symbols
(...)
The following symbols must not be used as part of a field name or as part of an object name:
. / * ; : ! # & - ? " ' $ %

The spaces between words is less of an issue; however, can cause you growing pains as you code, you MUST use the square brackets around the field names (best practice to do so anyway IMHO), and if you ever upsize to one of the Server based databases some of them may not like the extra spaces - in any case, extra work for you :) .

+ Just a suggestion, your field names are VERY long which makes your SQL and potentially your Macro script or VBA scripts very lengthy to program -- AND there is a character limit you may run in to depending on how you are developing your project. Thus you might consider changing things such as
[Description of Further Action] to something like [DofFrthrActn], use the description portion of the field in table design view to provide the nicer details (which will show up at the bottom of the access interface) and in your forms change the labels so that the user interface is friendly

[JBrinovcar]

Thanks for your reply! I'll try your suggestions and reply back.