General discussion


Query as filter for DoCmd.OpenForm

By p.w.d.stone ·
Senario: Search form (carSearchAdvanced) uses query (qryCarSearch) to filter results for main display form (carDetails).

The search form (carSearchAdvanced) contains two combination boxes (cmbMake and cmbColour), the row source of which is another query grouping entries made in the table fields Make and Colour. This is done to avoid duplication where more than one car has the same make or colour.

If the option box (optMake) is selected, the following code is used to open the form carDetails (bound to the cars table)-

"If optMake.Value = True Then DoCmd.OpenForm "carDetails", , "qryCarSearch"
End If"

This does open the form, but it always displays the first entry, and does not filter the results.

"SELECT Cars.FirstName, Cars.Surname, Cars.Make, Cars.Model, Cars.Colour, Cars.Registration, Cars.Type
GROUP BY Cars.FirstName, Cars.Surname, Cars.Make, Cars.Model, Cars.Colour, Cars.Registration, Cars.Type
HAVING (((Cars.Make)=[Forms]![carAdvancedSearch]![cmbMake]) AND ((Cars.Colour)=[Forms]![carAdvancedSearch]![cmbColour]));"

I have tested the query- and doesn't seem to be able to pull through the results of the combination boxes, presumably why the filter doesn't work. Am I trying to do something impossible, or have I just got the syntax wrong?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by p.w.d.stone In reply to Query as filter for DoCmd ...

I have tried setting the combination box contents as a private global string, and referencing this in the query instead, but this didn't work either.

Collapse -


by Tony Hopkinson In reply to Query as filter for DoCmd ...

Try a WHERE clause instead HAVING is for testing aggregates, sounds like HAVING is always coming back true.

Collapse -

by p.w.d.stone In reply to HAVING ?

I don't think this is the problem- the SQL was built by the Access query, and substituting "HAVING" with "WHERE" brings back a syntax error. I tried changing the command to run the query, as a troubleshooter to test this element, and it brings up input boxes asking for the fields. This to me suggests that the values from the form aren't being pulled across. Therefore the query is filtering without any parameters and, strangely enough, returning every record.

Any suggestions?

Collapse -

Well I don't get it then

by Tony Hopkinson In reply to

This should not be having clause.
The only way for it to be returning all records is for either having is always returning true or the entire clause is being ignored.
The statement should be
select ..
From ...
Where ...
Group By ...
that wasn't your syntax error was it ?
Where is before Group by but having is after it.

If it was just the values not being pulled across then it would be searching for empty strings or nulls or something.

Collapse -

by p.w.d.stone In reply to Well I don't get it then

Not having any prior knowledge of SQL I was relying on Access to build something that worked- clearly that was over optimistic. Having replaced the Access generated code with your suggested syntax, it has now started working. Thanks for that, you've been a great help,

Ps- I still can't figure out why it was bring up input boxes if you used to command to initialise the query direct, instead of using it to filter the second form. Any ideas?

Collapse -

By no means an access expert

by Tony Hopkinson In reply to

You can write a query so it will ask for the parameters in little poxy input dialog windows. They used a similar method to attach it to the form, so seeing as the form is not there when you run it direct, instead of saying something like form not loaded, or HUH ? it just treats the reference as an especially complicated variable name.
A lot of MS 'development' products are very tolerant of developer mistakes, while they deemed this a good thing in not putting off new devlopers with something too complex intitally, it doesn't teach you that you have made a mistake.
Keeping bashing away.
When I'm forced into using access (bring a big gun and three strong blokes for an effective attempt) I always use the SQL view option and type my queries in, It's quicker once you've learnt SQL and it does what you want, not what someone thought you might have wanted.

For instance if you were to mis-spell the name of one of your form variables when you run it from the form, it wouldn't surprise if you got the little input dialog.

Related Discussions

Related Forums