Lookup table, have item #2 out of 3 appear in form?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

StargateFan - 30 Dec 2005 12:46 GMT

This is one that I've looked high and low for and I can't seem to
figure out what to do to fix things. I have a lookup table to provide
province abbreviations.

Column 1 is, of course, the primary key with A2K-generated numbers.
Column 2 in the table has the province abbreviations which consist of
two letters just like US states (i.e., BC stands for British
Columbia). The third column shows the complete names of the
provinces.

The idea is that users can see the second and third column in the
pulldown of the contact form so that they don't have to wonder if they
have the right province just by the 2-character letters; they have the
complete name, too. But I need the form to take the 2-character
abbreviation only, which it never seems to be able to do. I get the
display okay to show columns 2 and 3 and have successfully hidden
column 1, but nothing works to just get the 2-character province
abbreviation in the field.

Is there a way to do this?

Reply to this Message

Jeff Boyce - 30 Dec 2005 13:43 GMT

I'm not sure I saw what the form DOES do right now, just what it doesn't do.

Assumptions:
* you are working in a form
* you have a combo box, based on a table (you're calling it a lookup
table)

If correct, the Column Width property of the combo box controls whether you
see a column or not. If your ID field is the first in the query underlying
your combo box, set its Width to 0 (zero) to hide it. The first
non-zero-width column is the one that displays in the combo box.

Or am I still confused about your situation?

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> This is one that I've looked high and low for and I can't seem to
> figure out what to do to fix things. I have a lookup table to provide
[quoted text clipped - 16 lines]
>
> Is there a way to do this?

Reply to this Message

StargateFan - 30 Dec 2005 23:41 GMT

>I'm not sure I saw what the form DOES do right now, just what it doesn't do.

<g> Oops, sorry. It's just that it's done various things depending
on what I've played around with, but nothing gives me what I need.

I'll try again ... what we need is to have 2 columns visible to the
user - the province abbreviation (preferably this is in the first
column) and the province's full name in the second column.

No matter which province the user chooses, though, we need the
2-character information to be what is put in the field in the form.

I've had the primary key be dumped and the full province name but not
the 2-character one - and this is no matter where I have put the
column in the table and with or without the primary key hidden.
Baffling.

>Assumptions:
> * you are working in a form

Yes.

> * you have a combo box, based on a table (you're calling it a lookup
>table)

That's it (using terms from a video I have on Access. That's the term
used. Sorry it might not be correct term <g>).

>If correct, the Column Width property of the combo box controls whether you
>see a column or not. If your ID field is the first in the query underlying
>your combo box, set its Width to 0 (zero) to hide it. The first

Okay. I've done that several times. But despite being hidden,
sometimes it's been this field that is dumped into form!

>non-zero-width column is the one that displays in the combo box.

You'd think it would be that easy, eh?! <g> I can see both columns
in the combo box, but can't seem to figure out how to get the right
one to be dumped into the form.

>Or am I still confused about your situation?

No, you've got it. It's just that the results haven't been the
expected ones.

As mentioned, I don't know what I'm doing wrong but there must be
something I'm missing. I'm going to go back right now and try again.

Thanks. :oD

>> This is one that I've looked high and low for and I can't seem to
>> figure out what to do to fix things. I have a lookup table to provide
[quoted text clipped - 16 lines]
>>
>> Is there a way to do this?

Reply to this Message

John Spencer - 30 Dec 2005 14:02 GMT

I would just use two columns in the table. The Province abbreviation and the
Province names.

If you want to assign just the abbreviation to some control in a form.

Change the combobox (pulldown) to two columns (drop the primary key column).

The rowsource for the combobox should be
SELECT Abbreviation, Abbreviation & " - " & Province as FullName
FROM YourTable
ORDER BY Abbreviation

Change the column count to 2 vice 3
Hide the first column by setting the column width to 0 for the first column.

Make sure the control is bound to the appropriate field.

You can go with you current design, make sure that the control is bound to a
field in your destination table.

Reply to this Message