Combo Box problem

[Guest]

I have a main form with a subform. The main form is using query 1 as a record
source. Which contains a table called Tech with a field named Tech#. The
subform has a record source of query 2. Which contains a table called Task
with a field named Task#. I would like to have a combo box on the main form
that shows a list of all Tech#'s and names. I want to select a name/tech# and
be able to have the record on the mainform and subform linked. Currently,
when I select a tech# from the combo box the main form and subform do not
sync up. Also, when I use the thumbwheel on my mouse the records are not in
sync. Any ideas?

Thanks!!!

 

[Guest]

I forgot to mention that I also have a table called Technames which contains
all the names and Tech#'s.

 

[Graham Mandeno]

Hi Bruce

Is there a one-to-many relationship between Techs and Tasks? Forms/subforms
are usually used to depict a one-to-many relationship, with the "one" record
on the main form and the "many" records on the subform.

Since I'm not certain of the way your relationships are set up, I'll use a
simple Customers/Orders example: Let's say CustID is the primary key in the
Customers table and OrdCust is the related foreign key in the Orders table.

I think the only bit you have missing is the link between the main and
subforms.

The subform control has two properties: LinkMasterFields and
LinkChildFields.

LinkMasterFields should be the name of a control on the main form (or a
field in the main form's RecordSource) which contains the value on the "one"
side of the relationship.

LinkChildFields should be the name of the related foreign key in the "many"
side table.

In the Customers/Orders example, you could bind the main form to the
Customers table and the subform to the Orders table. You then set
LinkMasterFields to "CustID" and LinkChildFields to "OrdCust". Then, as the
main form record changes, the subform automatically displays the orders
related to that customer.

The main form does not need to be bound. You could have just a combo box,
"cboCustomer", on the main form, with a RowSource as follows:
Select CustID, CustName from Customers order by CustName

Then, set LinkMasterFields to "cboCustomer".

Now the combo can be used to select any customer and the subform will show
related orders.

Does this cover what you need?

 

[Guest]

Yes!! Many thanks...
--
Bruce DiPaola

Hi Bruce

Is there a one-to-many relationship between Techs and Tasks? Forms/subforms
are usually used to depict a one-to-many relationship, with the "one" record
on the main form and the "many" records on the subform.

Since I'm not certain of the way your relationships are set up, I'll use a
simple Customers/Orders example: Let's say CustID is the primary key in the
Customers table and OrdCust is the related foreign key in the Orders table.

I think the only bit you have missing is the link between the main and
subforms.

The subform control has two properties: LinkMasterFields and
LinkChildFields.

LinkMasterFields should be the name of a control on the main form (or a
field in the main form's RecordSource) which contains the value on the "one"
side of the relationship.

LinkChildFields should be the name of the related foreign key in the "many"
side table.

In the Customers/Orders example, you could bind the main form to the
Customers table and the subform to the Orders table. You then set
LinkMasterFields to "CustID" and LinkChildFields to "OrdCust". Then, as the
main form record changes, the subform automatically displays the orders
related to that customer.

The main form does not need to be bound. You could have just a combo box,
"cboCustomer", on the main form, with a RowSource as follows:
Select CustID, CustName from Customers order by CustName

Then, set LinkMasterFields to "cboCustomer".

Now the combo can be used to select any customer and the subform will show
related orders.

Does this cover what you need?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form with a subform. The main form is using query 1 as a
record
source. Which contains a table called Tech with a field named Tech#. The
subform has a record source of query 2. Which contains a table called Task
with a field named Task#. I would like to have a combo box on the main
form
that shows a list of all Tech#'s and names. I want to select a name/tech#
and
be able to have the record on the mainform and subform linked. Currently,
when I select a tech# from the combo box the main form and subform do not
sync up. Also, when I use the thumbwheel on my mouse the records are not
in
sync. Any ideas?

Thanks!!!