frustrated

Topic

New Reply

hello all
i have a combo box (referencing another table) in a table that shows an employee’s name and then his/her employee ID number. ex. Barnett, Tom 0001
i want to be able to pick by the name (type “b” and Barnett would highlight) but enter “0001” in the table. i should be able to have name as column 1 and id as column 2, and have column 2 set to be the bound column… this is not working. somebody please help!

thanks in advance

Reply | Quote

Replies

Is the id field the primary key for the employee table? You should be able to use the lookup wizard in the second table to create a link between the two tables with the primary key field hidden. Here’s how the properties should look for your lookup field in the lookup tab:

Display Control=Combo Box
Row Source Type=Table/Query
Row Source=Your SQL Statement
Bound Column=2 or whichever one is the ID
Column Count=2
Column Heads=No
Column Widths=1″;0″

Default the rest

Reply | Quote

try column 0 and column 1
instead of 1 and 2
HTH

Reply | Quote

Hi Jerry,
Although you would generally use Column(0) and Column(1) in VBA for a 2 column listbox, in the properties dialog in form design you would use 1 and 2 respectively to refer to them. You’ve gotta love that consistency!

Reply | Quote

i have employee name as column 1, id as number 2. if i put bound column to 0, all fields are blank. if i use column 1 as bound, it puts the name in the field (which is expected).
if i put column 2 as bound, it changes all the fields to have the name in the field – ARRRGGHH!! (it should insert the ID number) why isn’t it doing that????

thanks for your help

Reply | Quote

No, you bind column #1, which is the same as column(0). That’s what Rory meant. When you reference the columns collection, you start with column(0) but when you specify the column to bind, you start with the ordinal position of the column.

Your real problem is that you appear to be confusing what you see on the screen and what is actually stored in the field. If you want to store the value in column 1 but see the value in column 2, you bind column 1 and set its visible property to false. Then the value in the field will be the value in the first column, but the display will show the value in the second column, i.e., the first visible column in the combobox. If you bind column 2, then that is the value that will be inserted into the field, which doesn’t sound like what you want to do.

Reply | Quote

I think you’re trying to do something that comboboxes can’t do. A combobox displays the first visible column when you select a value. It doesn’t matter which field is bound, it’s the first visible column that gets displayed.

If you want to do something different, have the combobox list the names with the bound employee number hidden by setting it to zero width BUT have a textbox bound to the same field underneath the combobox. Make the combobox’s backstyle transparent. That way, you’ll see and select from the employee name list, but when you move off of the combobox, the underlying textbox will become visible and display the employee number.

Wait a minute. Are we talking about forms or tables? If you’re trying to do it in a table lookup, you’re out of luck. If you’re trying to do it on a form, use the technique I just described.

Reply | Quote

well, i was talking about tables… looks like i am out of luck.. thank you all for your help..

Reply | Quote

Don’t give up yet. I was able to create a field in a table related to an employee table which displays the name in datasheet view from the first column but stores the employee id in the table from the second column. What may be tripping you up is that even though the id is being stored in the table you still see the name displayed in datasheet view. You can test if the id is being stored by opening a new report in design view and base it on the table where the name is displayed but the id is stored. Add a textbox – not a combobox – to the report and set it’s control source property to the name field. Then preview the report and you should see the id displayed on the report and not the name.

Reply | Quote

thanks paul, but i think i will just worry about it when i design the form… i will have more control over it then…

Reply | Quote