How to Implement Lookup Fields With Linked Tables

[tpmcd]

I'm working on an Access 2013 project that uses data stored in a SQL Server 2008 database. I need to present the user with a subset of data from a linked table.



In that same display I want the user to be able to have a drop down type box for each displayed record where they can select a description of a value in another linked table and have the main table updated with data from that second linked table. A simple example would be showing the user a list of customers and using the drop down type control to indicate the model number or color of a product that the user has in their location. Anytime a new product type is selected the linked table should be updated.

This may be a trivial feature to implement but I have a lot of SQL Server experience and little recent MS Access experience. Thank you in advance for any suggestions.

[ssanfu]

Welcome to the forum.....

So I'm the reverse of you. ACCESS experience, just getting into SQL SERVER Express 2008 R2 BE.

On your Access form, In design view, add a comb box control. (cancel the wizard, if it appears)
Open properties.
Click on the data tab.
"Control source" is the field it combo box is bound to
"Row source" is where the data comes from.
You have two options:
Click in the "Row source" field , then on the ellipsis
Select the table/query, add the fields to the grid, close the query design window. (You can switch to SQL view here and type in the SQL here also)

Or click in the "Row source" field and
enter in the SQL to get the data:

SELECT customerPK, CustomerName FROM Customers_T ORDER BY CustomerName ASC

In the combo box properties DATA tab, set the property "Limit to List" = True
In the FORMAT tab, set the "Column count" property to the number of fields in the query.
Since the PK field is the first (bound) field, Set the "Column WIDTH" to 0 (zero - won't be displayed) and 1. It would look like:
"Column WIDTH" 0",1"

Save
Then go to form view and test the combo box.

[tpmcd]

Steve, I appreciate your response. It got me closer but I did not explain what I was trying to do very well.

I want to show a table type view that contains a list of records from a query. In the query the user might supply a partial product name with a wildcard to get a subset of data, i.e.--"ABCD%'. The form would show something like:

[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]
[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]
[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]
[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]
[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]
[PRODUCT_NAME][DESCRIPTION][DROP DOWN LIST]

The query returns the first two columns. The [DROP_DOWN] list is a list from another table. When the user selects it a list of categories appears and once the user selects one a private key from that category is stored into a field of the product record perhaps something like 'category_id'.

[ssanfu]

I created an example dB that has two forms in it. Both forms use a query for the record source.
One form is in Continuous forms view. The record source is a query "qryC_Autos".
The other form is in Datasheet view. The record source is a query "qryD_Autos"

The table "tblC_Autos" does not have a look up field.

The table "tblD_Autos" does have a look up field.



Open both in design view to see what they look like, then switch to forms view.
After you look at the forms, open the tables and look at the color field. The values are exactly the same, you just can't see the actual value in the "tblD_Autos" table because of the look up field.

(First time I have ever created a look up field)

[tpmcd]

Steve --

I just briefly looked at this and I believe this may be exactly the example I've been searching for. I will look it over more thoroughly later today and try it out with linked tables, etc.

Wish I could buy you a coffee or a beer!

Tim