Displaying result of query of secondary table within a single form

[Harlequin]

Hi there,

I'm something of a newcomer to MS Access so it's more than likely that my question is extremely elementary.

I have a form whose record source is a single table in my database. It's purpose is only to display in sequence,every record in this table. Trouble is, one particular field in this table (tblAttendees) is a foreign key in a foreign key - primary key relationship to a field in another table. The foreign key, is of datatype "number" and so it obviously corresponds with another number field in the parent table which is a primary key.

My problem is that rather than simply display the field's value for every record which would be simple enough and would of course simply be a integer, I want to be able to access the value assigned to another specific field in the particular record the foreign key points to in the parent table. Given that I don't know how to assign mutliple record sources to a given form or if it is even possible, I don't know a solution to my problem.

At the moment, I have a label box that in design view, has the following syntax:

="Attendee:" & [intTitleTypeID] & " " & [txtAttendeeFirstName] & " " & [txtAttendeeSurname]

The [intTitleTypeID] field is the foreign key. Given that I can't access the parent table, I can't actually access the field name within the parent table either.

Any suggestions as to how I might solve this? Can you embed the result of a query of another table within a label box in a form for example?

[JustJim]

Hi there,

I'm something of a newcomer to MS Access so it's more than likely that my question is extremely elementary.

I have a form whose record source is a single table in my database. It's purpose is only to display in sequence,every record in this table. Trouble is, one particular field in this table (tblAttendees) is a foreign key in a foreign key - primary key relationship to a field in another table. The foreign key, is of datatype "number" and so it obviously corresponds with another number field in the parent table which is a primary key.

My problem is that rather than simply display the field's value for every record which would be simple enough and would of course simply be a integer, I want to be able to access the value assigned to another specific field in the particular record the foreign key points to in the parent table. Given that I don't know how to assign mutliple record sources to a given form or if it is even possible, I don't know a solution to my problem.

At the moment, I have a label box that in design view, has the following syntax:

="Attendee:" & [intTitleTypeID] & " " & [txtAttendeeFirstName] & " " & [txtAttendeeSurname]

The [intTitleTypeID] field is the foreign key. Given that I can't access the parent table, I can't actually access the field name within the parent table either.

Any suggestions as to how I might solve this? Can you embed the result of a query of another table within a label box in a form for example?

This is where relational databases become fun and addictive!

You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.

Now make that query the table's recordsource and drag fields to your heart's content.

Have fun but don't stay up too late!

Jim

[Harlequin]

This is where relational databases become fun and addictive!

You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.

Now make that query the table's recordsource and drag fields to your heart's content.

Have fun but don't stay up too late!

Jim

Jim,

Are you saying that the record source should be an entire query that would query both tables thereby giving me access to whatever fields I needed? If so, what you're saying would make sense.

[Harlequin]

This is where relational databases become fun and addictive!

You need to design a query, using both tables (linked by their PK/FK relationship) and pick the fields you want to be on the form from either table.

Now make that query the table's recordsource and drag fields to your heart's content.

Have fun but don't stay up too late!

Jim

Jim,

I've done exactly what you suggested and it works perfectly. THANK YOU!!

[alpnz]

Jim,

I've done exactly what you suggested and it works perfectly. THANK YOU!!

Search for DLookup in the help system, You will find help that documents another great way to display related information, using VBA code, great for just one off situations, and or conditional display. E.g. only display the information, if the criteria of a series of Dlookups is true or false etc etc.
Welcome to the disease ... As Jim pointed out, this is where relational Databases get addictive.

Hi all ... yes yes I know its been nearly a year in excile, but then its the crazy season again .... heh he

[JustJim]

Jim,

I've done exactly what you suggested and it works perfectly. THANK YOU!!

My pleasure. Sorry about missing your second question, being on the "Far Side" of the world in relation to most people on this list, means that I miss the busy period.

Enjoy

Jim