Combo Box problem 2

[muriel]

I'm trying to create a combo box on a subform. I have a many to many relationship between two tables. I'm creating the main form with one of the tables, and the subform with the join table. Now when I'm creating the combo box, I need to have the ID stored in the join table. Problem is, if I use the control source as an ID number to store the number, the combo box shows numbers, instead of text, and I get an error message that says the field values don't match. I need to figure out to store the ID number, but show users the actual text so they can select it.

Any ideas how to resolve this? I've really been trying to figure this out - going through my Access reference books, etc. Now I'm stuck.

Thanks.

 

[HomeALone]

OK, I assume you use a query to build your combo list, which contains (column1)=ID number (column2)=UserName
Here is the trick to get you out of the mud.
Click open the combo Properties
On the Format tab, make sure ColumnCount = 2, ColumnWidth = 0";1" (the 0” is to hide the ID number), ListWidth = 1" (this should be the total length of ColumnWidth combined)
Here is the important part
On the Data tab, make sure BoundColumn = 1 (this means use the ID number as the selected data for entry)
Tell me if you could get out of the mud?

 

[BillPower]

Hi HomeALone,

Very well explained, a well deserved star for you.

Keep up the good work.

Bill

 

[muriel]

HomeAlone,

Thanks - that helps a lot!!! Another star for you.

Followup: I need to set the Limit to List property to No, because users will need to type in items that are not in the combo box list. When I set it to No, I get the error message "The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column. Adjust the ColumnWidths property first, and then set the Limit to List property."

Any suggestions?

 

[Emmali]

I had list boxes, which I've converted to limited combo boxes. I had changed the order of my table columns (thing; thingID) so it would show up properly, e.g. a list of things, not the IDs. Now I've changed it to your format, which I now recognize as far more sensible than my cobbled fix, and my listed options have disappeared!

Of course, I could return to that previous state where things mostly worked....

Emm

Center for World Indigenous Studies

http://www.cwis.org

"Always carry a tuna sandwich in case of tigers."

 

[Eupher]

Muriel, that is correct. Access will not let you set the Limit to List property to "No" if the bound column is not the same as the first visible column. You can either change bound column so it's the same as the first visible column (which it sounds like you don't want to do) or you can create code for the Not In List event to handle when users enter data that's not in the list.

See thread702-568507, thread702-585023, thread702-565103 for more info on adding items to a combo box using the Not In List event.

Ken S.

 

[muriel]

Ken S. - thanks for the response. That's what I thought. I'll check out the threads you posted.