Two table Query

[injanib via AccessMonster.com]

Hi,

I have three tables (tblCustomer, tblContract, tblDetail).

fields are as follows:

1. tblCustomers:

CustomerID
CustomerName
Organization
Phone

2. tblContract

ContractID
CustomerID
ContractNumber
AwardDate

3. tblDetail

ModID
ContractID
ModDetail
CLIN
Amount

I have made a query using both tblCustomer and tblContract.
tblCustomer has a one to many relationship with tblContract using the
CustomerID field.

The query is the record source for my form.
the fields on the forms are

txtContractNumber
txtAwardDate
cboCustomer
txtOrganization
txtPhone

the row source of the cboCustomer is the tblCustomer table that automatically
fills in the txtOrganization and txtPhone fields upon selection from the
combo box.

what I would like to know is if I should use the CustomerID as the record
source for this field or txtCustomer and also which field to use as a bound
column.

My form has a subform too. the record source for it is the tblDetail.
do I need to make a relationship between the tblDetail and the Query or
tblContract? since the main form is based on the query? Any help is
appriciated.

 

[Steve Schapel]

Injanib,

The query that the form is based on should only have one of the CustomerID
fields included in its output. I recommend using the CustomerID field from
the tblCustomers table, because it is the primary key field there. But in
reality, because it is the basis of the relationship between the two tables,
tblCustomers.CustomerID and tblContract.CustomerID will always have the same
value.

This CustomerID field can then be the Control Source of the cboCustomer
control on the form.

I am not sure of your meaning about your question with the subform. The
usual approach here is to use the Link Master Fields and Link Child Fields
properties of the subform control to link the subform to the main form
current record. I assume this would be on the basis of the ContractID
field.

 

[injanib via AccessMonster.com]

Thank you very much for the helpful response.
I am clear now on both issues.

Injanib,

The query that the form is based on should only have one of the CustomerID
fields included in its output. I recommend using the CustomerID field from
the tblCustomers table, because it is the primary key field there. But in
reality, because it is the basis of the relationship between the two tables,
tblCustomers.CustomerID and tblContract.CustomerID will always have the same
value.

This CustomerID field can then be the Control Source of the cboCustomer
control on the form.

I am not sure of your meaning about your question with the subform. The
usual approach here is to use the Link Master Fields and Link Child Fields
properties of the subform control to link the subform to the main form
current record. I assume this would be on the basis of the ContractID
field.

Hi,

[quoted text clipped - 51 lines]

tblContract? since the main form is based on the query? Any help is
appriciated.