Drop down box query

[jam]

Hi all I have a query which looks at the status of a project (of which there are 4 options), however the query forces me to type the status in rather than using the 4 options. Is there anyway of changing the query to prompt the user with a drop down box rather than a text box?

Cheers
jam

 

[pbaldy]

Not if you're using [Enter whatever] as your criteria. You'd need to create a form with a drop down, and have the query look there.

 

[jam]

Okey Dokey thanks for that.

What expression do I use in the criteria? Forms![formname].[dropdownbox] ?

God my DB is getting very messy with all these forms

Thanks for your help though, sorry if these are all neewb questions its been over 10 years since I last played with Access.

 

[pbaldy]

That would be the syntax, yes. I often use a single form to gather criteria for all my reports. I pass it the name of the report to open in OpenArgs. Since many reports require different criteria, I'll have controls on the form for all of them, but only make the necessary ones visible when I open it.

 

[jam]

Ok I'm having difficulty.

Now when I had [Enter open/closed/ongoing] in the criteria field on the query I got the results I wanted by using Access's default popup box and typing what I wanted into this box and it works.

Now I've changed it to...
Forms![StatusForm]![Status]
The above points to a drop down box which takes its list from the Landtable which can either be open/closed/ongoing.
But when I run the query it returns no results.

Any ideas?

When I created the drop down box it asked me whether I'd prefer to base it on values in a table or Enter them myself and I selected the table option is this where I'm going wrong?



Its started to work prefect no idea why
criteria = Forms![StatusForm]![Combo2]

 

[pbaldy]

Glad you got it sorted out. My guess would be that the first combo had more than one column, and the bound column wasn't the one expected by the query.

 

[ljw137]

Hi

This thread helped me with my problem but it's causing another issue.

The user gets directed to a form where they choose the desired value (called Module) from a combo box. They then click a button to run the query. The query refrences the value they have picked on the form plus also has start/end date user entry parameters.

Then it gets interesting - if the query returns a record then the table that lists all the Module names is somehow modified. It overwrites a (random) Module name in the table with the one that was chosen in the query. (If no records match the query then the table is unaffected.)

I have set the OpenQuery property to acReadOnly but that doesn't help.

My data integrity is now completely shot as it's taken me a while to realise what was happening. Can anyone please help?

 

[pbaldy]

What is the SQL of the query?

 

[ljw137]

Thanks for responding. The sql is;

SELECT requirements.reqId AS requirements_reqId, requirements.reqRef, requirements.reqShortName, requirements.reqDescription, reqModules.reqId AS reqModules_reqId, reqModules.modId AS reqModules_modId, Module.moduleName, Module.modDescription, Module.modId AS Module_modId, requirements.ModifyDate
FROM requirements INNER JOIN ([Module] INNER JOIN reqModules ON Module.modId = reqModules.modId) ON requirements.reqId = reqModules.reqId
WHERE (((Module.moduleName)=[Forms]![frmModule].[moduleName]) AND ((requirements.ModifyDate) Between [Enter Start Date (dd/mm/yy)] And [Enter End Date (dd/mm/yy)]));

I've just been playing some more and it appears to have been resolved by changing the moduleName field property to 'Index (no duplicates)'. I was expecting an error but didn't get one and have run it 5 times now. Still no idea what the cause is though.

I will test it again tomorrow as I have been starting at it so long today I fear I am no longer believing my eyes....

 

[pbaldy]

I don't see anything there that should change a value in a record. Is the form/combo bound to a table/field by chance? That could be the source of the problem.

 

[ljw137]

Hi
The combo is bound to a query which simply picks up the Module name field from the Modules table. Should I not have done it that way?

 

[pbaldy]

Probably not, though I'm not clear on your structure. Generally speaking, the combo box should have a row source that gets the appropriate values for selection, but the control source should be empty. Otherwise, changing the selection is changing the value of a record.

 

[ljw137]

Sorry but I'm not sure I understand what you mean. The row source of the combo box is ;
SELECT qryModulesForList.moduleName FROM qryModulesForList;

What sort of info would you expect to see in there instead?

Many thanks for your time.

 

[ljw137]

The control source of the combo box is moduleName. After rereading your post I changed that to be blank. Now the query no longer works - it returns no entries for when there should be many. Any idea what else I may be doing wrong.

 

[ljw137]

Think I've got it. When I removed moduleName from the Control Source Access appears to have renamed by combo box - which is of course referred to in the query. Have modified the query to pick up new name and everything looks good.

Thanks so much for your time.