Displaying data from two tables in one form

[Guest]

I have a database which has four levels as follows

Clients - leads to
Sites - leads to
Batches - leads to
Samples

The client may have multiple sites and each site may have multiple batches
and so on. (Hope this is clear)
In the input form for batches I have a combo box which allows any batch to
be selected and this then updates the record for that batch into the form,
based on the batch I select, I would also like the client name to be shown on
the form as a reference for users. The clients name is only stored in the
Clients and Sites tables.
As before my knowledge of code is limited so answers in words of one syllable.

Many thanks

Richard

 

[Allen Browne]

So:
- Your form is bound to the Batches table.
- The Batches table has a SiteID field (relates to the primary key of the
Sites table.)
- The Sites table has a ClientID field (relates to the primary key of the
Clients table.)
The ClientName field will exist only in the Client table, but you want to
show it on this form.

Create a query bound to the Sites table, and include the Clients table as
well. It will need to return the SiteID as the first field, then the
SiteName as the 2nd field, then the ClientName (from the Clients table) as
the 3rd field.

Use this query as the RowSource for the combo in your form. Set these
properties for the combo:
Column Count 3
Column Widths: 0"; 2"; 0"
Bound Column 1
List Width 2.2"
The first and last fields (SiteID and ClientName) are zero-width, so the
combo will show only the site name.

Now add a text box, and set its Control Source to:
=[SiteID].Column(2)
Column() is a zero-based property (i.e. the first column is zero, so the 3rd
one is 2), so when you select a site in the combo, the text box will show
the client name from the hidden column.

 

[Guest]

The Combo box is now not working in updating the rest of the form with the
record selected, also the clients name does not appear.
In the control source for the text box you say to use [Site ID], is this the
name you would have given to the query created?

Thanks

Richard Nuttall

So:
- Your form is bound to the Batches table.
- The Batches table has a SiteID field (relates to the primary key of the
Sites table.)
- The Sites table has a ClientID field (relates to the primary key of the
Clients table.)
The ClientName field will exist only in the Client table, but you want to
show it on this form.

Create a query bound to the Sites table, and include the Clients table as
well. It will need to return the SiteID as the first field, then the
SiteName as the 2nd field, then the ClientName (from the Clients table) as
the 3rd field.

Use this query as the RowSource for the combo in your form. Set these
properties for the combo:
Column Count 3
Column Widths: 0"; 2"; 0"
Bound Column 1
List Width 2.2"
The first and last fields (SiteID and ClientName) are zero-width, so the
combo will show only the site name.

Now add a text box, and set its Control Source to:
=[SiteID].Column(2)
Column() is a zero-based property (i.e. the first column is zero, so the 3rd
one is 2), so when you select a site in the combo, the text box will show
the client name from the hidden column.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database which has four levels as follows

Clients - leads to
Sites - leads to
Batches - leads to
Samples

The client may have multiple sites and each site may have multiple batches
and so on. (Hope this is clear)
In the input form for batches I have a combo box which allows any batch to
be selected and this then updates the record for that batch into the form,
based on the batch I select, I would also like the client name to be shown
on
the form as a reference for users. The clients name is only stored in the
Clients and Sites tables.
As before my knowledge of code is limited so answers in words of one
syllable.

Many thanks

Richard

 

[Guest]

Allen

Have now understood the combo box bit, so please just a little help with the
text box and what [Site ID] would relate to. I have tried relating this box
to the query created but this is not working

 

[Allen Browne]

Your original post states that the Sites table leads to the Batches table.
I assume the Sites table has a primary key named SiteID.
I assume the Batches has a SiteID field that relates to Sites.SiteID.

I assume the subform is based on the Batches table.
The subform therefore has a field named SiteID.
You are using a combo bound to this field.
The RowSource for the combo is the Sites table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Have now understood the combo box bit, so please just a little help with
the
text box and what [Site ID] would relate to. I have tried relating this
box
to the query created but this is not working

I have a database which has four levels as follows

Clients - leads to
Sites - leads to
Batches - leads to
Samples

The client may have multiple sites and each site may have multiple
batches
and so on. (Hope this is clear)
In the input form for batches I have a combo box which allows any batch
to
be selected and this then updates the record for that batch into the
form,
based on the batch I select, I would also like the client name to be
shown on
the form as a reference for users. The clients name is only stored in the
Clients and Sites tables.
As before my knowledge of code is limited so answers in words of one
syllable.

Many thanks

Richard