Combo Box Data Validation, If then criteria, handle null and empty string

[Kryst51]

The following code is supposed to validate some information. If a certain combo is null or empty string then a message box should pop up. I added the red portion to the original code, as the message box would pop up if I deleted info from the box, but not if I had never selected an item in the list in the first place. Currently it just updates and goes to a new record when this is the case.

I am not sure that I am using the null correctly, or, maybe that isn't my problem at all. Any help with this problem is greatly appreciated.

Also, I don't know if you need to know this, but cmbWarehouse is storing a "number" type data into a junction table. but the display hides the number ID and only displays the text I want to show. (typical combo box use). Also I am using Access 2003

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim part1 As String
Dim part2 As String
 
part1 = "As this Nonconformance is a warehouse error "
part2 = "You must specify the warehouse for reporting purposes"
 
If Me.cmbErrorCode.Column(3) = "W" Then

  If Len([COLOR=red]Nz([/COLOR]Me.cmbWarehouse[COLOR=red], "")[/COLOR] & vbNullString) = 0 Then
    MsgBox (part1 & part2)
    Cancel = True
    Me.cmbWarehouse.SetFocus
  End If

End If
End Sub

I was trying to make the form not scroll through previous records and had set the data entry to "yes", but then this disable going to a new record and also seemed not to save the current. So I had added a button to save the record, but then my validation didn't work. So I deleted the button, the code behind the button, and set the data entry back to "no". I don't know if this could have caused problems.

 

[pbaldy]

Offhand that looks okay. I use the same test without the red part all the time:

If Len(Me.cmbWarehouse & vbNullString) = 0 Then

Are you sure the first test is being met? Have you set a breakpoint and examined all the values?

 

[Kryst51]

I am sure that the first test is being met. I know how to set breakpoints. Ran my code, it seems to be doing what I want. Test wise, just not popping up the message box when there is no value in cmbWarehouse

 

[Kryst51]

OK, Not only did I check the values, but had my co-worker, who is more experienced then I am look at it, and he says it should work too.

So in the hopes of getting some insight into what is going on, I am posting my db.

Paul, thanks for the suggestions. Actually your code is what got me started on this track, I found it in an old post when I google searched, then modified from there to add the message box parts. At least I'm learning, eh?

Edit: Added back in the null check which I had deleted, to see if it was wrong.

 

[pbaldy]

The field has a default value of 0 in the table. That appears to follow through so that even if the combo is left empty, when I set a breakpoint it has a value of 0 in your code. Taking that default off at the table level appears to fix the problem.

 

[Kryst51]

The field has a default value of 0 in the table. That appears to follow through so that even if the combo is left empty, when I set a breakpoint it has a value of 0 in your code. Taking that default off at the table level appears to fix the problem.

Wow, the level of my errors astonish me. It has nothing to do with the code I just learned, and everything to do with a simple default value. All of my errors lately have been little things like this....

Thank you for your help! It is certainly bittersweet, as I am extremely frustrated with myself. But your code works great.

 

[pbaldy]

No problemo; glad we got it sorted out. Sometimes it's the seemingly simple things that can frustrate the heck out of us.