X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How To Lookup Information From Another Table, Access 2016    
post Jun 24 2019, 11:18 AM

Posts: 1
Joined: 24-June 19

Hello everyone. I need some help and am somewhat of a novice. Very much a novice when it comes to code. I have a form which is built off a query pulls information from two related tables. The tables are clientinformation and fhlist. One of the fields on the form is the funeral home name which is contained on both tables but this is where it gets a little confusing. On the clientinformation table it is actually stored as a number because on another form the information was entered from a combo box and Access stored the primary key from the FHlist. In the FHlist it is the actual text name. Anyway what I am trying to do now to make it even more confusing is to get the corresponding FHvendorid, which is another field on the FHlist table to appear on my form for the current client. So basically I want access to lookup the fhvendorid based on the fhname information. Please help
Go to the top of the page
post Jun 24 2019, 11:36 AM

Access Wiki and Forums Moderator
Posts: 75,699
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

I hope you're just trying to display this information and not store it also. If so, there are a few ways to do it. There is a function called DLookup() you could try to use. Or, if you can include the information to display in the Row Source of the Combobox, you can use the Column property of the Combobox to display the values in the other columns of the selected row.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Jun 24 2019, 11:48 AM

UA Admin
Posts: 35,297
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

If I read your question correctly, you have a standard kind of table design--with one possible flaw. But let's review some basic concepts first.

ALL data is stored in tables. This is the "Data Layer" or "Data Tier" in any relational database application.

Forms are used to manage the data in those tables. This is the "Interface" in any relational database application.

In addition, you need code--either macros or VBA--to implement the logic behind your relational database applicaton. This is the "Logic Layer" in any relational database application.

So, your particular question addresses the actual relationship between tables. We can talk about the way you PRESENT that data in a form after we get a handle on that table relationship.

In one table, the one called FHList, you have a list of Funeral Homes by name. I assume there are other fields in that table, but the one of interest here is the FHName.

In addition to that field, you have a Primary Key field, possibly called ID if you accepted the default name Access offered when the table was created. It is probably an AutoNumber. The datatype of AutoNumbers is Long Integer.

In the second table, the one called clientinformation, you have what is called a Foreign Key field. It is--or should be--a Long Integer as well. That's because the way we store information about the relationship between the Funeral Homes in the FHList and the clientinformation.

This would be a standard design. So far so good.

Now, you need to DISPLAY the FH Name in a form which is bound to the clientinformation table. Note that this form should be bound ONLY to the clientinformation table, not to a query joining both tables. Again, we need ONLY the Foreign Key field in this form, but we can use it to DISPLAY the corresponding FH Name. So, we don't join both tables in a query for the form (properly stated "the form is bound to a table or query"). We only need to put a combo box on the form bound to the Foreign Key, i.e. the Long Integer field holding the FH ID field. I don't what name you gave it in clientinformation, but I'll assume something like FHID, or similar.

Now, the combo box allows you to DISPLAY multiple additional fields in addition to the field to which it is bound. And that means you can DISPLAY the name from FHList, even though the actual field in the table is the corresponding Foreign Key in the clientinformation table.

We do that by creating a query on FHList. This query has two fields: the primary key and the FHName field.

Use this query as the RowSource for the combo box.

Set the first column in the combo box to 0 width so that the Foreign Key is not shown. Set the second column in the combo box to a width that allows you to display the longest possible FH Name.

Now, when you want to assign a client to a Funeral Home, use the combo box to find the name of it, but when you select it, the combo box actually stores its corresponding Foreign Key in the clientinformation table.

Also, the VendorID will be handled exactly the same way.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    16th July 2019 - 10:12 AM