synchronize two combo boxes

I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the
second Combobox2 to list those Agencies I can refer clients to. A given
agency may show up for any or all the services. I do not want to create one
table which lists each Agency (multiple times) for the different services
they may or may not provide. I have one Table listing the agency with Yes/No
columns for services provided. This will enable administrator to easilly
check whether a given agency currently supports particular service. Therefore
it appears I would need a separate row source query for each service in
Combobox1 to show list of available Agencies in Combobox2. Have played a bit
with using Select Case in the AfterUpdate event of Combobox1 without success.
Even with one case scenario am unable to "tell" Combobox2 its rowsource comes
from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice.
Larry

You are very close.
Both combos should be unbound. Using bound combo boxes for searching will
cause the value in the current record to change if a combo is bound.

A Select Case statement in the After Update event of combo1 would be an
ideal way to create a row source for combo 2. I don't know the names of the
Yes/No fields in your agency table, so I will just call them [Food],
[Utilities], and [Clothing] For example sake.

Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strReplace As String

strSQL = "SELECT AgencyName FROM tblAgency WHERE [TheField] = True;"

Select Case Me.Combo1
Case "Food"
strReplace = "Food"
Case "Utilities"
strReplace = "Utilities"
Case "Clothing"
strReplace = "Clothing"
End Select

strSQL = Replace(strSQL, "TheField", strReplace)

Me.Combo2.RowSource = strSQL

End Sub
--
Dave Hargis, Microsoft Access MVP


"tuesamlarry" wrote:

I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the
second Combobox2 to list those Agencies I can refer clients to. A given
agency may show up for any or all the services. I do not want to create one
table which lists each Agency (multiple times) for the different services
they may or may not provide. I have one Table listing the agency with Yes/No
columns for services provided. This will enable administrator to easilly
check whether a given agency currently supports particular service. Therefore
it appears I would need a separate row source query for each service in
Combobox1 to show list of available Agencies in Combobox2. Have played a bit
with using Select Case in the AfterUpdate event of Combobox1 without success.
Even with one case scenario am unable to "tell" Combobox2 its rowsource comes
from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice.
Larry

Larry

You did say "appreciate advice"...g

If you want to use "Yes/No columns for services provided", consider using a
spreadsheet instead.

If you are trying to let "administrators ... easily" work inside tables,
consider using a spreadsheet instead.

Access is a relational database ... you won't get easy use of its
features/functions if you insist on feeding it 'sheet' data (i.e., your
columns of Yes/No services).

Access tables are great for storing data, but lousy for data entry/etc. ...
that's what Access forms are for. Don't constrain yourself to table design
to match administrator needs -- that's a spreadsheet!

You did ask...!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"tuesamlarry" wrote in message
...
I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the
second Combobox2 to list those Agencies I can refer clients to. A given
agency may show up for any or all the services. I do not want to create
one
table which lists each Agency (multiple times) for the different services
they may or may not provide. I have one Table listing the agency with
Yes/No
columns for services provided. This will enable administrator to easilly
check whether a given agency currently supports particular service.
Therefore
it appears I would need a separate row source query for each service in
Combobox1 to show list of available Agencies in Combobox2. Have played a
bit
with using Select Case in the AfterUpdate event of Combobox1 without
success.
Even with one case scenario am unable to "tell" Combobox2 its rowsource
comes
from Combobox1. Do the Comboboxes need to be unbound? Would appreciate
advice.
Larry

Thank you
My Combobox1 gets its "list" of data from "Needs" table and puts the
selection into a "Service" table underlying the collection Form where the
Comboboxes reside. If these Comboboxes are unbound then how do the selections
I make get saved in the underlying table?
Larry

"Klatuu" wrote:

You are very close.
Both combos should be unbound. Using bound combo boxes for searching will
cause the value in the current record to change if a combo is bound.

A Select Case statement in the After Update event of combo1 would be an
ideal way to create a row source for combo 2. I don't know the names of the
Yes/No fields in your agency table, so I will just call them [Food],
[Utilities], and [Clothing] For example sake.

Private Sub Combo1_AfterUpdate()
Dim strSQL As String
Dim strReplace As String

strSQL = "SELECT AgencyName FROM tblAgency WHERE [TheField] = True;"

Select Case Me.Combo1
Case "Food"
strReplace = "Food"
Case "Utilities"
strReplace = "Utilities"
Case "Clothing"
strReplace = "Clothing"
End Select

strSQL = Replace(strSQL, "TheField", strReplace)

Me.Combo2.RowSource = strSQL

End Sub
--
Dave Hargis, Microsoft Access MVP


"tuesamlarry" wrote:

I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the
second Combobox2 to list those Agencies I can refer clients to. A given
agency may show up for any or all the services. I do not want to create one
table which lists each Agency (multiple times) for the different services
they may or may not provide. I have one Table listing the agency with Yes/No
columns for services provided. This will enable administrator to easilly
check whether a given agency currently supports particular service. Therefore
it appears I would need a separate row source query for each service in
Combobox1 to show list of available Agencies in Combobox2. Have played a bit
with using Select Case in the AfterUpdate event of Combobox1 without success.
Even with one case scenario am unable to "tell" Combobox2 its rowsource comes
from Combobox1. Do the Comboboxes need to be unbound? Would appreciate advice.
Larry

Thanks Jeff, yes I know the value of a relational database. I am creating one
to replace a spreadsheet currently used. The Agency table I have with the
multiple Yes/No columns for all the types of services offered is essentially
a static lookup table. In looking at the various solutions for synchronizing
ComboBoxes, they seem to be based on one table which lists all the possible
combinations . Therefore I would have to create a table which would repeat
Name of agency for each service it provided. If for some reason that agency
did not offer service for a time, I would have to delete that record and then
add it again when it restarted. Hence my choice of one table listing
Agencies, addresses, etc. and the services they offer.
Is there a better way for me to handle this?

"Jeff Boyce" wrote:

Larry

You did say "appreciate advice"...g

If you want to use "Yes/No columns for services provided", consider using a
spreadsheet instead.

If you are trying to let "administrators ... easily" work inside tables,
consider using a spreadsheet instead.

Access is a relational database ... you won't get easy use of its
features/functions if you insist on feeding it 'sheet' data (i.e., your
columns of Yes/No services).

Access tables are great for storing data, but lousy for data entry/etc. ...
that's what Access forms are for. Don't constrain yourself to table design
to match administrator needs -- that's a spreadsheet!

You did ask...!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"tuesamlarry" wrote in message
...
I Have looked at many examples but believe mine is a bit different.
Combobox1 lists services such as Food, Utilities, Clothing. I want the
second Combobox2 to list those Agencies I can refer clients to. A given
agency may show up for any or all the services. I do not want to create
one
table which lists each Agency (multiple times) for the different services
they may or may not provide. I have one Table listing the agency with
Yes/No
columns for services provided. This will enable administrator to easilly
check whether a given agency currently supports particular service.
Therefore
it appears I would need a separate row source query for each service in
Combobox1 to show list of available Agencies in Combobox2. Have played a
bit
with using Select Case in the AfterUpdate event of Combobox1 without
success.
Even with one case scenario am unable to "tell" Combobox2 its rowsource
comes
from Combobox1. Do the Comboboxes need to be unbound? Would appreciate
advice.
Larry

"tuesamlarry" wrote

In looking at the various solutions for synchronizing
ComboBoxes, they seem to be based on one table
which lists all the possible combinations .

You are mistaken -- that is not the case. Synchronized Combo Boxes are
almost always based on related tables, not a single table. I have never
implemented synched Combos using just one table.

Therefore I would have to create a table which would repeat
Name of agency for each service it provided.

As your premise is incorrect, you cannot draw a valid conclusion. (I guess,
in symbolic logic, they'd say, it implies any conclusion.)

Is there a better way for me to handle this?

Definitely. You'll have a table of agencies, a table of services, and a
table relating the two (called an intersection table or a junction table)...
I'm guessing that you may have multiple agencies providing a service, and
multiple services provided by an agency. That is a many-to-many
relationship, easy to have a first combo for picking the agency, and use the
value in constructing the query for a second combo listing the services it
provides. If an agency adds or drops a service, it is only the record in
the intersection table that will have to change, and only the one record --
a separate, trivially easy, form will do this in "jig time".

Larry Linson
Microsoft Office Access MVP