# vs text in combo box

[Guest]

I'm clearly missing something, and I want to believe I'm not the only new
user to have this problem, however, I couldn't find anything through keyword
search on the board. I'd appreciate your help.

In the Access class I took, I was taught that each table needed to have a
Primary Key, where the Primary Key was unique in each record. To obtain
this, it was suggested that the first field we create in a table is an ID_#
using Autonumber. So I have a table T States (ID# - auto; State - text;
StateAbbrv - text). In my form, I created a combo box using T States. On
the Form, for the properties of this box, I chose field 2 (State) as my bound
field, showing 1 column. However, when I do a dropdown window in the combo
box, all I see is the ID_#. I COULD remove the ID_# field ... but do I
really want to do that?

What am I missing?

Thank you. This board is great and the expertise here has helped me get
out of more then one jam.

 

[Guest]

In column widths property, put something like 0",2" assuming the id is the
first column. You could also just eliminate the id column completely by
feeding the combo box via a query rather than the entire table e.g. SELECT
State from [T States]

By the way, you'd be better off eliminating ALL special characters and
embedded spaces from table and column names; they can cause problems.

-Dorian

 

[Guest]

Thank you. And thank you for the advice on the columns, I didn't know this.

In column widths property, put something like 0",2" assuming the id is the
first column. You could also just eliminate the id column completely by
feeding the combo box via a query rather than the entire table e.g. SELECT
State from [T States]

By the way, you'd be better off eliminating ALL special characters and
embedded spaces from table and column names; they can cause problems.

-Dorian

I'm clearly missing something, and I want to believe I'm not the only new
user to have this problem, however, I couldn't find anything through keyword
search on the board. I'd appreciate your help.

In the Access class I took, I was taught that each table needed to have a
Primary Key, where the Primary Key was unique in each record. To obtain
this, it was suggested that the first field we create in a table is an ID_#
using Autonumber. So I have a table T States (ID# - auto; State - text;
StateAbbrv - text). In my form, I created a combo box using T States. On
the Form, for the properties of this box, I chose field 2 (State) as my bound
field, showing 1 column. However, when I do a dropdown window in the combo
box, all I see is the ID_#. I COULD remove the ID_# field ... but do I
really want to do that?

What am I missing?

Thank you. This board is great and the expertise here has helped me get
out of more then one jam.

 

[BruceM]

It is not a requirement that each table have a primary key, although it is
often the best choice. Also, there are choices other than autonumber. In
the case of states, the state abbreviation could work as the PK, although it
is a text field rather than a number field, and therefore somewhat more
complex to work with when you get to coding. You can set the indexed
property of any field to Required, No Duplicates. That option is not
limited to autonumber or other PK fields.
For a beginning Access class it is probably a good idea just to say that
each table needs a PK, and that autonumber is a good choice. You won't go
wrong with that approach, at least not for quite a while. As you learn more
about Access you will discover exceptions to that general rule. For
instance, invoices usually need to be sequentially numbered. Autonumber
will almost inevitably leave gaps in the numbering, so you will need to use
another system to generate a sequential number without gaps. That number is
unique, and is a reasonable choice for PK. There is probably no need for
two unique number fields.
Again, there is nothing wrong with your approach to the States table, but be
open to the idea of rules evolving as you learn more.

Thank you. And thank you for the advice on the columns, I didn't know
this.

In column widths property, put something like 0",2" assuming the id is
the
first column. You could also just eliminate the id column completely by
feeding the combo box via a query rather than the entire table e.g.
SELECT
State from [T States]

By the way, you'd be better off eliminating ALL special characters and
embedded spaces from table and column names; they can cause problems.

-Dorian

I'm clearly missing something, and I want to believe I'm not the only
new
user to have this problem, however, I couldn't find anything through
keyword
search on the board. I'd appreciate your help.

In the Access class I took, I was taught that each table needed to have
a
Primary Key, where the Primary Key was unique in each record. To
obtain
this, it was suggested that the first field we create in a table is an
ID_#
using Autonumber. So I have a table T States (ID# - auto; State -
text;
StateAbbrv - text). In my form, I created a combo box using T States.
On
the Form, for the properties of this box, I chose field 2 (State) as my
bound
field, showing 1 column. However, when I do a dropdown window in the
combo
box, all I see is the ID_#. I COULD remove the ID_# field ... but do I
really want to do that?

What am I missing?

Thank you. This board is great and the expertise here has helped me
get
out of more then one jam.