We help IT Professionals succeed at work.

MSAccess Setting  Combo box RowSource using both a table and separate items

Lambel
Lambel used Ask the Experts™
on
I have a report tool with a form where the admin can set up a list of email recipients.  The form has fields for Name; Email; Region.  The region field is a combo box with a row source from the tblRegions.  When a report is run for a specific region, an email goes out to the recipient listed. I also need two additional options offered for each recipient:  "ALL" and "NATIONAL".  So I need the rowsource for the Region combobox to offer all tblRegion records and these two additional selections.  Can someone suggest the best way to set this up?

Thanks,
Lynn
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Best way" is sort of dependent upon your programming skills and time, and developer opinion.

One "best way" would be to turn the combo box into a multi-select List Box instead, so users can grab any combination of records. You'd then need enough programming know how to feed the resulting array into your filter (say, into an "In(<array items list>)" where statement).

Another option is to create a subsidiary table that includes a list of all items in the tblRegion that are considered "National" (I'm assuming this is some subset of the total list but not as much as the ALL variation). Give users a checkbox that says "National Only" and when that's used, use the tblRegionsNational list instead of the tblRegion list. Another checkbox could represent the "ALL" in which case you run it wide open without a where filter.

Yet another choice is to add a National and All category to your tblRegion table and write a bit of code with a Case statement which creates the WHERE filter string as a result. It'd roughly be:

select case Forms!<formname>!<combobox field name>
Case "ALL"
 strWhere = "WHERE <fieldname> like '*'"
Case "National"
 strWhere = "WHERE <fieldname> in(<nat value1>, <natvalue2>...)"
Case else
  strWhere = "WHERE <fieldname> =" & Forms!<formname>!<combobox field name>
end select

As for "best way," - as you can see, there are a few (and I'm sure other people have more). As my dad says, "Always choose functional code over beautiful code."

-Rachel Morris

Author

Commented:
Thanks for your suggestions.

Lynn