Filtering a Number of Comboboxes Before Their Forms are Opened(?)

[croy]

I'm trying to set up a "switch" in my db such that another
office can get an updated front-end from me, and the first
time they open the mdb, they can select their office,
causing several cbos on various forms to change their
rowsource (or change a criteria in their SQL) so that they
don't have to weed thru so many choices.

I've got a startup form with a cbo for selecting their
office, and I've made it sticky, like it should be. But
now, how to change what they see in the cbos on the other
forms?

 

[Marshall Barton]

I'm trying to set up a "switch" in my db such that another
office can get an updated front-end from me, and the first
time they open the mdb, they can select their office,
causing several cbos on various forms to change their
rowsource (or change a criteria in their SQL) so that they
don't have to weed thru so many choices.

I've got a startup form with a cbo for selecting their
office, and I've made it sticky, like it should be. But
now, how to change what they see in the cbos on the other
forms?

I suggest that you add a table (named Installation) with one
row. You can then save the office in a field (named Office)
in the table (this table might be useful for a few other
installation related values too).

Then, assuming the combo boxes' row source table also has a
field for the office where each entry cabe used, you can
fileter the row source queries using the installation
table's office field. Just set the row source table's
office field's criteria to:
=DLookup("Office", "Installation")

 

[J_Goddard via AccessMonster.com]

Hi -

To do what you are asking requires that the forms be opened in design view,
the combo box sources changed to match the office, and the updated forms
saved. While this can be done programmatically in VBA, it can be a pain.
Much easier would be to set and keep the "Office" value in a table, as
Marshall has suggested, then set the combo box sources in the form On Open
events to suit each office's requirements.

John

 

[Marshall Barton]

That is not so. It's not that big a deal to use one table
to filter records in another table and my earlier reply
demonstrated one way. Actually, there are several ways to
do it using the same row source in all installations. The
trick is to decide where to save the installation's Office
value.

If the Office value is saved in a custom database property,
then the query's criteria could use a trivial user defined
function the retrieves the saved value.

Using the Open event to set the combo box's RowSource
property is probably the most efficient, but it's unlikely
enough of a gain to warrant the additional code.

 

[croy]

That is not so. It's not that big a deal to use one table
to filter records in another table and my earlier reply
demonstrated one way. Actually, there are several ways to
do it using the same row source in all installations. The
trick is to decide where to save the installation's Office
value.

If the Office value is saved in a custom database property,
then the query's criteria could use a trivial user defined
function the retrieves the saved value.

Using the Open event to set the combo box's RowSource
property is probably the most efficient, but it's unlikely
enough of a gain to warrant the additional code.

Thanks to Marshall and John for their answers.

The "Installation" table that Marshall recommended was
actually in place, but with a different name. I'm trying
without success to understand why it didn't occur to me to
hook that up to the query feeding the cbos like Marshall
suggested... Sigh.

 

[croy]

Thanks to Marshall and John for their answers.

The "Installation" table that Marshall recommended was
actually in place, but with a different name. I'm trying
without success to understand why it didn't occur to me to
hook that up to the query feeding the cbos like Marshall
suggested... Sigh.

Ah. Now I remember....

Since there is some overlap between the offices'
jurisdiction, I set up the combo boxes' row source table
with a field for each office (there are only two now, and
there might be one more in a year or so), using "-1" and "0"
for values.

To continue with this, I would have to make the query select
which field to use on the fly. I don't even know if that's
possible!

And I'm not sure if further normalization would make this
any easier, either (brain is on strike this morning).

 

[Marshall Barton]

Ah. Now I remember....

Since there is some overlap between the offices'
jurisdiction, I set up the combo boxes' row source table
with a field for each office (there are only two now, and
there might be one more in a year or so), using "-1" and "0"
for values.

To continue with this, I would have to make the query select
which field to use on the fly. I don't even know if that's
possible!

And I'm not sure if further normalization would make this
any easier, either (brain is on strike this morning).

This is starting to sound like a normalization issue. How
about posting more information about these "jurisdictions"
and the combo boxes' row sources.

 

[croy]

croy wrote:


This is starting to sound like a normalization issue. How
about posting more information about these "jurisdictions"
and the combo boxes' row sources.

You're right about the normalization. When I finally bit
the bullet, and added the necessary tables, the pieces
started falling into place.

It appears to be working now.

Thanks for your nudge.