Cannot see on form data from more than one table

[Guedj54]

I have two tables (table_a and table_b).
Table a got 2 fields: ID, Description
Table b got 2 fields: ID, Comment
Table a and b are linked by ID.

I have one form.
I want to display on this form ID and description from table a and
also comment from table b.

What should I put in the control source or other property of the text
box that should display the comment on the form

Many thanks

 

[Wayne Morgan]

This could be done by creating a query and linking the two tables in the
query on the ID field then using the query as the Record Source for the
form. Set the fields from the query as the Control Source for the textboxes
on the form.

Now for the real question, why are these two items in two different tables
if the both refer to the same thing? If they don't both refer to the same
thing, then the IDs shouldn't match up and there would be no way to link the
two of them together.

 

[Guest]

Yes I did that (query) , but getting #name? in the text box instead of the data?

 

[Wayne Morgan]

What is the Control Source of the textbox? What is the Record Source of the
form? If you click into the Control Source box in the control's Properties
sheet and click the down arrow button, does the field show as an available
field? If so, if you select the field from here do you still get #name? Do
you only get #name when you are at a new record?

--
Wayne Morgan
Microsoft Access MVP

Yes I did that (query) , but getting #name? in the text box instead of the

data?

 

[John Vinson]

I have two tables (table_a and table_b).
Table a got 2 fields: ID, Description
Table b got 2 fields: ID, Comment
Table a and b are linked by ID.

I have one form.
I want to display on this form ID and description from table a and
also comment from table b.

What should I put in the control source or other property of the text
box that should display the comment on the form

Many thanks

Create a Query joining TableA to TableB by ID and display both fields.

Why two tables? Wouldn't it be a lot simpler to have one table with
fields ID, Description, and Comment?

 

[Wayne Morgan]

Ok, so the Record Source of the form is not the query so the field isn't
available which is why you are getting ?Name. If you want a calculated
textbox that does a lookup in a record source other than the form's Record
Source, try the DLookup function instead of using the query.

Example:
=DLookup("[Field Name]", "[Table or Query Name]", "Criteria")

The criteria would look something like
"[ID]=" & txtID
where txtID is a textbox on the form that contains the value of the matching
ID that you want to lookup.

 

[Guest]

Thanks, working now