Showing Transaction History in a Form with Foreign Key Values

[thadius856]

Hey all. First post. Hope this isn't too easy a question because I can't find the answer ANYWHERE on the interwebs with my current Access vocabulary.

BLUF: I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?

Background:
I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.

I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.

The problem is that three fields are foreign keys, so I'm displaying the foreign key id, which is meaningless to the user.

 

[shoji]

Hi thadius856,

You create a query in which you link the main table, tblTransactions, to other tables by those foreign keys and show the fields you want to show in the listbox. Use this query as the row source of the listbox.

Shoji

 

[thadius856]

Hi thadius856,

You create a query in which you link the main table, tblTransactions, to other tables by those foreign keys and show the fields you want to show in the listbox. Use this query as the row source of the listbox.

Shoji

Ah-ha! Took me a few minutes of fiddling and a refresher on multi-table queries, but I figured it out. In case anybody stumbles upon this from Google and is new like me, here's a step-by-step to make shoji's suggestion happen.

Before you start, I'm assuming that you already have a basic one-table query made and it contains multiple fields from that table. Also, I assume that you've already set up your foreign keys as relationships. If you haven't gotten this far, then it's probably safe to say that you need to finish designing the tables involved before you proceed to this query.

Let's have a go at it...

1. Open the query in Design View.
2. On the ribbon, go to the Design tab and click Show Table.
3. Double click the table that your foreign key points to. Repeat for all foreign keys. If you have multiple foreign keys from one table, you only need to add it once.
4. Example: Since I'm querying transactions for people checking items in and out, two people are involved -- the individual checking out the item and the individual receiving the item. Both are in my users table, so I only had to add it one time.
5. Double click the field name that holds the text you want to display instead of the foreign key. This adds it to the bottom pane.
6. Example: I was displaying UserId from my transactions table, but really wanted to show UserLastName and UserFirstName from my users table, so I added those two.
7. In the bottom pane, drag the fields to give a sane ordering. I'll touch on my preferred ordering at the end.
8. In the bottom pane, deselect the "Show" checkbox for the foreign key to hide it from your listbox.
9. Click 'Run' or switch to Datasheet View to test out your changes.

That's all there is to it! I thought it'd be harder.

I like my fields in the bottom pane to be in this order for cleanliness:

Main table's primary key
Foreign key #1

Data to display for foreign key 1​

Some more data to display for foreign key 1 (repeat as applicable)​

Foreign key #2

Data to display for foreign key 2​

...
etc
...

Enjoy!