Custom Selects using Combo Box

[ywenz]

Hi, I need to create a form where one combo box exists for each field in my database. The user can then choose the criteria of each field, or "NONE" to exclude that field from the query. Then hitting the command button will bring up a report of the query result. I've done some tutorials with textboxes, but those only covered the WHERE clause, I guess now I need to mess around with the SELECT clause. Can someone explain to me how i can achieve this with combo boxes? Thanks

 

[JeremyNYC]

For each of your combo boxes, do something like this:
SELECT BankID, BankName
FROM tblBank

UNION

0 as BankID, ' -None- ' as BankName
FROM tblBank

ORDER BY BankName

In the code behind the button, only add to the WHERE string if the value is greater than 0.

if me!cmbBank > 0 then
strWhere = strWhere & " AND BankID = " & me!cmbBank
end if

and when you're done pasting them all together, do this

if len(strWhere) > 0 then
strWhere = mid(strWhere, 5)
call docmd.OpenReport("RptWhatever", _
acViewPreview,,strWhere)
else
call docmd.OpenReport("RptWhatever", _
acViewPreview)
end if

Hope this helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ywenz]

Thanks, but I'm not quite sure what this means:

SELECT BankID, BankName
FROM tblBank

UNION

0 as BankID, ' -None- ' as BankName
FROM tblBank

 

[JeremyNYC]

That's a sample of some sql to be the rowsource of a combo box. The point is that you select what you want to display and then UNION in a bogus record that will stand for not making a selection on this criteria. I must have been sleeping when I wrote it, though, because ' -None- ' should be ' -All- '.

Also, you probably just missed it in the cut and paste, but the ORDER BY phrase is important, as that makes the first one in the list be that All value.

Oh, and make the default value of the combo box be 0.

Of course, with different data, you'll have to tweak it, but this is the most common scenario.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ywenz]

Maybe I haven't explain clearly what I need to do. I don't need to specify criteria in the WHERE clause. I need to specify the fields to return in the SELECT clause. Maybe there's a way where I can concatenate all the fields the user selects from the form and put that string into the SELECT clause?