deleting data in a field doesn't seem to clear the field

[bkberg05]

Hi - I have a field on a form that is required. It's in a combo box. The combo box is set to Limit to List. Sometimes a user comes into the field and starts typing into the field to locate their record. But the record doesn't exist. So they backspace over the data they entered and try to leave the field (to go hit a button to add the record they need) and they get a message saying the field is required and can't be null. However, if they return to the field and then hit the escape key, they can exit the field without the error message. Backspacing over the data somehow doesn't leave the field blank, but hitting escape does. It's confusing for the user. Is there any way around this (allowing them to backspace and then not get this error)?

Thanks for your help

[bkberg05]

At someone's suggesting on another question, I've tried the following, but am still getting the same validation error.

If Vendor_ID.Dirty = True Then
If Vendor_ID.Value = "" Then
Vendor_ID.Undo
End If
End If

[ChipR]

Just check for cboBox.ListIndex >= 0 to make sure they have selected a value and not blank.

[kstevens]

I am currently using this to do the exact same thing in the before update event

If IsNull([WorkOrderNumber]) Then
    Me.Undo
    Exit Sub
End If
 

And this if they continue (enter the entire number without backspacing out), and the record already exists

Dim TempWork As Integer
If Nz(DCount("*", "tblOpenOrders", "WorkOrderNumber =" & Me.WorkOrderNumber), 0) > 0 Then
    TempWork = Me.WorkOrderNumber
    Me.Undo
    DoCmd.ApplyFilter , "WorkOrderNumber =" & TempWork
End If
End Sub

Otherwise they are creating a new record.

[bkberg05]

Hi KStevens. Makes good sense on the BeforeUpdate.

The me.undo resets whole form though (takes it back to a new record and any data entered previously is lost). I tried fieldname.undo and then I still got the same error. Does it work for you if you specify the fieldname rather than using 'me'?

[bkberg05]

ChipR - thanks for the suggestion, but what do I do after I check this? The checking for null or "" seems to work for, but I can't seem to get the field back to an 'undirty' state so that the validation rule doesn't kick in.

[ChipR]

Is your combo box bound to a field in your table? I don't get this problem with an unbound combo box.

[bkberg05]

yes, it is bound. is that the wrong way to do it?

[ChipR]

Not necessarily, but I have found it easier to work with unbound combo boxes. It's a little more work, but you get an extra step of code and/or formatting between the combo box and the table. So in the AfterUpdate of my combo box, I would handle the validity check, and if the value was good, I would copy it to a hidden bound text box on the form.

[missinglinq]

IF it was me, I'd go back into the table and strip off the "required" property and then use Validation at the form level to insure a it has a value, using the Form_BeforeUpdate event. Keeps the field as "required" and eliminates the problem, I would think.

Linq ;0)>

[bkberg05]

Thanks linq and chipr. Don't know which method I'll choose (diplomatic), but I'm sure either one will solve me problem easily enough.