Form-level validation (XP sp1)

Topic

New Reply

I need to apply some validation but really don’t know where to start.

I have a continuous subform, with each record comprising some text (txtType) and two check boxes (chkPresent and chkExtent). The check boxes are used as follows:

The first (chkPresent ) indicates the observed occurance for the object described in the text field. The second (chkExtent) indicates the objects extent.

For each record on the main form, there will be say, 10 entries on the subform (created by an append query).
Any, all or none of the records could have chkPresent set to True (by default they will be False)

This is the validation I need.
1) chkExtent can only be set to true if chkPresent is True (maybe it should be until this condition is met)
2) A MAXIMUM of 3 records could have chkExtent set to True

To keeps things tidy I suppose if an instance of chkPresent gets unchecked, then chkExtent should be set to false and disabled

The idea is here, just not the know-how

Reply | Quote

Replies

You can add code to the After Update event of chkPresent to set chkExtent to False if chkPresent is set to False:

Private Sub chkPresent_AfterUpdate()
If Me.chkPresent = False Then
Me.chkExtent = False
End If
End Sub

and add code to the Before Update event of chkExtent to prevent it being set to True if chkPresent is False, or if the limit has been reached. To make it work, you need to put a text box in the header or footer of the subform (it can be hidden, but on the other hand, it might give the user useful feedback) that counts the number of records with chkExtent set to True. Name this textbox txtCountExtent and set its Control Source to

=Abs(Sum([chkExtent])

The code is:

Private Sub chkExtent_BeforeUpdate(Cancel As Integer)
If Me.chkExtent = True and Me.chkPresent = False Then
Cancel = True
ElseIf Me.txtCountExtent > 2 Then
MsgBox “You can’t have more than three records with extent.”, vbInformation
Cancel = True
End If
End Sub

To disable chkExtent is problematic in a continuous form, for you would disable it for all records. There is no way to disable a control for one record only in a continuous subform.

Reply | Quote

I’m clearly not having a good day.

The text box won’t display a value, saying #Error instead, where I am going wrong

Reply | Quote

In the first place, I have to apologize, I forgot the second closing parenthesis.
In the second place, you must use the name of the Yes/No field in the table, not the name of the control on the form (if they are different). So if chkExtent is bound to a Yes/No field named Extent, use

=Abs(Sum([Extent]))

instead of

=Abs(Sum([chkExtent)

Reply | Quote

Thanks Hans,

I tried that very late in the day and didn’t have chance to post back until now.

I managed a bit of minor modification. It needed to ‘check’ whether the check box was about to be changed to true to run the comparison with the value in the text box, otherwise it was cancelling the event when I was trying to uncheck a box.

If Me.chkExtent = True And Me.chkPresent = False Then
Cancel = True
ElseIf Me.chkExtent = True And Me.txtCountExtent >2 Then
MsgBox “You can’t have more than three records with extent.”, vbInformation
Cancel = True
End If

I also found a requery was necessary to keep the value in the text field up to date, otherwise I was able to check 4 of the check boxes.

Its all working perfectly now, thanks for all your help

Reply | Quote

Thanks Hans,

I tried that very late in the day and didn’t have chance to post back until now.

I managed a bit of minor modification. It needed to ‘check’ whether the check box was about to be changed to true to run the comparison with the value in the text box, otherwise it was cancelling the event when I was trying to uncheck a box.

If Me.chkExtent = True And Me.chkPresent = False Then
Cancel = True
ElseIf Me.chkExtent = True And Me.txtCountExtent >2 Then
MsgBox “You can’t have more than three records with extent.”, vbInformation
Cancel = True
End If

I also found a requery was necessary to keep the value in the text field up to date, otherwise I was able to check 4 of the check boxes.

Its all working perfectly now, thanks for all your help

Reply | Quote

In the first place, I have to apologize, I forgot the second closing parenthesis.
In the second place, you must use the name of the Yes/No field in the table, not the name of the control on the form (if they are different). So if chkExtent is bound to a Yes/No field named Extent, use

=Abs(Sum([Extent]))

instead of

=Abs(Sum([chkExtent)

Reply | Quote

I’m clearly not having a good day.

The text box won’t display a value, saying #Error instead, where I am going wrong

Reply | Quote

You can add code to the After Update event of chkPresent to set chkExtent to False if chkPresent is set to False:

Private Sub chkPresent_AfterUpdate()
If Me.chkPresent = False Then
Me.chkExtent = False
End If
End Sub

and add code to the Before Update event of chkExtent to prevent it being set to True if chkPresent is False, or if the limit has been reached. To make it work, you need to put a text box in the header or footer of the subform (it can be hidden, but on the other hand, it might give the user useful feedback) that counts the number of records with chkExtent set to True. Name this textbox txtCountExtent and set its Control Source to

=Abs(Sum([chkExtent])

The code is:

Private Sub chkExtent_BeforeUpdate(Cancel As Integer)
If Me.chkExtent = True and Me.chkPresent = False Then
Cancel = True
ElseIf Me.txtCountExtent > 2 Then
MsgBox “You can’t have more than three records with extent.”, vbInformation
Cancel = True
End If
End Sub

To disable chkExtent is problematic in a continuous form, for you would disable it for all records. There is no way to disable a control for one record only in a continuous subform.

Reply | Quote