Combo Box dilema

[Patti]

I have a combo box on a form that lets the user select a
company name from a table: "tblCompany" whose fields are
CompanyID (PK) and CompanyName.

I have the combo box properties set to the following:
row source type: table/query
row source: tblCompany
column Count: 1
Column Head: No
column width: 0.5"
bound column: 2

The dropdown menu only shows and only allows me to enter
the ID number even though I've selected the bound column
as "2" which, I'm assuming, is the 2nd column ...
CompanyName. However, when I go back to the actual
Master table that I'm entering data into, the company
name is listed properly. I've tried setting column count
to "2" so I can see both columns, but it still will enter
the ID number instead of the name. What am I doing wrong?

tia,
Patti

 

[Allen Browne]

You cannot nominate the bound column as number two when there is only one
column in the combo (Column Count is 1).

In most scenarios, you would want the CompanyID to be the bound column,
because that is the field that uniquely identifies the company, even if 2
companies did have the same name.

You could set the Column Width to zero if you want to see the company name
and not the CompanyID.

 

[Patti]

Thanks, Allen. That makes sense. I made the necessary
changes, and it seemed to work OK. However, I've also
added a "NotInList" event so that if the user mispells a
name or enters one thats not in the table, they have the
option to add it or get the pulldown menu, but then I get
a runtime error '3162':

"You tried to assign the null value to a variable that is
not a variant datatype."

Once I've cleared the runtime message box, the pulldown
menu appears and I can continue on.

Any ideas on what I'm doing wrong??

 

[Allen Browne]

NotInList isn't useful where the bound column is hidden, because you can't
add the text being typed (company name) to the AutoNumber column.

Instead, use the DblClick event of the combo to open the form where users
can enter the company info. Then in the AfterUpdate event of this company
form, requery the combo:
Forms![YourOtherFormNameHere].[YourComboNameHere].Requery

 

[Allen Browne]

You could use error handling in the procedure to just ignore that error.

If you are not sure how to set up error handling, see:
http://members.iinet.net.au/~allenbrowne/ser-23a.html

 

[Patti]

Thanks, Allen. I forgot about error handling code. I
took a VB course about a year-and-a half ago but don't
use it a lot. I'll give that a try. THANKS!

patti