building required field logic in a form and corresponding error messages

[tfurubay]

Hi,

I am building a form and i would like it to have conditional error messages.

For instance, I am creating a Date field. If the date field is populated, I also need Field 2 populated and an error message created. However, if the date field is not populated, then Field 2 need not be populated.

is this possible?

[ChipR]

You could use a form with unbound controls and a Save or Submit button. On click, you can execute whatever validation code you want, then if everything is valid, insert a record. That's my preference for forms with lots of validation, anyway. Let me know if you decide to go this route and have problems.

[tfurubay]

Hi Chip,

thanks for responding! Here's where i have problems. i'm not sure where to put the logic. the logic i'm hoping to build is something like this:

Expr1: IIf([ENROLLMENT DATE] Is Not Null And [count] Is Null, {REJECT RECORD AND DISPLAY ERROR MESSAGE COUNT REQUIRED},{ACCEPT THE RECORD})

I'm hoping to design the form so that the capitalized and encased in {} is the outcome. does this make sense?

[ChipR]

A quick and easy way is some code in the Form's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
    If (Not ( IsNull ( [Enrollment Date] ) ) ) And ( IsNull(count) ) Then
        Cancel = True
        MsgBox "Count is required if an Enrollment Date is entered."
    End If
ExitCode:
    Exit Sub
ErrorHandler:
    MsgBox Err.number & " - " & Err.Description
Resume ExitCode
End Sub

[tfurubay]

Great! what if i have multiple things that i need to check. do i need to fit the logic within one statement or can i create multiple statements. if i can create multiple statements, then will each error message come up seperately?

finally, what's the function of the ErrorHandler? is that for me to create my own error message? confused then about the msgbox "count is required". are those redundant? thanks so much!

[ChipR]

You can check multiple things with multiple statements, and give an appropriate MsgBox for each. They will all be checked, and then at the end if Cancel was set to True by any of them, the update will cancel, and the record won't be saved.

The error handling code will only be called in case something in your code creates a run-time error, which would normally crash the program. Error handling allows you to display a nice message box and not crash, in case you caused a problem that wasn't caught when you compiled, but it has nothing to do with your validation. Sorry to confuse you with that. It's not really necessary for development, since you can debug, but if you are planning to release your application for others to use, it should be in every piece of code.

[tfurubay]

thanks Chip, i'll give it a shot! your assistance is very much appreciated. thank you so much!

i'll let you know how it goes. Todd

[tfurubay]

regarding form validation, will a seperate message box come up with each error type? thanks!

[ChipR]

Just to be clear, let's not describe them as errors, because there are predefined errors that already exist and have another purpose. For each validation check you want to do, use the format:

  If (logic for checking that your rule was broken) Then
     MsgBox "your message to the user"
     Cancel = True
  End If

And you can do a bunch of those one after another.

[tfurubay]

brilliant! thank you again.

Todd

[ChipR]

No problem, Todd. Good luck.

[NeoPa]

It's not for everyone, or even every situation (There is only one message for instance), but Access tables include Validation Rule & Validation Text properties. These can be used at a table level (which means they are not reliant on the operator using the intended form) and can specify how the data in the table can fit together.

To be honest, I doubt this will help you with your current problem Todd, but it may help some of those who are looking for similar answers and find this thread.