using a form in a query

[is1ma1il]

Hi,

I am trying to query a table using a form which the user selects their desired options from a number of pull down, the person may not want to use all the pull down just some, can anyone help me on this, i have set up the form with the pull downs on which gives the user his selection criteria. i need to know how i get this to iteract with the query.

 

[KenReay]

Hi

At its most simple what you do is to keep the form open while you run the query (you can set its visible property to false if you wish) and in the criteria of each column which has a pull down list for the suer to choose from you put Forms!YourForm!PullDownListName

But this will only operate if all pulldowns have something selected.

How I would normally do this is to include an <ALL> option which returns a value of *, for each drop down, and make this the default, then use LIKE(Forms!YourForm!PullDownListName) in the criteria

Do you need more explanation? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[is1ma1il]

Ray, I would like more explaination if you dont mind. where is the <ALL> option? do you stick the like part in the query? what if one of the pull downs is blank

sorry ken

 

[KenReay]

Hi

By having an <All> option for each pull down, the pull down is never blamk, because I make if defauly to <all>

What I do is make each dropdown a two column drop down, for actual values I repeat the value in each column, but for the all option I put * in col 1 and <All> in col2.

I Achieve this by using a union query:

SELECT strVal, strVal As xVal FROM tblMyTable
UNION
SELECT &quot;*&quot; as strVAl, &quot;<All>&quot; as xVAl FROM tblMyTAble
ORDER BY strVal

as the source for teh combo, and I set the width of column1 to zero, so user does not see it

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK