Combo Box Sources

[Guest]

Hi there,

I'm having trouble with two combo boxes on a form. There is a different
problem with each one.

First Problem

The first combo box was originally a text field and I changed it to a combo
box. The source table for the combo box is "User" and it has three fields.
They are UserId, User, and UserInactive.

I set up the row source as:
SELECT User.UserId, User.User, User.UserInactive FROM User ORDER BY
User.User;

I have 1 bound column, column count 3 and column widths set to 0"'1";0" and
it does not work. Data which already existed in records doesn't show up and
the drop down box is empty, but the old data does show up if I set the row
source to this:
SELECT User.User, User.User, User.User FROM User ORDER BY User.User;

Obviously the row source version which works is wrong. It should work the
right way. Any ideas why not? Thanks.

Second Problem:

I have a combo box which originally had a record source of a table called
"Status". I changed the record source to a table called tblTaskStatus. The
tblTaskStatus has three fields. They are TaskStatusId, TaskStatus, and
ShowTaskStatus. I set the row source to:

SELECT tblTaskStatus.TaskStatusId, tblTaskStatus.TaskStatus,
tblTaskStatus.ShowTaskStatus FROM tblTaskStatus ORDER BY
tblTaskStatus.TaskStatus;

Just like my first combo box problem, this does not work. The combo box is
empty and data that had already been entered in pre-existing records did not
show up. Again I had 1 bound column, column count 3, and column widths set
to 0";1";0. But when I change the row source to:

SELECT tblTaskStatus.TaskStatus, tblTaskStatus.TaskStatus,
tblTaskStatus.ShowStatus FROM tblTaskStatus ORDER BY tblTaskStatus.TaskStatus;

It works. Any idea why this is happening? Thanks for your help.

Janet

 

[MacDermott]

When you set BoundColumn to 1, you are saying that the data in the
underlying table (the table behind the form, not the RowSource for the
combobox) should match the data in the first column of your combobox's
RowSource. Since you now have UserID in the first column, the values you
had stored for User won't match, so they won't display in the combobox.

You might want to try changing your BoundColumn to 2.

 

[Graham R Seach]

Janet,

I would think that Problem 1 might be due to the fact that User is an Access
reserved keyword. Try the following:
SELECT [UserId], [User], UserInactive FROM [User] ORDER BY [User]

As for Problem 2; I have no idea. The SQL statement is OK.

Perhaps the form is corrupt. Try creating a new form; add two combos to it;
set them up just like the two problemmatic combos, and see what happens. If
it works OK on this test form, then the original is most likely corrupt, in
which case, you know what you have to do.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Guest]

Thanks so much for your help. That solved both problems!

Janet