Show field from a related table

[Guest]

This is just a simplified example of what I want to do. My goal is to have
my Access Form match the already developed hard copy datasheets.


I have a table called [sites] with 2 fields (Site_ID, State) and it is
related to a table called [visit] with three field (Visit_ID, Date, Unit).
The 2 tables are related using the ID fields where 1 site can have several
visits.

I have a Form that shows the [Site_ID] and this form has a subform that
shows the [Date] and the [Unit]. I also want the subform to show the [State]
field. Do I have have to base the subform on a query so I can show the State
on the form, or preferable is there a way to create a text box and populate
the control source with a query that will populate the [state] based on the
Visit_ID in the subform?

Thanks for the help

 

[Marshall Barton]

This is just a simplified example of what I want to do. My goal is to have
my Access Form match the already developed hard copy datasheets.


I have a table called [sites] with 2 fields (Site_ID, State) and it is
related to a table called [visit] with three field (Visit_ID, Date, Unit).
The 2 tables are related using the ID fields where 1 site can have several
visits.

I have a Form that shows the [Site_ID] and this form has a subform that
shows the [Date] and the [Unit]. I also want the subform to show the [State]
field. Do I have have to base the subform on a query so I can show the State
on the form, or preferable is there a way to create a text box and populate
the control source with a query that will populate the [state] based on the
Visit_ID in the subform?

Is the main form bound to the sites table? If so, then the
main form can easily have a text box (named txtState) bound
to the state field and the subform can use a text box
expression to display the state:
=Parent.txtState

If the main form is not bound to the sites table, then the
subform's record source query need to Ho9in to the sites
table so it can include the states field.