Possible to run a query against a value set in a form text-field?

[MrDeveloper]

Hi,
I have a form where users select a value from a list. This value is then stored in a text-field variable on the form itself. I then want to run a query that is based on the chosen value.

As a fictional example: Select a Car Manufacturer. Value displayed in the form text-field is then set to 'Mazda', so I then want the query to say "display all cars that are a Mazda".

My query therefore looks like:

SELECT [Cars].[Car_ID], [Cars].[Type], [Cars].[Engine] FROM Cars WHERE ((([Cars].Type)=[Forms]![frmReports]![Text4]))

Problem is, when I run it, the starting form successfully has the car model in the text-box at run-time but as the query is run (activated by button and subsequent report on the form), it prompts for the value of [Cars].[Type] to be entered in a pop-up box! I wish to automate this so the user simply specifies the type earlier on and can then run a report against the chosen form value rather than type it in each time.

Any advice where I am going wrong or whether this is simply impossible to achieve, very much appreciated!

Kind Regards,

 

[PHV]

No spelling issue on the [table name].[field name] in your query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrDeveloper]

Many thanks for the quick response.

Double-checked the spelling. Present and correct. SQL structure is exactly as:

WHERE (((

.[Field])=[Forms]![frmReports]![Text4]))
ORDER BY etc etc...

I assume it is possible for the query to do this without it prompting me. Anything I have missed?

 

[PHV]

When the query is launched, is frmReports an opened main form ?
What is the name of the asked parameter ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrDeveloper]

frmReports is open, yes.

Not sure by what you mean by the asked parameter? In the query there is a column that is not needed to be displayed in the query-results but I use it to have a condition attached (that all records use this column to match the value in the form text-field Text4).

Hope this information helps but do let me know if I have missed anything out to help explain what I have done so far.

Thanks,

 

[PHV]

it prompts for the value of
What is the exact spelling of the prompt ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrDeveloper]

Oh! I understand now. The pop-up prompt has the heading of
'Enter Parameter Value' then just above the box waiting for the user to enter the response it has the table and column info:

****Enter Parameter Value****

Cars.Model
[-------blank-------]
[--ok--] [--cancel--]

 

[PHV]

And the Cars table is requested in the FROM clause and Model is a field defined in the Cars table ?

I suggest you post the real SQL code and the relevant tables structure to help us help you ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrDeveloper]

Hi,
Full code would probably help, yes. Pasted below and hope it doesnt confuse the issue. Without the WHERE clause the query brings back the results perfectly and as expected. Keeping WHERE in causes the query to prompt for the value rather than taking it from the open form as I wanted.
Can confirm Volunteers.Project does exist in the Volunteers table, yes.

SELECT Volunteers.Vol_ID, Volunteers.VolSurname, Volunteers.VolFirstname, StaffMembers.StaffMember, VolunteerStage.Vol_Stage
FROM (Volunteers INNER JOIN StaffMembers ON Volunteers.StaffMemberID = StaffMembers.StaffMemberID) INNER JOIN VolunteerStage ON Volunteers.VolStageID = VolunteerStage.VolStageID
WHERE ((([Volunteers].[Project])=[Forms]![frmReports]![Text4]))
ORDER BY Volunteers.VolSurname;

Hope that helps things and really appreciate input on this - it affects the whole ability for the system to run reports!

Kind Regards,

 

[MrDeveloper]

Important Note:

My apologies. I have just re-checked the columns and noticed a mis-match. Re-did the column headings in both query and form and it now works a treat.

So sorry I missed this, although it was only what you said in your last post that prompted me to re-check everything and only when I started pasting the query in did I start to notice things were not right.

Thanks for asking me the obvious - there was me thinking it was some deep-rooted problem!

Kind Regards,

 

[PHV]

Can confirm Volunteers.Project does exist in the Volunteers table
Really ?
In the query designer you should have a Project column with display unticked and criteria cell with the (Forms] stuff.
Try to tick the display checkbox and look at the new select list in the SQL code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrDeveloper]

Hi,
Project column is there with the [forms] criteria and the SQL now handles the WHERE clause in the manner I wanted. Although I wanted to select by Project, I did not want to display the project column.

All works now I have sorted out the column headings being matched, so I appreciate the outside-of-the-box analysis.

Thanks for your help.

MrD