cascading combobox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mohan - 09 Feb 2007 16:23 GMT

I have three combo boxes on a form
combo1 = Organization region
combo2 = Division
combo3 = UserID (user names)

based on the combo1 selection, I set the rowsoruce for combo2 so it only
lists the divistion in the specified region (in combo1)
again, based on the selection of combo2, the combo3 rowsoruce is set so it
only lists the users in that division.

Now I need to pass all the userID in combo3 to a query
If the user selects one user from combo3, then its easy: you pass the the
value of the combo3 (which is userID)

However, if nothing is selected from the combo box, I need to pass all the
userID in combo3. How do I do that?

Thanks

Reply to this Message

Brandon Johnson - 09 Feb 2007 16:47 GMT

> I have three combo boxes on a form
> combo1 = Organization region
[quoted text clipped - 14 lines]
>
> Thanks

Use the SQL "IN": e.g. SELECT * FROM [whatever] WHERE userID
IN('cboindex1',cboindex2',...)
keep in mind that you only use single quotes for text fields, number
fields dont use the single qoutes.

Reply to this Message

Klatuu - 09 Feb 2007 17:01 GMT

That would be the correct way to do it except hard coding the indexes will
create problems if there are more or less items in the combo than in the
query. You can create the Where string by looping through though the
itemdata collection to dynamically build the values for the IN predicate.

Signature

Dave Hargis, Microsoft Access MVP

> > I have three combo boxes on a form
> > combo1 = Organization region
[quoted text clipped - 19 lines]
> keep in mind that you only use single quotes for text fields, number
> fields dont use the single qoutes.

Reply to this Message

Uncle Gizmo - 09 Feb 2007 18:22 GMT

You could use a union query, (this will only work if the combo boxes
unbound) and then use an if statement to check for the "*" if the star
is detected use one SQL statement if not use a different SQL
statement.

SELECT DISTINCTROW tlkpCustMup.CustMupID, tlkpCustMup.CustMupLongDesc
FROM tlkpCustMup UNION SELECT "*","<ALL>" FROM tlkpCustMup;

There is an example of cascading combo boxes here that may provide you
some inspiration:
http://www.tonyhine.co.uk/ms_access.htm

cheers Tony.

Reply to this Message