linking combo boxes

[Bob Parr]

I have a form that I want to use for editing, adding and removing entries in
a table. I have two of the fields that I want to use a combo box for. How
can I link these so that when a selection is made in either the proper field
for the same record will show in the other. Both combo boxes have as the
source, an index field and the specific field they are to display.

 

[Jonathan Parminter]

-----Original Message-----
I have a form that I want to use for editing, adding and removing entries in
a table. I have two of the fields that I want to use a combo box for. How
can I link these so that when a selection is made in either the proper field
for the same record will show in the other. Both combo boxes have as the
source, an index field and the specific field they are to display.

Hi Bob,

as you have not provided names please use the following as
an example..

form name: frmForm
first combobox name: cboCategoryID
columns in cboCategoryID: CategoryID, Category
second combobox name: cboItem
columns in cboItemID: ItemID, Item, CategoryID
criteria for CategoryID column in cboItem: =frmForm!
cboCategoryID

have the cboCategoryID_AfterUpdate() event requery the
second combobox: cboItemID.requery

Merry Christmas
Jonathan

 

[Bob Parr]

By
criteria for CategoryID column in cboItem: =frmForm!cboCategoryID

do you mean to set the Control Source for cboItem to frmFrom!cboCategoryID?

The problem here is that cboItem is slaved to CategoryID, wich is good, but
I cannot select anything in cboItem it is not just slaved, but locked to
CategoryID. I want a way to slave them together so that I can make the
selection in either combobox and have the change reflected in the other.

Bob

 

[Andy]

No, he means for you to set the criteria in the second cbo source statement.
This is what ties the 2nd cbo to the firt one.

Select ItemID, Item, CategoryID FROM <sourcename> WHERE CategoryID =
frmFrom!cboCategoryID;


Andy

 

[Bob Parr]

I want both tied to eachother. Peer to peer so to speak not master and
slave. I want the user to be able to select the record from either
combobox. Can I use this method on both comboboxes refering to eachother?

Bob

 

[Andy]

I don't think so. It sounds like chicken and egg to me. The first cbo cannot
refer to an item in the second cbo that hasn't been selected yet.

Each time the user selects an item from the first cbo, it requerys the
second cbo so the second cbo has a list of sub-items matching the first.

Andy

 

[Jonathan Parminter]

-----Original Message-----
I want both tied to eachother. Peer to peer so to speak not master and
slave. I want the user to be able to select the record from either
combobox. Can I use this method on both comboboxes refering to eachother?

Bob

Bob, the short answer is 'yes'. You can use the
AfterUpdate event of one combobox to change the rowsource
of the other combobox. You'll have to be careful to avoid
a circular reference.

sub cboCategory_AfterUpdate()
cboItem.rowsource="select ItemID, Item from tblitem where
cateforyid=" & cboCategory
end sub

sub cboItem_AfterUpdate()
cboCategory.rowsource="select CategoryID, Category from
tblcategory join .... where itemid=" & cboItemID
end sub

only you will know the joins required for the second
example. If your not sure construct a query that has the
required joins and then either copy into your code or save
as a query to use in your code as follows...

cbocategory.rowsource="select * from qryCategoprySource
where ItemID=" & cboItem

Luck
Jonathan