Handling null entry in combo box

[sly like Coyote]

I have an error I haven't been able to handle. I have a many-to-many relationship between two tables, one for clients and one for vouchers, broken down using a junction table. I have a mainform based on the vendor record and a subform with a combo box to select a client (or clients) to associate with the voucher and populate the junction table.

The problem I have is that my users sometimes notice their intended client isn't in the system from the dropdown options and instead of hitting 'enter' or something will try to delete their entry and click away to the 'create client' command button that launches the form needed to enter a new client record. This prompts an error for trying to select a null value.

I've handled the NotInList event when they just try to enter a client that doesn't exist, but I can't figure out how to catch this null value entry error - the only thing they can do is select another client, leave the subform, and then delete the 'wrong' selection. I've thought about just getting rid of the command button (the NotInList can launch it automatically anyway) but that doesn't fix the actual error and would be unintuitive in many cases.

 

[Jeanette Cunningham]

If you have error handling for the NotInList code, set it so that the error number you get for trying to select a null value is ignored, or just use Resume Next for error handling for that sub.

 

[Steve R.]

Have you tried using the NZ function? Below is a snippet from Access Help.

You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

With the NZ function you could use it to set the value of your combobox to zero, which should be an "impossible" value. Before the second form is allowed to open, you would test for a value of zero. If the value of combox is zero the you would then prevent the form from opening.

 

[sly like Coyote]

Well the problem is really user behavior. The only way for there to BE a null selected is for the user to enter part of a selection, see it's not in the list, and then manually delete what they've entered and try to leave the field.

If they don't do that and just do their search, NotInList moves them automatically to create a new client. I'd just like to do the same thing if they decide to delete their intended entry manually once they realize it's not in the list, leaving the field null.

 

[Steve R.]

See if this helps, or at least points you in the right direction:Not In List With a Pop-Up Form and SubForm. In my case, the pop-up form has a cancel button, so if the user does not wish to add a new entry the operation can be cancelled.

 

[Jeanette Cunningham]

Use an error handler to trap that error, or instruct the users to press Esc to when they want to delete a wrong selection from the list or let the users put up with the error message.

Do you have any code associated with this? If yes, post your code so we can show how to use an error handler to suppress the error message.

 

[sly like Coyote]

Well, I have general NotInList handling (which is essentially just suppressing the Access error, giving one of my own, and doing a .Undo on the field and then setting the focus to a command button) but nothing for the Null event. I've tried to catch it in that event with an IsNull test, but the IsNull doesn't seem to ever return true. Right now I don't have any code for it.

 

[Jeanette Cunningham]

From your description it sounds as if you could use the after update event for the combo - I assume you have code for this event?
Check for null values like this:
If Not IsNull(Me.[ComboName]) Then
'code to do something here
Else
'trap the null value here and do nothing
End If

Personally I gave up using the Not In List event because of the errors and I always seen to have more than one field needed in the new record, so I just write code to open a form to add a new record to the combo. This code is launched either by double clicking the combo or by clicking a small button next to the combo.

If the code above doesn't help, you will need to look further at trying to trap the null error in your Not In List code.

 

[sly like Coyote]

Thanks for your help Jeanette. My NotInList even works much like yours - it just clears out the field and directs the user to a command button that will launch the creation form.

Trapping the null is the only thing getting me; I've tried the IsNull() test in the NotInList where it doesn't return true, for some reason, in the AfterUpdate which never fires because the error generates first. It's helped quite a bit to instruct my users not to delete the entry manually, though.

 

[Jeanette Cunningham]

Perhaps you can trap that error in the form's error handler.
Create an event for the form's error event and put
Debug.Print Err.Number, Response

Put a break point on this sub and run the form to see if it gives an error here.

 

[VilaRestal]

Well the problem is really user behavior. The only way for there to BE a null selected is for the user to enter part of a selection, see it's not in the list, and then manually delete what they've entered and try to leave the field.

If they don't do that and just do their search, NotInList moves them automatically to create a new client. I'd just like to do the same thing if they decide to delete their intended entry manually once they realize it's not in the list, leaving the field null.

I think the problem might be if the user does this then the Value isn't null, it's what it used to be (I think, it doesn't get a new Value until the not-in-list and validation have been passed) and the Text isn't null it's a zero-length string

So, I think the test would be:

If Nz(Me.[ComboName].Text,"") = "" Then

Perhaps best in the BeforeUpdate event:

If Nz(Me.[ComboName].Text,"") = "" Then
Cancel = True
'Any other code (prompt for new record perhaps)
End If