Combo box problem

[Mary Beth]

I have two combo boxes on a form, each control has a lookup table with an ID
and a Name. The rowsource in the property box only selects the Name column
because I only want the name to show in the form and in the query, which
ultimately creates a spreadsheet with the table data. The problem is that I
still get the ID showing in the query, not the Name. This is what I put in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width 1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

 

[Jeff Boyce]

Mary Beth

A standard approach to comboboxes is to use an ID field and a more
descriptive field (?your [Name] field?). By setting the bound column to 1,
you store the ID. By setting the widths to something like "0;1", you get
the second, more descriptive field displayed on the form.

By the way, if your field is truly named "NAME", change it! Access treats
"NAME" as a reserved word, so what it thinks you mean may not be what you
think you mean....

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Piet Linden]

I have two combo boxes on a form, each control has a lookup table with anID
and a Name.  The rowsource in the property box only selects the Name column
because I only want the name to show in the form and in the query, which
ultimately creates a spreadsheet with the table data.  The problem is that I
still get the ID showing in the query, not the Name.  This is what I put in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width  1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

Yes. First off, don't use lookups in your tables. That's the
problem. Dropdowns etc are for *forms*, not tables. They're
deceptive... they store one thing and show something else. At form
level, that's not a problem, but at table level, I personally find it
really annoying and never use it.

 

[Ron2006]

still get the ID showing in the query, not the Name. This is what I put in

my property box:

What property box? There are all sorts of properties and events for a
combo box.

Ron

 

[Mary Beth]

Rest assured, I did not name the field Name, I just have it Name here to make
it simple, but thanks.

I am using a lookup table with the first column as intID, and the second
column as strCtyName.

The form's combo box control source is the field, strCounty, in the table,
tblCounty. In the combo box's property rowsource I have the syntax (which I
changed from previous)

SELECT tblCounty.intID, tblCounty.strCtyName FROM tblCounty
Column count 2
Column width 0",1"
Bound Column 2

What I want to happen is the combo box on the form to display county name,
which it is. But, I want to store the county name in the table as well.
However, it is storing the ID in the table instead. If there is already live
data in the db, and Access is storing the ID, will Access change the ID to
the county name?

I hope this is clearer. Can you help?

Thanks again,
Mary Beth

Mary Beth

A standard approach to comboboxes is to use an ID field and a more
descriptive field (?your [Name] field?). By setting the bound column to 1,
you store the ID. By setting the widths to something like "0;1", you get
the second, more descriptive field displayed on the form.

By the way, if your field is truly named "NAME", change it! Access treats
"NAME" as a reserved word, so what it thinks you mean may not be what you
think you mean....

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two combo boxes on a form, each control has a lookup table with an
ID
and a Name. The rowsource in the property box only selects the Name
column
because I only want the name to show in the form and in the query, which
ultimately creates a spreadsheet with the table data. The problem is that
I
still get the ID showing in the query, not the Name. This is what I put
in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width 1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

 

[Ron2006]

A) The form's combo box control source is the field, strCounty, in the
table,
tblCounty.

B) In the combo box's property rowsource I have the syntax (which I
changed from previous)


C) SELECT tblCounty.intID, tblCounty.strCtyName FROM tblCounty

===================================

I copied the above from your last posting.

Item B and C seem to be correct to me.

Item A seems wierd since you saying that the source is tblcounty. But
item A should be pointing to the table that is supposed be be updated
not the source of the combo.

If indeed the base table has the ID instead of the county then you
will have to write a separate query to update the ID with the county.
You will also have to make sure that that field is now a txt field and
not a number. It will change the ID to the county name for every
record that you actually touch and actually chose the proper county
using the form. That is why you will have to write the separate fix
query to back load all the prior records. New records will have the
correct field saved.

Also only update those records that the field containing ID matches
the ID number in the county table.

Ron

 

[Jeff Boyce]

Why bind to the text field? The ID field is the one you want to store in
your table.

If you use a combobox, bind the ID field (so it gets stored), and display
the text field, isn't that what you want to see in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Rest assured, I did not name the field Name, I just have it Name here to
make
it simple, but thanks.

I am using a lookup table with the first column as intID, and the second
column as strCtyName.

The form's combo box control source is the field, strCounty, in the table,
tblCounty. In the combo box's property rowsource I have the syntax (which
I
changed from previous)

SELECT tblCounty.intID, tblCounty.strCtyName FROM tblCounty
Column count 2
Column width 0",1"
Bound Column 2

What I want to happen is the combo box on the form to display county name,
which it is. But, I want to store the county name in the table as well.
However, it is storing the ID in the table instead. If there is already
live
data in the db, and Access is storing the ID, will Access change the ID to
the county name?

I hope this is clearer. Can you help?

Thanks again,
Mary Beth

Mary Beth

A standard approach to comboboxes is to use an ID field and a more
descriptive field (?your [Name] field?). By setting the bound column to
1,
you store the ID. By setting the widths to something like "0;1", you get
the second, more descriptive field displayed on the form.

By the way, if your field is truly named "NAME", change it! Access
treats
"NAME" as a reserved word, so what it thinks you mean may not be what you
think you mean....

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two combo boxes on a form, each control has a lookup table with
an
ID
and a Name. The rowsource in the property box only selects the Name
column
because I only want the name to show in the form and in the query,
which
ultimately creates a spreadsheet with the table data. The problem is
that
I
still get the ID showing in the query, not the Name. This is what I
put
in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width 1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

 

[Mary Beth]

Well, I am kind of new to Access, and I actually figured out what I was doing
wrong. For some reason I thought that when I changed the bound column to
store the name field, the ID's that were already stored would automatically
change to the county name. But, I have to do an update query (per a
co-worker) to change the ID to the name, if that is really what I want to do.
So, I kept thinking I was doing something wrong. The only reason I am
worrying about all of this is I have to produce an Excel spreadsheet with the
county names intead of the ID's. In my madness, I linked the lookup table to
the tblConsumer in my query to get the name instead of the ID, and that
worked. I think I will store the ID and get the name for Excel from the
query.

Thanks for all your help. Sorry if this was confusing.

Why bind to the text field? The ID field is the one you want to store in
your table.

If you use a combobox, bind the ID field (so it gets stored), and display
the text field, isn't that what you want to see in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Rest assured, I did not name the field Name, I just have it Name here to
make
it simple, but thanks.

I am using a lookup table with the first column as intID, and the second
column as strCtyName.

The form's combo box control source is the field, strCounty, in the table,
tblCounty. In the combo box's property rowsource I have the syntax (which
I
changed from previous)

SELECT tblCounty.intID, tblCounty.strCtyName FROM tblCounty
Column count 2
Column width 0",1"
Bound Column 2

What I want to happen is the combo box on the form to display county name,
which it is. But, I want to store the county name in the table as well.
However, it is storing the ID in the table instead. If there is already
live
data in the db, and Access is storing the ID, will Access change the ID to
the county name?

I hope this is clearer. Can you help?

Thanks again,
Mary Beth

Mary Beth

A standard approach to comboboxes is to use an ID field and a more
descriptive field (?your [Name] field?). By setting the bound column to
1,
you store the ID. By setting the widths to something like "0;1", you get
the second, more descriptive field displayed on the form.

By the way, if your field is truly named "NAME", change it! Access
treats
"NAME" as a reserved word, so what it thinks you mean may not be what you
think you mean....

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two combo boxes on a form, each control has a lookup table with
an
ID
and a Name. The rowsource in the property box only selects the Name
column
because I only want the name to show in the form and in the query,
which
ultimately creates a spreadsheet with the table data. The problem is
that
I
still get the ID showing in the query, not the Name. This is what I
put
in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width 1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

 

[Jeff Boyce]

Mary Beth

It sounds like you found the solution that Access handles most easily --
i.e., use a query to "show" the name field associated with an ID stored in
your main table.

Then you can export that query to Excel!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Well, I am kind of new to Access, and I actually figured out what I was
doing
wrong. For some reason I thought that when I changed the bound column to
store the name field, the ID's that were already stored would
automatically
change to the county name. But, I have to do an update query (per a
co-worker) to change the ID to the name, if that is really what I want to
do.
So, I kept thinking I was doing something wrong. The only reason I am
worrying about all of this is I have to produce an Excel spreadsheet with
the
county names intead of the ID's. In my madness, I linked the lookup table
to
the tblConsumer in my query to get the name instead of the ID, and that
worked. I think I will store the ID and get the name for Excel from the
query.

Thanks for all your help. Sorry if this was confusing.

Why bind to the text field? The ID field is the one you want to store in
your table.

If you use a combobox, bind the ID field (so it gets stored), and display
the text field, isn't that what you want to see in the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Rest assured, I did not name the field Name, I just have it Name here
to
make
it simple, but thanks.

I am using a lookup table with the first column as intID, and the
second
column as strCtyName.

The form's combo box control source is the field, strCounty, in the
table,
tblCounty. In the combo box's property rowsource I have the syntax
(which
I
changed from previous)

SELECT tblCounty.intID, tblCounty.strCtyName FROM tblCounty
Column count 2
Column width 0",1"
Bound Column 2

What I want to happen is the combo box on the form to display county
name,
which it is. But, I want to store the county name in the table as
well.
However, it is storing the ID in the table instead. If there is
already
live
data in the db, and Access is storing the ID, will Access change the ID
to
the county name?

I hope this is clearer. Can you help?

Thanks again,
Mary Beth



:

Mary Beth

A standard approach to comboboxes is to use an ID field and a more
descriptive field (?your [Name] field?). By setting the bound column
to
1,
you store the ID. By setting the widths to something like "0;1", you
get
the second, more descriptive field displayed on the form.

By the way, if your field is truly named "NAME", change it! Access
treats
"NAME" as a reserved word, so what it thinks you mean may not be what
you
think you mean....

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have two combo boxes on a form, each control has a lookup table
with
an
ID
and a Name. The rowsource in the property box only selects the Name
column
because I only want the name to show in the form and in the query,
which
ultimately creates a spreadsheet with the table data. The problem
is
that
I
still get the ID showing in the query, not the Name. This is what I
put
in
my property box:
Any ideas?

SELECT tblCounty.strCounty FROM tblCounty
Column width 1.0"
Columns 1
Bound Column 1

Thanks,
Mary Beth

 

[Ron2006]

The only caveat/caution is that you DO NOT want to ever delete one of
the counties from that table unless you first check to see if that
county ID is used in the other table.

Depending on how you make the query, if you are not careful, the
absense of the county record will cause the record NOT to show as a
record without a county but will simply not show up in the query at
all.

You will either have to make the county a required field in some
manner OR in that query make sure that the link says show ALL of the
main table records and only those of the county table that match by
ID.

Ron