Combo box on form only saves ID and not value

[Sashacat7]

I've created a combo box on a form that a user can select from a set of staff
names referenced in a table of staff names. However, when I select my staff
names from the combo box, what it stores in the table is just the ID, not the
staff name - so when I create a report all I have in my staff name field is
the ID, not the staff name.

I can't figure out what I'm doing wrong. Please help.

 

[Dale Fye]

Actually, it sounds like you are doing it exactly right.

1. Some inexperienced users might tell you to open the table in design
view, highlight your StaffID field, and then change the Display Control
property from Textbox to combo or list, and then change the RowSource to your
Staffs table, but they would be wrong. Of course, you can do this, and when
you open your table, you will see the staff names rather than the ID, but it
can be confusing when you think you are going to get an ID and actually see a
name.

2. The proper way to do this is to link you staff table to the other table
in your reports Record Source. Link the two tables on the StaffID field, and
then add the StaffName field to the query. Then change the control source of
the control where you want to see the name to the StaffName field.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Sashacat7]

Thanks, although can you give me some detail about how I complete #2?
Queries are an area where I'm very weak (plus I'm sketchy on terminology.)

what I just tried was going into the field on my report where Staffname
should show up, going into Properties>>Control Source>>Expression Builder

Then I added + table + staffname + staffname + <value>

Which didn't work (probably for obvious reasons to someone who actually
knows what they are doing.)

Thanks for helping

 

[Dale Fye]

Actually, you need to:
1. Open the report in design view.
2. Select the report (square in the upper left of the design)
3. In the reports properties, on the data tab, Record Source property,
click the "..." to open the query that the report is designed from. If you
are currently just using a table, it will ask you if you want to create a
query based on the table (say yes).
4. In the query grid, add the Staff table (that contains the StaffID,
StaffName, and other fields that pertain to the staff). and join the Staff
table to the other table on the StaffID field. Then save the query and close
it. You should now see the name of the saved query in the Record Source
property of the forms property sheet. If you don't, scroll through the
tables and queries until you find the one you just saved and select it.
5. Now, go to the Control Source on your report and select the StaffName
field from the list of fields that are available.

That should do it. When you run your report, instead of seeing the ID
number that is associated with the name, you should now see the name.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Sashacat7]

Ok, I got as far as this "and join the Staff
table to the other table on the StaffID field. "

Before I got lost again. I should have mentioned earlier I'm using Access
2007, in case that makes a difference.


Thanks!

 

[Dale Fye]

2007 doesn't matter.

Actually, reading back to your initial post, you mention [ID] and "staff
name field".

So, what was your original query/table for the report, there should be a
field that currently displays the [ID] with each staff member. Is that just
[ID] or is it [StaffID], or [StaffName], or ????

In the Staff table, what are your fields? See questions above. It may be
that you need to join the ID fields, not [StaffID].

On the other hand, if your problem is that you don't know how to join the
fields in the query grid, then Click and hold on the ID (StaffID) field in
one table, and drag from that table to the ID (StaffID) field in the other
table.

If neither of those solves the problem, then provide me with the names of
the tables and their fields and I'll try to explain better with your actual
table structure.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Sashacat7]

I have two tables "Stores" and "Staff Names"

All my forms and reports were originally built on the Stores table. I've
decided to try and ease some data entry tasks by adding a combo box that
references a new table called Staff Names. The Staff Names table has two
fields: ID and Staff Names.

Prior to the addition of the new combo box, we just typed in the staff name
as a data entry field for that record. The report currently returns the
value from either Staff 1 or Staff 2 in the Stores table.

 

[Dale Fye]

OK,

So you have fields [Staff 1] and [Staff 2] in the [Stores] table. In the
form for entering store info, you now have two combo boxes (I would name
these cbo_Staff_1 and cbo_Staff_2), which both reference the [Staff Names]
table as their Row Source.

Back to the Query grid.

You will need to have the [Stores] table, and two copies of the [Staff
Names] table (when you add the second staff names table, it will probably
read [Staff Names_1]) in your query grid. Click and hold on the ID field in
[Staff Names] and drag onto the [Staff 1] field in the [Stores] table. Then
click and hold on the ID field in the [Staff Names_1] table and drag that
connection to the [Staff 2] field in the [Stores] table.

Right click on each of the lines connecting [Stores] to the two staff names
tables and select the "Join Properties" option. Once that dialog has come
up, click on the radio button that says something like: "Include all records
from 'Stores' and only those records from 'Staff Names' where the joined
fields are equal. Then click OK and repeat for the link to the other staff
names table.

If there are not any fields listed in the grid at the bottom of the page,
double click on the * at the top of the [Stores] table, that tells Access to
include all the fields from the Stores table in the query. Next, double
click on the [Staff Names] field in the [Staff Names] table (you should now
see this in the grid). Repeat this for the other [Staff Names_1] table.

Run this query (click the excaimation point in the ribbon bar) to make sure
you have all the fields you need for your report (along with the two names).
Now save the query and go back to the design view of the report. Select the
[Staff 1] field, open the properties window, and then select the [Staff
Names] field as the control source for that field. Do the same for the
[Staff 2] field, but select the 2nd version of the [Staff Names].

Hopefully, you will now see the names of the staff, rather than their ID
values in the report.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Sashacat7]

I got this part "Next, double
click on the [Staff Names] field in the [Staff Names] table (you should now
see this in the grid). Repeat this for the other [Staff Names_1] table."

I have the stores fields listed down on the grid. I went back up to my staff
names tables and double clicked on the Staff Names fields and they showed up
in the next column of the grid

What I have is:

stores* staff names staff names
stores staff names staff names_1

When I attempt to run the query, it gives me an error message of "Type
Mismatch in Expression."

 

[Sashacat7]

According to Access, the problem is that I've got a mismatch in the field
types. The ID field and auto number and the staff name field is a text.

 

[John W. Vinson]

According to Access, the problem is that I've got a mismatch in the field
types. The ID field and auto number and the staff name field is a text.

Make sure that the Bound Column property of the combo box is the value which
you actually want to save. Normally this WOULD be a numeric ID, being saved
into a numeric foreign key field. If that's not what you want to do, change
it.