Combo Box synchronization - main form to subform?

[Guest]

Have main form containing a subform. Both are bound to linked tables. Main
form has Combo Box A set to "display a RECORD on the subform, based on
selection in Combo Box A." This works great-does just what I want to this
point! At the same time a RECORD selection is made from Combo Box A, I want,
also, to have Combo Box A to SYNCHRONIZE with Combo Box B in the subform to
display a value in Combo box B based upon the same value selected in Combo
Box A (that selects a record). Have tried several things to get
synchronization between Combo Boxes A & B; have searched Access
Newsgroups/Knowledgebases without success. Am using MS Access 2002, producing
Access 2000 database. Has anyone got any ideas? I could really use some help.
Thank-you!

Denny G.

 

[tina]

is ComboBox B unbound (nothing in the *ControlSource* property)?

please post the *RowSource* of each combo box.

what field in each RowSource contains the value that you want to match
between the two combo boxes?

 

[Guest]

Hi Tina and thanks for responding. I tried getting back to you sooner, this
newsgroup was unavailable for a few hours today. Combo A is unbound. Combo
B is bound to a field in the subform's table: tblServiceOrder, its Control
Source: txtContactName.

The Row Source for Combo A is qryProvider. The Row Source for Combo B is
qryContactName.

The field in the row source that I want to match between the two Combos is
ProviderID.

Thanks again, Denny G.

 

[tina]

so when you choose a certain provider in Combo A, you want only Contacts
from that provider to show in the droplist of Combo B on the subform,
correct?

if so, you need to set a criteria in Combo B's RowSource query, on the
ProviderID field, that refers to the ProviderID field of Combo A. is
ProviderID the bound column of Combo A? if so, set the criteria in Combo B's
query as
[Forms]![MyFormName]![Combo A]

if ProviderID is *not* the bound column of Combo A, it's a little trickier.
if i recall correctly, you can't refer to a control's Column property in a
query criteria. if you're not familiar with VBA, the easiest way around the
problem might be to create an unbound textbox control in the main form, i'll
call it txtID. you can set its' Visible property to No. set the
ControlSource source to
=[Combo A].[Column](2)
and change the column number to the correct number that refers to the
ProviderID column (note that combo box columns are zero-based, so column one
is (0), column two is (1), etc). then set the query criteria in Combo B to
[Forms]![MyFormName]![txtID]

hth

 

[Guest]

Hi back, Tina. Thanks! A quick glance of what you have here tells me I am
close to getting it. I've finally got things working with one minor glitch
I WILL overcome. I'll copy your suggestions and go from there.

Denny G.

so when you choose a certain provider in Combo A, you want only Contacts
from that provider to show in the droplist of Combo B on the subform,
correct?

if so, you need to set a criteria in Combo B's RowSource query, on the
ProviderID field, that refers to the ProviderID field of Combo A. is
ProviderID the bound column of Combo A? if so, set the criteria in Combo B's
query as
[Forms]![MyFormName]![Combo A]

if ProviderID is *not* the bound column of Combo A, it's a little trickier.
if i recall correctly, you can't refer to a control's Column property in a
query criteria. if you're not familiar with VBA, the easiest way around the
problem might be to create an unbound textbox control in the main form, i'll
call it txtID. you can set its' Visible property to No. set the
ControlSource source to
=[Combo A].[Column](2)
and change the column number to the correct number that refers to the
ProviderID column (note that combo box columns are zero-based, so column one
is (0), column two is (1), etc). then set the query criteria in Combo B to
[Forms]![MyFormName]![txtID]

hth

Hi Tina and thanks for responding. I tried getting back to you sooner, this
newsgroup was unavailable for a few hours today. Combo A is unbound. Combo
B is bound to a field in the subform's table: tblServiceOrder, its Control
Source: txtContactName.

The Row Source for Combo A is qryProvider. The Row Source for Combo B is
qryContactName.

The field in the row source that I want to match between the two Combos is
ProviderID.

Thanks again, Denny G.

 

[tina]

you're welcome, and good luck!
and btw, i neglected to mention that you *may* have to requery Combo B to
get the correct sublist, *only* if does not requery automatically when the
subform itself is filtered by the act of selecting a value in Combo A.

Hi back, Tina. Thanks! A quick glance of what you have here tells me I am
close to getting it. I've finally got things working with one minor glitch
I WILL overcome. I'll copy your suggestions and go from there.

Denny G.

so when you choose a certain provider in Combo A, you want only Contacts
from that provider to show in the droplist of Combo B on the subform,
correct?

if so, you need to set a criteria in Combo B's RowSource query, on the
ProviderID field, that refers to the ProviderID field of Combo A. is
ProviderID the bound column of Combo A? if so, set the criteria in Combo B's
query as
[Forms]![MyFormName]![Combo A]

if ProviderID is *not* the bound column of Combo A, it's a little trickier.
if i recall correctly, you can't refer to a control's Column property in a
query criteria. if you're not familiar with VBA, the easiest way around the
problem might be to create an unbound textbox control in the main form, i'll
call it txtID. you can set its' Visible property to No. set the
ControlSource source to
=[Combo A].[Column](2)
and change the column number to the correct number that refers to the
ProviderID column (note that combo box columns are zero-based, so column one
is (0), column two is (1), etc). then set the query criteria in Combo B to
[Forms]![MyFormName]![txtID]

hth

Hi Tina and thanks for responding. I tried getting back to you

sooner,
this

newsgroup was unavailable for a few hours today. Combo A is unbound. Combo
B is bound to a field in the subform's table: tblServiceOrder, its Control
Source: txtContactName.

The Row Source for Combo A is qryProvider. The Row Source for Combo B is
qryContactName.

The field in the row source that I want to match between the two Combos is
ProviderID.

Thanks again, Denny G.

:

is ComboBox B unbound (nothing in the *ControlSource* property)?

please post the *RowSource* of each combo box.

what field in each RowSource contains the value that you want to match
between the two combo boxes?


Have main form containing a subform. Both are bound to linked tables.
Main
form has Combo Box A set to "display a RECORD on the subform, based on
selection in Combo Box A." This works great-does just what I want

to
this

point! At the same time a RECORD selection is made from Combo Box

A,
I

want,
also, to have Combo Box A to SYNCHRONIZE with Combo Box B in the subform
to
display a value in Combo box B based upon the same value selected

in
Combo

Box A (that selects a record). Have tried several things to get
synchronization between Combo Boxes A & B; have searched Access
Newsgroups/Knowledgebases without success. Am using MS Access 2002,
producing
Access 2000 database. Has anyone got any ideas? I could really use some
help.
Thank-you!

Denny G.