Form Validation of one field based on value of another 1

[dcrake]

How do I make access validate the value of one form field based on the presence of data entered into another form field.

Specificaly, I have data entry form (called CORR-001) which contains 8 fields (A, A1, A2, A3, B, B1, B2,B3).
When field B has a value (not null), I want to require that fields B1, B2, and B3 also have vales (not null). When field B is null, no values need to be entered into B1, B2, or B3.

 

[PCLewis]

My vote is on the Before_Update event for the form. If you check your fields at this point and use the Cancel=True in that event to prevent the record form being saved, you can ensure that your data entry rules are obeyed. Of course, you'll have to identify the probelm in your code and tell the user:

If not IsNull([B]) then
  if IsNull([B1]) then
    Cancel=True
    msgbox "must enter a value for B1"
    Exit Sub
  end if
  
  if IsNull([B2]) then
    Cancel=True
    msgbox "must enter a value for B2"
    Exit Sub
  end if
end if

there will be some repitition here that you might like to get rid of:

dim ctrl as control 
...

If not IsNull([B]) then
  for each ctrl in me.controls
    if lcase(left(ctrl.name),1)="b" then
      Cancel=True
      msgbox "must enter a value for " & ctrl.name
      Exit Sub
    end if
  next
end if

That code relies on your field names to have something consistent. I'd recommend something like "txtB_B1, txtB_B2,.." or something. I hope you get the idea.

cheers

 

[grantwilliams]

We've used something similar to this in the past using the Before Update property

Private Sub Form_BeforeUpdate()

    Dim ChkFields As Boolean

    ChkFields = False

    If Not IsNull(Me!B) Then

        If IsNull(Me!B1) Then ChkFields = True
        If IsNull(Me!B2) Then ChkFields = True
        If IsNull(Me!B3) Then ChkFields = True

    End If

    If ChkFields = True Then 
        MsgBox "Not all fields have been filled out."
        Exit Sub
    End If

End Sub

Not sure if I've coded this right, but it gives you an idea of what I'm getting at.

Hope this helps!

Grant

 

[grantwilliams]

OOPS!

I left it idle a bit long and PCLewis beat me to it!

Grant

 

[dcrake]

I ended up making B a check box, so I set the If to be

If B = -1 then
if IsNull([B1]) then
Cancel=True
msgbox "must enter a value for B1"
Exit Sub
end if

if IsNull([B2]) then
Cancel=True
msgbox "must enter a value for B2"
Exit Sub
end if
end if

and it almost works perfectly.

The problem I am now encountering is that if I uncheck the B checkbox, go to another record, return to teh first record and then recheck the B checkbox, it does not seem to revalidate. Where else do I have to put this code to solve for that situation?

 

[PCLewis]

Where did you place the code?

If it's in Before_Update and if B is not a field in the table, then your event won't be called. Why? Because no data was being updated.

You need to decide what you want and then implement it. If you want the fields validated when a change to the record is made, then Before_Update. If you want the fields checked every time you scroll to a new record, then go for On_Current (although the cancel=true cannot be used there).