Joining tables to make a Query

[Don]

II have a query behind a form that returns the records I expect. This query
is a combination of three tables. CostCenter, tblCostCenters and
tblBudgetSubType. CostCenter is the table I want to update using the form and
tblCostCenters is a table that is used only for description purposes it
describes the cost center. Example, one value which is in the CostCenter
table, Column CC is 13601 and the description is in
tblCostCenters.CostCenterDesc "Shoe". These two tables are joined by
CostCenter.CC and tblCostCenters.CostCenterIDNum. Using just these two
tables, I can update records in the form. When I add tblBudgetSubType, the
form/query is no longer able to update. In fact, adding this table to only
the CostCenter in the query makes the form/query unable to update regardless
of the way I join them. On the form I need the
tblBudgetSubType.BudgetSubtypeDescription to tell the user what the codes
mean. Therefore, on the form I need the descriptions in both of the tables
tblCostCenters.CostCenterIDNum and tblBudgetSubType.BudgetTypeDescription for
the form to make sense to the user. Is there another way to have this show up
on a form and have it relate to the record showing in the query behind the
form?

Thanks,

Dennis

 

[Bob Quintal]

II have a query behind a form that returns the records I expect.
This query is a combination of three tables. CostCenter,
tblCostCenters and tblBudgetSubType. CostCenter is the table I
want to update using the form and tblCostCenters is a table that
is used only for description purposes it describes the cost
center. Example, one value which is in the CostCenter table,
Column CC is 13601 and the description is in
tblCostCenters.CostCenterDesc "Shoe". These two tables are joined
by CostCenter.CC and tblCostCenters.CostCenterIDNum. Using just
these two tables, I can update records in the form. When I add
tblBudgetSubType, the form/query is no longer able to update. In
fact, adding this table to only the CostCenter in the query makes
the form/query unable to update regardless of the way I join them.
On the form I need the tblBudgetSubType.BudgetSubtypeDescription
to tell the user what the codes mean. Therefore, on the form I
need the descriptions in both of the tables
tblCostCenters.CostCenterIDNum and
tblBudgetSubType.BudgetTypeDescription for the form to make sense
to the user. Is there another way to have this show up on a form
and have it relate to the record showing in the query behind the
form?

Thanks,

Dennis

Absolutely easy.
You don't even need the query.
The methd is to use comboboxes for your linked fields.

You use the CostCenter table as the record source of your form.
For the tblCostCenters.Description, you change the costcenter.CC
textbox to a combobox. The rowsource for that combobox is
tblCostCenters, with the IDnum as column 0 and Description as column
1. The bound field is the CostCenter.CC, and the boundColumn is 0.

You put description into a separate textbox if you want, by putting
One line of Visual Basic into the combo box After Update event

me.[textboxname] = me.combobox.column(1)

Use the same technique to retrieve the ,BudgetSubtype description.