How to get results in lookup field from another lookup field? (1 Viewer)

[laurenk]

Hi - I am not a computer science person (I'm actually an English major), but I'm learning how to use Access so that I can build a database for my job. So, I apologize if there is redundancy in my explanation of this.

My database's purpose is to manage the rental properties within our city, so I have tables tracking the rental property itself, the licenses that have been issued for that property, the inspections for each unit within the property and so on.
My issue is that I have a drop down / combo box in my RentalProperties table that pulls data from another table with all of the valid addresses in the city. When building my inspections table, I want to have a drop down that only pulls the addresses that are part of a record in RentalProperties, not just all of the records from CityAddresses (AKA I only want to be able to choose from the addresses of rental properties instead of any address). When I use the look-up wizard create this drop down and try to pull an address from the RentalProperties table, it comes back with the ID number instead of the text because that's the data type stored in RentalProperties. I assume there is some way to add criteria to the look-up query so I can pull the text data from CityAddresses, but filter it to only show the ones that are part of a record in RentalProperties? But I'm totally new to SQL and haven't coded before, so what I've been reading online has been largely going over my head.

How do I get a drop-down field with the addresses in RentalProperties instead of the ID number?

Thanks for any help or suggestions!

 

[MajP]

An example db or at least a screen capture would help.
There are "Cascading Combos" where you select something from one combo and it limits selections in the second. Pick Ford in the 1st and get Ford models in the second
Or depending how you are set up you limit the selection based on some property of the current record.

The trick in a combo to save the ID but show the text is to return in the rowsource both the ID and the Text. Use the query builder to select both fields maybe sorted by Color.
1 Blue
2 Green
3 Yellow

Then the combo has these properties
Bound column: 1 (id)
Column Count: 2
Column Widths: 0:2" (where 2" is some width greater than zero)

You see the text "blue" because it is the only visible column. But it saves the ID since it is bound.

 

[theDBguy]

Hi. Welcome to AWF!

If you're using lookup fields at the table level and want to filter them using a criteria, I would recommend you stop using the lookup field and simply use a combobox on a form to have a filtered list of items for your record. You're not supposed to manually edit/add records directly using tables anyway. Using a form for data manipulation is considered best practice.

 

[Pat Hartman]

Ditto on the stop using table level lookup fields. This is obfuscating how Access is actually storing data and causing you to do the wrong thing. No matter what the column shows, the lookup column actually ALWAYS stores the ID. It NEVER stores the street address.

Once you have that sorted out, you will use a query that joins your property table to the city table on the AddressID field as the combo's RowSource. This join will restrict the items in the combo to only those which are your properties.

If you post your tables, someone will fix them for you.

 

[The_Doc_Man]

Since you are a novice, it is possible that you have "loose" nomenclature. There are several ways to do lookups in form context. One involves tables that have something called a "lookup field." This structure contains a hidden table to support the lookup, which usually involves translating a code into a name or vice versa. The key to take away from that is that the lookup table is hidden and therefore will be a literal pain in the toches to obtain. If this is how you were doing the lookup, undo it in favor of the method I will describe next.

Take the list of codes and values and make a small table out of them. Whichever way you do the lookup, make that field the primary key of that table. (I.e. if you have the code and want its meaning, the code will be the PK.) Then when you need to do a lookup from a form, use the form wizard to build a combo box based on that table so that you can select a name and show a code or vice versa, and in VBA you can use the DLookup function to do the translation in-line. AND in queries, you can JOIN the main table to the lookup table in a way that will cause the be in the query's list of fields.

 

[LarryE]

Hi - I am not a computer science person (I'm actually an English major), but I'm learning how to use Access so that I can build a database for my job. So, I apologize if there is redundancy in my explanation of this.

My database's purpose is to manage the rental properties within our city, so I have tables tracking the rental property itself, the licenses that have been issued for that property, the inspections for each unit within the property and so on.
My issue is that I have a drop down / combo box in my RentalProperties table that pulls data from another table with all of the valid addresses in the city. When building my inspections table, I want to have a drop down that only pulls the addresses that are part of a record in RentalProperties, not just all of the records from CityAddresses (AKA I only want to be able to choose from the addresses of rental properties instead of any address). When I use the look-up wizard create this drop down and try to pull an address from the RentalProperties table, it comes back with the ID number instead of the text because that's the data type stored in RentalProperties. I assume there is some way to add criteria to the look-up query so I can pull the text data from CityAddresses, but filter it to only show the ones that are part of a record in RentalProperties? But I'm totally new to SQL and haven't coded before, so what I've been reading online has been largely going over my head.

How do I get a drop-down field with the addresses in RentalProperties instead of the ID number?

Thanks for any help or suggestions!

As a new member, you may not be able to attach your file so we can review it, but if you will take a screenshot of your table and relationship structure, that will help a lot. Use Database Tools > Relationships to view them, then take a screenshot and copy it into a reply post.