In a pickle, switched form recordsource to query but a field in "lookup table'' confused ... ?

[StargateFan]

I noticed earlier this morning in my main form that sorting not
correct and that the Me.OrderBy wasn't enough. Many posts I've
recently found in archives have now taught me that I should always
rely on a query rather than Me.OrderBy. Fair enough. Changed
recordsource to a query on the main table. Sorting now fine. But
I've run into a snag which seems to point to my having done something
wrong with the "lookup table" values of one field.

In my main form, the province shows up as two characters. The lookup
table values have an A2K primary key index #, the field for the two
character version of the province and then one for the full name. So,
for example, one of the entries in the "Provinces and Abbreviations"
table which is used in a query later is:
9 ON Ontario

The difficulty that I've just found is that in this main form, I see
the ON value for any records in Ontario and everything seems to work
fine there. When user is in field, s/he sees only the text values:
ON Ontario
and if this were chosen, only ON shows up in form. Okay so far.

Well, the query gave me trouble as an error box came up saying value
was invalid for some of the provinces. I didn't know what to do so
just re-selected the provinces from there. **One funny thing that
might point to answer is that in the query, the only thing user sees
is ON, and all other two-character abbreviations but no full names
(?).

When I just went back to the main form, the sort order now perfect but
in the fields where I'd added/changed the province while in the query,
I get a "9" instead of "ON", for example. I wanted to see what
happened next so I changed the "9" back to "ON" in the form and then
when I went back to query saw that the province field now shows up
blank! What on earth have I done now, pls? <g>

Thanks for any help. D

 

[Arvin Meyer [MVP]]

The particular example that you showed would benefit from a natural key,
instead of a numeric one. However, if you do use the numeric key, always
sort by the natural one. If you do use the numeric key, always use the
textual data in the query, so that the display is human readable.

Only in a combo or list box would you need the number key to select. Reports
and lookup forms only need the join result. In a combo or list box, set the
column width of the key field to 0" so that the first displayed column will
be human readable.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[StargateFan]

The particular example that you showed would benefit from a natural key,
instead of a numeric one. However, if you do use the numeric key, always
sort by the natural one. If you do use the numeric key, always use the
textual data in the query, so that the display is human readable.

Thank you. I didn't quite understand all of the above yet <g>, but
what I'm understanding is that that I could and should get rid of the
A2K numeric key. I went to the table and deleted it.

Only in a combo or list box would you need the number key to select. Reports
and lookup forms only need the join result. In a combo or list box, set the
column width of the key field to 0" so that the first displayed column will
be human readable.

I'm a little lost here as I had the number key and the column width
set to "0" but I had these problems. However, since I've deleted the
numeric field/primary key, both table and query seem to be working
fine. All I had to do was adjust the number of columns and the column
widths. What shows me that things are working better is that the
combo box in _both_ the table and the query now display the same and
correctly. Before, the query one displayed incorrectly and different
from the main table and I didn't know how to fix it there.

Just to get things right ... when I make what an Access videotape I
have says is a "lookup table" - i.e., a table that a combo box is set
to - I should never use a numeric field and I don't need a primary
key? I can live with that, it's just to get this straight in my head.

Phew, thank you for this! So far, so good. D

 

[Arvin Meyer [MVP]]

Just to get things right ... when I make what an Access videotape I
have says is a "lookup table" - i.e., a table that a combo box is set
to - I should never use a numeric field and I don't need a primary
key? I can live with that, it's just to get this straight in my head.

Not at all. This particular instance works better if the primary key is the
State abbreviation. The overwhelming majority of the time, you'll want to
use a numeric (long integer - generally an autonumber) primary key. And yes
you need a primary key to ensure that you can properly link to foreign key
values. The primary key also ensures uniqueness.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[StargateFan]

Not at all. This particular instance works better if the primary key is the
State abbreviation. The overwhelming majority of the time, you'll want to
use a numeric (long integer - generally an autonumber) primary key. And yes
you need a primary key to ensure that you can properly link to foreign key
values. The primary key also ensures uniqueness.

Ah, okay. I understand better now. Why I had in my head that a
primary key had to be a number, I just don't know <g>. It usu. is,
but it doesn't have to be. Okay, understood.

I just went back to the provinces table and made the province
abbreviation the primary key. Fortunately, as with US states, these
2-character abbreviations can't and are never the same ... on purpose.
So they are indeed safe to use as primary keys.

Thanks! D