Trying to replace lookup fields

[Kastor]

After reading about the evils of lookup fields, I've been trying to rid
my database of them, but not with the desired results.

The suggested alternative is to use the RowSource property to populate
a combobox on the form.

I'm working on a CD database. In tblAlbums I had a lookup field Label,
which linked to tblLabels. The latter table has two fields: ID (primary
key, autonumber) and Label. So what I did is this: I deleted the
relationship between tblAlbums.Label and tblLabel.ID, changed the data
type from Number to Text, and on the Lookup tab, changed combobox to
textbox. Next, I put a combobox on my form with ControlSource =
tblAlbums.Label and RowSource = SELECT tblLabels.Label FROM tblLabels
ORDER BY tblLabels.Label. Note that the SQL statement does NOT contain
tblLabels.ID.

Result: not the label name, but a numeric value is stored in
tblAlbums.Label. This is not a problem as long as I'm working with the
form (where the numeric value is invisible), but when I generate a
report based on tblAlbums, I get a number instead of the label name,
which also means I cannot sort alphabetically on label.

What perplexed me most, is that even when I delete tblLabels.ID, Access
still stores a numeric value!

 

[Douglas J. Steele]

Id is what should be stored, not the text. It takes less space, plus if you
have to change the name of the Label, you only have to change it in one
place.

For reporting purposes, you need to create a report that joins the two
tables together so that you get the Label associated with each Id. You can
then sort on Label.

 

[Arvin Meyer [MVP]]

After reading about the evils of lookup fields, I've been trying to rid
my database of them, but not with the desired results.

The suggested alternative is to use the RowSource property to populate
a combobox on the form.

As Doug suggests, build a query. You can view the simple basics of query
building, including how to connect to a lookup table here:

http://www.microsoft.com/office/previous/xp/columns/column06.asp

Click on the "How and why do I build a query?" item. If you can locate the
old Access manual: "Building Applications with Microsoft Access 97", either
the book or the disk file, there are some good illustrations. If not, any of
John L. Viescas's Access books will have excellent query building
information, and/or you can ask John himself, as he hangs out on the
newsgroup: microsoft.public.access.queries
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Albert D.Kallal]

After reading about the evils of lookup fields, I've been trying to rid
my database of them, but not with the desired results.

The suggested alternative is to use the RowSource property to populate
a combobox on the form.

Well, actually, the suggest workaround is to use the combo box wizard on a
form....it is quick, and fast.

I'm working on a CD database. In tblAlbums I had a lookup field Label,
which linked to tblLabels. The latter table has two fields: ID (primary
key, autonumber) and Label. So what I did is this: I deleted the
relationship between tblAlbums.Label and tblLabel.ID

Why are you deleting relationships? You will can and should keep the
relationships you have. The only thing being said here to do is to NOT use
the TABLE DESIGN lookup feature. You most certainly can, and will, and
should use tables and relationships in your application. So, there is no
need to delete the relationship here. There is also NO need to modify, or
change the fields value either. The only thing you need to do there is
remove the lookup. Just open up the table in design mode, and on the lookup
tab, change the display control back to text box. You don't need to do ANY
thing else.

Now, to put a comb on a form...just use the wizard, and have the two columns
(id, that still gets stored, and the text description).

, changed the data
type from Number to Text,

You do not want to do the above, as now all places in your database
application will need to be changed. Leave it as a id, and in forms use the
combo box wizard to work with the id, but display the text. In a report, you
should likely use a sql query and simply drop in (join) those additional
tables with the text values that you need.