Form Data Validation Driving me Nuts!

[Montez659]

I cannot for the life of me figure out how the data validation property on a combobox control on a form works! Maybe it is the fact that it is a subform in datasheet view - who knows - but everytime that I try something for the validation rule, nothing happens. What I want is supposed to be simple:

 Is Not Null

I have tried this code too many different ways to document here, but someone please explain to me what the correct expression should be.

The field should not be blank and the user has a value list to choose from - Primary or Assistant. Limited to list and no edits to list. But when the form pops up (which actually contains the subform for a Junction table) they can choose a name from the first field and still leave the repRole field blank with no warning message.

Thanks for the help in advance!

 

[boblarson]

I would do your checks in one place - the Form's Before Update event. Then you can use code like:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
 
If Len(Me.ComboNameHere & vbNullString) = 0 Then
   strMsg = "Field Name Here" & vbCrLf
End If
 
If Len(Me.SomeOtherControlNameHere & vbNullString) = 0 Then
   strMsg = strMsg & "Other Field Name Here" & vbCrLf
End If
 
If strMsg <> vbNullString Then
   MsgBox "You are missing data for these fields: " & vbCrLf & strMsg
   Cancel = True
End If
 
End Sub

And where it says "Field Name Here" and "Other Field Name Here" that is where you can put a friendly name which the user will recognize instead of the control or field name.

 

[Montez659]

How does that work in a subform in datasheet view? Do I need to loop through something to check each field Before Update or does the code already take care of that?

BTW, Bob, you have been a tremendous help to me today (and many times in the past). I just want to say that I appreciate the time that you put into sharing your wisdom here.

 

[Montez659]

Ok, nm that. I got it to work. Is Access form data validation (via the properties box) just a pain in the rear, or do I just need to read up on it some more?

 

[boblarson]

It would go in the subform's Before Update event and it would run if anything in the subform is trying to save. And as far as going through each control - you would need to have any of the controls in this code if you want to validate the entry. You can use validation at table level but that, and the control's validation rules are not all that flexible like this is. In the example I gave you, it would give the user ONE message which includes ALL of the places they messed up. Users like not having to get an error, and another, and another, and another. They like to know ONE TIME if they messed up and how to fix it.

 

[jasmith4]

There's also the Required property: if it's Yes, nulls are not allowed.

 

[Montez659]

Yeah, I looked into that. However, the problem with that is a generic error message. I needed something to be specific. The code above that Bob posted took care of that specific message that I needed.

I appreciate your input. BTW, where exactly is that Required property? Is it at the table level or should it be a property of the control on the form itself? I found myself looking for it but couldn't find it.

 

[boblarson]

where exactly is that Required property?

It is a property of the field at the table level. You can put in your own message in the Validation Text property which will then be shown instead of the Access one, but again it is not as flexible as it is using the other method.