Wrong Combo Box Value Being Stored in Table

[Guest]

Hi, I have Access 2003. I created a combo box using the wizard. I have
compared my combo box properties to the Northwind database combo boxes on the
products form. I do not see any differences between the my combo boxes and
those in Northwind. However, in Northwind I can select a product from the
drop-down list of the combo box and the product name is stored in the product
table. On my form (I am selecting a site) when I select the site name the
site ID is stored in the site name field of my table. Here is what I have:
Name: SiteID
Control Source: SITE_ID
Row Source Type: Table/Query
Row Source: SELECT DISTINCT SITE.SITE_ID, SITE.SITE_NAME, SITE.SITE_ACRONYM
FROM SITE ORDER BY SITE_NAME;
Column Count: 3
Column Heads: No
Column Widths: 0";0";1"
Bound Column: 1

When I change the bound column to 2, I get a error box popping up: The value
you entered isn't valid for this field. For example, you may have entered
text in a numeric field or a number that is larger than the FieldSize
setting permits.

The datatype for SiteID is numeric and the datatype for SiteName is text.

When I used the wizard, I selected:
I want the combo box to look up the values in a table or query.
I selected the Table: SITE
I selected all 3 columns in the SITE TABLE: SITE_ID, SITE_NAME, and
SITE_ACRONYM
I selected SITE_NAME to sort in ascending order.
I chose my column widths and checked the box to hide the key column
(SITE_ID) as the wizard recommended doing this.
I selected to "Store that value in this field" and chose "SITE_NAME"
I gave the combo box the name shown above.

Any clue as to why when I select a site name from my list, close the form,
open the table, and I see the site_id value inthe site_name field but not the
actual name?

Thanks for the help.

 

[Dirk Goldgar]

Hi, I have Access 2003. I created a combo box using the wizard. I
have compared my combo box properties to the Northwind database combo
boxes on the products form. I do not see any differences between the
my combo boxes and those in Northwind. However, in Northwind I can
select a product from the drop-down list of the combo box and the
product name is stored in the product table. On my form (I am
selecting a site) when I select the site name the site ID is stored
in the site name field of my table. Here is what I have: Name: SiteID
Control Source: SITE_ID
Row Source Type: Table/Query
Row Source: SELECT DISTINCT SITE.SITE_ID, SITE.SITE_NAME,
SITE.SITE_ACRONYM FROM SITE ORDER BY SITE_NAME;
Column Count: 3
Column Heads: No
Column Widths: 0";0";1"
Bound Column: 1

When I change the bound column to 2, I get a error box popping up:
The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field or a number that is larger
than the FieldSize setting permits.

The datatype for SiteID is numeric and the datatype for SiteName is
text.

When I used the wizard, I selected:
I want the combo box to look up the values in a table or query.
I selected the Table: SITE
I selected all 3 columns in the SITE TABLE: SITE_ID, SITE_NAME, and
SITE_ACRONYM
I selected SITE_NAME to sort in ascending order.
I chose my column widths and checked the box to hide the key column
(SITE_ID) as the wizard recommended doing this.
I selected to "Store that value in this field" and chose "SITE_NAME"
I gave the combo box the name shown above.

Any clue as to why when I select a site name from my list, close the
form, open the table, and I see the site_id value inthe site_name
field but not the actual name?

Your combo box's properties tell Access to store the numeric SITE_ID,
not the SITE_NAME, in the SITE_ID field to which the combo box is bound:

Bound Column: 1

.... means, store the value from the first column

Control Source: SITE_ID

.... means, store that value in the SITE_ID field

Row Source: SELECT DISTINCT SITE.SITE_ID, SITE.SITE_NAME,

.... means the first column of the combo box is the SITE_ID

This all sounds perfectly correct to me. Normally, if you are selecting
a site, and SITE_ID is the primary key of the SITE table, that's the
value you would want to store. You may want to *display* the site name
in a form or report, instead of the SITE_ID, but it's the key field that
should be stored in the table. Forms and reports are for displaying
data, so you generally want them to do the lookups necessary to convert
the stored SITE_ID to the corresponding site name. Tables are for
storing data, and you generally want them to show exactly what is really
stored. It's possible to tell Access to use a lookup field (a combo box
defined in the table design) to do the lookup and show you the site name
while storing the ID; however, I recommend that you don't do that. You
really want to be able to see exactly what's in the table, not a lookup
result.

The only odd thing is this:

I selected to "Store that value in this field" and chose "SITE_NAME"
I gave the combo box the name shown above.

The properties you listed above say that the combo box is bound to
SITE_ID, not to SITE_NAME. So I'm not sure how that came about.

 

[Guest]

Thanks for the prompt response. I understand your answer as meaning
everything is working correctly BUT why does the Northwind sample databse act
differently. In that sample database on the Products form, the combo box
named SupplierID shows only the Suppliers Company Name in the drop down list,
and after selecting a company name, I can close the form, open the Products
table, and there is the new company name I selected for the product record I
was messing with. The SupplierID combo box on the Products form is all based
on the SupplierID yet in the Product table the SupplierID the ID does not
show, just the company name. That is what I want to occur. I cannot find
any coding for this products form in the after_update section, or any other
section.
I have a table named "Interpretation" which in my mind corresponds to the
"Products" table in Northwind.
I have a table named "Site" which in my mind corresponds to the "Supplier"
table in Northwind.
When you select the supplier name, which is the only thing showing the combo
box, the "Products" table field named "Supplier" is updated with the
supplier's name ALTHOUGH the combo box bound column is supplierID.
When I do this with my form, the "Interpretation" table field named" Site"
is populated with the siteID #, not the site name.

This is why I am confused: I get a number, yet Northwind gets a name.

Andrew

 

[Dirk Goldgar]

Thanks for the prompt response. I understand your answer as meaning
everything is working correctly BUT why does the Northwind sample
databse act differently. In that sample database on the Products
form, the combo box named SupplierID shows only the Suppliers Company
Name in the drop down list, and after selecting a company name, I can
close the form, open the Products table, and there is the new company
name I selected for the product record I was messing with. The
SupplierID combo box on the Products form is all based on the
SupplierID yet in the Product table the SupplierID the ID does not
show, just the company name.

In the Northwind database, the Products table does what I referred to,
but don't recommend:

If you open the Products table in design view, click on the SupplierID
field to select it, and then click on the Lookup tab in the property
sheet for the field, you'll see that they've specified that the table's
datasheet is set to use a combo box to display the field, with
properties for the combo box set up so that it stored the SupplierID but
displays the CompanyName from the Suppliers table.

That is what I want to occur.

Despite the fact that Microsoft has done this in the Northwind database,
I do not recommend it, for a variety of reasons. The simplest one to
understand is that there's no real need to do it, because you should not
be using table datasheets to display information to users. But this
page explains more about why most developers disapprove of this
technique.

http://www.mvps.org/access/lookupfields.htm

Now, if you're just building a very simple database for your own
personal use, using lookup fields in the table design may be convenient
and not cause you any real pain. But if you're using forms to enter and
view data, you really don't need them. Combo box lookups are a terrific
user-interface tool -- on forms.

 

[Guest]

I figured it out. I noticed in the Northwind sample database the Products
table had in table view two fields named Supplier and Category. However, in
design view those two fields did not exist! But, there were two fields named
SupplierID and CategoryID. I saw in the General tab of the Field Properties
section of these two field these fields had captions of Supplier and
Category. I then noticed on the Lookuptab the Display Control was set to
Combo Box, and the other properties had values also. I changed the Field
Properties of my table field to similar settings and everything started to
work!

The only question in my mind now is how would I have known to set the Lookup
properties to combo box when I created the table?

 

[Dirk Goldgar]

I figured it out. I noticed in the Northwind sample database the
Products table had in table view two fields named Supplier and
Category. However, in design view those two fields did not exist!
But, there were two fields named SupplierID and CategoryID. I saw in
the General tab of the Field Properties section of these two field
these fields had captions of Supplier and Category. I then noticed
on the Lookuptab the Display Control was set to Combo Box, and the
other properties had values also. I changed the Field Properties of
my table field to similar settings and everything started to work!

The only question in my mind now is how would I have known to set the
Lookup properties to combo box when I created the table?

Please read and consider my reply to your previous message.

 

[Guest]

There was some "reply overlap" going on here. Ok, I went to the link you
posted and agree about the problems of using a combo box in a field in a
table, as Microsoft does in the Northwind database.

The question then becomes, "How do I get same result as Microsoft gets
without doing it like they did, putting a combo box in a table?" Do I need
to do some VB coding int he after_update section of the site name control I
place on the form?

 

[Douglas J. Steele]

Why would you want to store the name redundantly? You've got the Id, which
is sufficient to allow you to create a query that links the two tables when
you need the name.

 

[Guest]

Hmmmm...I do not want to store the name redundantly. And, the user should
never have to open the Interpretations table directly. If an interpretation
needs to be edited, or viewed, the user will use a form to edit an existing
interpretation and a report or form to view an exisiting interpretation.

 

[Guest]

Thanks to both of you. Your answers helped clarify my thinking on this
database. I have already gone back and removed some fields from my table,
designed a few "test" queries to ensure I get the results I want, and
reviewed my new knowledge with my Team Lead. She concurred with everything
you said about the redundancy. Your answers have increased my knowledge of
Access and databases, and I can move this project further along now. THANK
YOU very much!!!!