Hi
I have a form where I need to validate a field based on the response of another field.
From reading all the posts it looks likethis needs to be done on the Before Update action of the Form.
is this right?

Can anyone help me with this, I am new to VB and can use as much direction as possible on this one!!

Do I need to use Cases?

The first field is a combo box [AgDirectCB] with 2 choices
Agency
Direct

The 2nd Field is a combo box [AgencyCB] with a list of Agencies

My logic needs to be as follows

AgDirectCB can not be null
If AgDirectCB = Agency
Then AgencyCB must not be null

If AgDirectCB = Direct
Then AgencyCB must be null

If I were doing it, I would keep the control disabled until the first control has been properly selected. Then for complete validation I would use the BeforeUpdate event of *both* the control and the form.

Since I was PM'd I'll jump in. Disabling the second control isn't a bad idea. I would make the first field required in the table, so Access would handle the validation for that. Then code in the before update event of the form might look like:



Hey RG, hope you're seeing that snow that moved through here yesterday!

Indeed Paul. We've got 3-4 inches sitting on the ground right now.

We just got dusted here, though they got a few inches up at Lake Tahoe. I guess it's almost time to hang up the golf clubs and take down the skis!

I got the tractor ready for pushing snow a couple of week ago. I'm ready, let 'er rip.

enough about the snow,,, what about my needs!!!

Actually can't wait to hit Tahoe, Mammoth opened last week.

This worked great,
only problem is,,
If i select Direct and select an agency, then when i close the form using the X at the top right,, i get my nice warning message and then access message saying it will not save the record,,,
How can i get rid of that?

The other issue is if i select the Close button that I added, I get the warning with an OK button but the form closes anyway and does not save,, doesn't allow the user to make the change

I tried adding the same code in the close button on click event and also added a save before the close, but now i get the warning twice and still the form closes

Can you get your minds off the slopes and back on me for just a minute please!!!

Private Sub Close_Click()
On Error GoTo Err_Close_Click

If Me.AgDirectCB = "Agency" Then
If IsNull(Me.AgencyCB) Then
MsgBox "Please Select an Agency"
Cancel = True
End If

Else
If Not IsNull(AgencyCB) Then
MsgBox "You have selected Direct, Either change to Agency or remove the Agency"
Cancel = True
End If
End If
DoCmd.save
DoCmd.close
DoCmd.RunCommand (acCmdRefresh)


Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

For the record, if the form is "Dirty" the FORM BeforeUpdate event *will* run regardless of how the form is closed.

The validation code you have in the Close_Click() event should be in the Form_BeforeUpdate which, as Allan said, fires anytime the form goes from Dirty to Not Dirty, i.e. is saved, regardless of how the record is saved or hoe the form is closed.

Also note that the line you have

DoCmd.Save


does not save a record, it saves design changes to the form! The code to save a record would be

DoCmd.RunCommand acCmdSaveRecord

but in point of fact should not be in the code at all. This will be handled automatically by Access unless you prevent it, as the first part of your validation code should.

As a last note,

Cancel = True

can only be used in events where it appears in the sub header in the parens following the sub name, such as

Form_BeforeUpdate(Cancel As Integer)

and no, you cannot simply add it to the header. If Access doesn't put it there it's not available for that event.