Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form-level validation (XP sp1)

    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 <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form-level validation (XP sp1)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form-level validation (XP sp1)

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    I'm clearly not having a good day.

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form-level validation (XP sp1)

    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)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form-level validation (XP sp1)

    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 <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •