By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,736 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Displaying result of query of secondary table within a single form

P: 8
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:

Expand|Select|Wrap|Line Numbers
  1. ="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?
Dec 10 '07 #1
Share this Question
Share on Google+
5 Replies


JustJim
Expert 100+
P: 407
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:

Expand|Select|Wrap|Line Numbers
  1. ="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
Dec 10 '07 #2

P: 8
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.
Dec 10 '07 #3

P: 8
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!!
Dec 10 '07 #4

alpnz
100+
P: 113
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
Dec 10 '07 #5

JustJim
Expert 100+
P: 407
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
Dec 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.