Record-Level Validation

[whdyck]

Can anyone offer guidance on how to do record-level validation of data input?

For example, how can I validate one record field based on another field or fields on the same record?

Currently, I'm using field validation at the table level, but I'd like to validate in the form in the above manner. I suppose I'd do so in the BeforeUpdate event.

Thanks for any help you can give.

Wayne

 

[pbaldy]

I agree about the event. Here are the basics:

http://www.baldyweb.com/BeforeUpdate.htm

 

[whdyck]

I agree about the event. Here are the basics:

Thanks. That seems to do the trick. However, I also get other message boxes appearing in addition to the prompt to fill out the control.

For example, after the msgbox I want, I see the following:
The DoMenuItem action was canceled.

When I saw this error, I tried wrapping the entire If statement in "echo false" and "echo true" statements. I got rid of the above msgbox, but another one appeared (on a new record):
No current record.

This is probably a dumb question, but I'm new to Access. How can I get rid of the other msgbox?

Thanks.

Wayne

 

[pbaldy]

What is your code?

 

[whdyck]

What is your code?

Sorry. Here it is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Echo False
 
    If Len(Me.txtClaimNumber & vbNullString) = 0 Then
        MsgBox "Please enter the Claim Number."
        Cancel = True
        Me.txtClaimNumber.SetFocus
    End If
 
    Echo True
 
End Sub

 

[pbaldy]

I don't see anything there that should cause the message. Is there code explicitly saving the data? You may need to trap for this error there. Basics of error trapping if you need them:

http://www.baldyweb.com/ErrorTrap.htm

 

[whdyck]

I don't see anything there that should cause the message. Is there code explicitly saving the data? You may need to trap for this error there. Basics of error trapping if you need them:

I was finally able to get this to work. Not exactly sure what's different now. But maybe there was a conflict with some table level validation I had mistakenly left in place.

Just a couple more questions, if you don't mind:

1. You use the following code to test for a blank string:

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

Can you please explain why you add the "&vbNullString"? I notice that it *is* necessary (doesn't work without it), but I'm not sure why.

2. Is it good practice to throw multiple messages to the user (when there are multiple problems in the data on a given form) or to present a single message detailing all the problems?

Thanks again for your help.

Wayne

 

[pbaldy]

1) That will test for both Null and a Zero Length String, which can appear to be Null but isn't. Here's a bit of a discussion on the difference:

http://www.baldyweb.com/NullEmptyEtc.htm

2) Probably a style thing. I wouldn't throw multiple message boxes, but I might give them one that summarized the missing info, like:

The following still need to be filled out:

Name
Phone number
Whatever

I often use Conditional Formatting to highlight controls that need to be filled out, and my warning message will simply direct them to the first one that needs info. When they've filled out a control, it is no longer highlighted.

 

[whdyck]

I often use Conditional Formatting to highlight controls that need to be filled out, and my warning message will simply direct them to the first one that needs info. When they've filled out a control, it is no longer highlighted.

Conditional formatting is a great idea. (Sure beats my asterisk on the labels beside the required fields.)

But the Conditional Formatting dialog doesn't offer any condition related to nulls (and for text fields, testing for "" doesn't seem to work). How do you do it? (I have Access 2003.)

Wayne

 

[pbaldy]

You use Expression Is and this type of thing:

IsNull([txtPassCell])

txtPassCell in my case is the name of the textbox.