Lookup Fields in Forms

[NLR]

Hello,
I need some direction on how to set up a table and form to use several combo box controls in the form that will display the value not the id number in the table. I'll also need to use the values in a query later.
The fields contain address information. I have a table of all the information; name, address, city, ST and zip for each record. I would like to set up the form's combo box fields to pull in the information on the same record.
This will help the user save time entering the information and maintain accuracy.
I hope my request is clear.
I'm using access 2010.
Thank you in advance!

 

[Ranman256]

In the cboBox, Set the bound column to the text field,instead of the ID column
Or
Hide the ID column, and show the text columns.
Column widths=0;1;1

Once the user picks the ID, just filter the records:
Me.filter="[PersonID]=" & me.cboBox
Me.filterON = true

 

[NLR]

I'm not sure I understand how the filter would work?
I would create a cboBox for each field (name, address, city, ST, zip). Hide the ID column and add the filter for each field?

 

[moke123]

I have a table of all the information; name, address, city, ST and zip for each record. I would like to set up the form's combo box fields to pull in the information on the same record.

Maybe you should expand a bit on how your tables are set up.

This will help the user save time entering the information and maintain accuracy.

Are you repeating the same info in each record or are you only saving the Primary/Foreign Key?

 

[NLR]

I have a table FIList which contains all the names and addresses needed for the cboBox fields. This should be the source.
So for one record on the form there will be 5 fields that will use the FIList table. Each of these fields (name, address, city, ST, zip) on the form will hold a piece of the information of a record from a record on the FIList table. The information in the FIList table will be used on many records on the form.
The second table CustTrn contains the customer info as well as the associated fields for each customer from the FIList table. This is where the form's cboBox fields should store the value.
I hope this helps to answer your question.

 

[moke123]

i'm a little lost. Are you repeatedly storing the individual fields from FIlist in the CustTRN table? You should only be storing the primarykey of FIList in CustTRN. Or is there a reason to store the repetitive data? I think your design may be off.

 

[static]

You don't replace ID's with values in tables. You use a query to do that.


Open the table in design view.
select the field.
in field properties, select the lookup tab
display control = combo
row source = "select ID, valuefield from yourlookuptable" < change as required
column count = 2
column widths=0

wherever you use that field it should display the combo as you want without changing values to ID's

 

[Minty]

I would strongly advise against using lookup fields in your tables, they cause all sort of problems http://access.mvps.org/access/lookupfields.htm

As Moke has said it sounds like you are storing data you already have stored. This is considered very poor use of the way a database works.

 

[static]

I wouldn't normally advise it but I wouldn't strongly advise against it either.
Describing lookup fields as evil is a bit OTT IMHO.

Especially if the alternative is to denormalise the whole thing.

 

[Minty]

They aren't pure evil, but have a look on here for the confusion they cause.

I and many others always suggest simply storing the look up in separate table that you can get to easily and see what is happening. If you need to add an extra field to a look up table you can do so easily, a look up field you can't.

If you convert to SQL Server or something else - a look up field will have to be recreated anyway.

 

[static]

Tbh, I've never used them so I'll bow to your experience.

I don't see how defining a lookup field in a table would be any different to the combo/list sitting on a form. It's only a display thing. It could be confusing to other novice users fiddling with the tables I guess. Back when Access was good and you could lock them out you didn't have to worry about it so much.

 

[moke123]

I don't see how defining a lookup field in a table would be any different to the combo/list sitting on a form. It's only a display thing

With a combo and a lookup table its WYSIWYG, where as a lookup field is a hidden table that is more trouble than its worth. Every time i download someones database who's having troubles the first thing I do is look for Lookup Fields and its quite often the root of their problem.

 

[static]

Hmm. Well, Okay. I don't know. Maybe we are talking about different things.

The method I described looks up values from an existing table like a normal combo would.
It creates query rows in msysqueries like a normal combo would.

The stored ID is the same as the linked table.
I see no hidden tables.

The only bad thing I see about it is that if you were to drop and recreate the table you'd lose the settings. But the data would remain the same.



View attachment z.accdb

 

[moke123]

I see no hidden tables.

Of course not, its hidden!

Actually a poor choice of words. The actual value stored is hidden which inevitably is a PITA.