Full Version: Not showing Juntion table data
UtterAccess Forums > Microsoft® Access > Access Forms
I have a subform using a junction table as it's recordsource. The junction table has information I want to display as well as (fk)s to other tables with data I want to show. Unfortunatly instead of seeing the other data I'm just seeing the (fk) data. How can I get my subform to show the other data? In this case I want to see the Result and the Reason, but all I see is the ResultID and the ReasonID.
TestDateID (pk)
Employee_number (fk)
ReasonID (fk)
ResultID (fk)
ReasonID (pk)
ResultID (pk)
I'd appreciate any advice you can offer. You all have been a great help recently! (not that you're not Always a great help)
Depending on how your subform is set up, you can do one of two things:

1. Data entry form
  • Change a textbox that displays only the foreign key value into a combo box.
  • Set up the combo box's Row Source Type as a query, then build your query.
    Assuming this is something like a Customer field, the query should probably have only two columns; the first value will be the primary key value for the Customer table, and the second column would be the actual info that the user will see displayed in the combo box -- in this example, the customer name.
  • Next, set up the combo box's column count to 2 (or however many you needed), and set the column widths so the first one is essentially hidden: 0";1.5" would be a typical setup.

That's the basics for a data entry set up. Your users can use the combo box to change the selected customer.

Now, if you just need to DISPLAY the data in a non-interactive way, the subform should not just be driven by the junction table. You need to add the related table(s) that hold the details you want to display.
  • Set up a query to drive the subform.
  • The query should include the junction table along with the important info from the related tables that you want to display. For example, if you are storing the CustomerID in the junction table, then add the Customers table to your query (making sure the one-to-many link between your junction table and the Customers table is established), and add the CustomerName field to your query. You can now set up a textbox on your subform to display the CustomerName value.

Hope this helps,

Thanks for the feedback, Dennis. Since this particular subform is only to view the history I went with your advice to create a query. It worked (of course). I appreciate your help!
Glad to help!
Hi Dennis,
I have had some confusion in this same area regarding junction tables. More specifically what should be the record source for what (form/subform). I know that the data should be bound to the foreign keys in the junction table. In the cases I've tried, I usually have existing data in the 2 tables - I just need a form to relate the values to each other. So it's a little different from your example in that I need more that simple display.
To illustrate, I did a little quick'n'dirty file using actors and movies as a simple example of a many-to-many. So I threw in a few actors/actresses and a few movie titles to see if I could design a form that would tie them together. It was hardly a success.
Otried using a query to drive the subform as you mentioned and I got some error relating to Automation. So I changed the Record Source to Roles (the junction). That 'seemed' to work, but it left me with a Form and a Subform both sourced by the junction table, which doesn't seem right. But, in the Main Form, I need to see the Actor name from 'tblActors' but I want to store ActorID in junction 'Roles'. By the same token, in the subform I need to see the Movie name, but I want to store MovieID again in junction 'Roles'.
Could you take a quick look at the attachment and see where my logic is misplaced.
Hey Dennis,
looked at your situation and found adjusted the query; I saved it as qryRoles for comparison. I also Added a few items to your form and placed their Visible property to False. I'm not sure why this works, but in working with subforms it seems to link items together. I'm sure there's a probable explanation. I'll see if I can find it somewhere. I've attached the file.
Sorry for not getting back to you more quickly -- I assumed this thread was completed. You will get better results by starting a new thread of your own in the future, rather than chiming in at the end of someone else's. No biggie, just for future reference.
Here's how you set up a data entry form for actors and movies.
You need three tables: tblActors, tblMovies, and tblMovieCasts. The tblMovieCasts table is your junction table, and is structured like this:
MovieCastID [Primary Key, Autonumber]
intMovieID [long integer foreign key to the primary key of tblMovies]
intActorID [long integer foreign key to the primary key of tblActors]
Your data entry form should be based solely on the MovieCasts table. Your two data entry fields will both be combo boxes. The MovieID combo box will look up the movie by title and store the movie's primary key value. The data source would be something like this:
SELECT MovieID, strMovieTitle FROM tblMovies;
Set up the combo box to have two columns. Hide the MovieID field from the user by setting the column widths to 0"; 1.5" and you're done. Use a similar strategy for the actors combo box.
Hope this clears things up for you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.