Null value in combo box - 'Cannot assign a null value to a variable'

[hjgwright]

Hi.
I get the 'Cannot assign a null value to a variable' error when I enter a new name in my 'company' (company name) combo. I have some info on creating a null value in a combo, but I don't think it worked for me.

What I need to do is allow users to enter an unknown company in the combo, which triggers the NotInList event (which I've done - this is set to open up the Company Form). This works as long as the user leaves their 'unknown' company in the field. If they delete it (and render it 'Null') I just get an error (at the top) and it won't open up the Company form etc.

Anyone know how to get around this please?

Thanks,
HW

 

[Alexandre]

Test for the value entered from the Before update event of your control. If it is null/zero-length string, then you would cancel the entry. Then you would have to decide if you want to display a message explaining that the value should be entered; or do nothing more (my choice since a user that deletes a value may just have decided not enter any at the moment), or open your form and 'force' him/her to enter a value...

If Len(Nz(Me.Controls("YourControlName"), "")) = 0 Then
DoCmd.OpenForm "YourForm" 'Or message or nothing
DoCmd.CancelEvent 'Cancel the update
'wipe out the null/zero-length string
Me.Controls("YourControlName").Undo
'If you chose to have the user enter a value, then you need to update the listbox
Me.Controls("YourControlName").Requery

Hope this helps