Required checkbox on Form

[Linda2431]

Hi - I have a form that is filled out by users in the front end database. I wrote some code in the form in the "After Update" event procedure to ensure that certain fields are filled out before going to the next form. The code works EXCEPT for the two items in red below. These are check boxes whereas the first three items are combo boxes and text boxes.

If IsNull(Me.Combo111) Then
MsgBox "You must enter a LOB"
Me.Combo111.SetFocus
End If
If IsNull(Me.Combo82) Then
MsgBox "You must enter your initials"
Me.Combo82.SetFocus
End If
If IsNull(Me.[Loan Nbr]) Then
MsgBox "You must enter a Loan Number"
Me.Combo82.SetFocus
End If
If IsNull(Me.Review_Start) Then
MsgBox "Please Click Review Start"
Me.Review_Start.SetFocus
End If
If IsNull(Me.Review_Complete) Then
MsgBox "Please Click Review Complete"
Me.Review_Start.SetFocus
End If

What code do I need which would tell the user to make sure they check the box before moving to the next record? I typed in "If 0", but that didn't work either.

I'm still a very new user -- so I very much appreciate any assistance - thank you very much!!!

Linda

 

[pr2-eugin]

Any final checks for a Form entry should be made in From_BeforeUpdate.. AfterUpdate is triggered after an update is performed.. When you use the Before Update you can cancel the update.. Making sure that the data is entered.. So try the following..

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Me.Combo111 & vbNullString) = 0 Then
        MsgBox "You must enter a LOB"
        Me.Combo111.SetFocus
        Cancel = True
        Exit Sub
    End If

    If Len(Me.Combo82 & vbNullString) = 0 Then
        MsgBox "You must enter your initials"
        Me.Combo82.SetFocus
        Cancel = True
        Exit Sub
    End If

    If Len(Me.[Loan Nbr] & vbNullString) = 0 Then
        MsgBox "You must enter a Loan Number"
        Me.Combo82.SetFocus
        Cancel = True
        Exit Sub
    End If

    If Len(Me.Review_Start & vbNullString) = 0 Then
        MsgBox "Please Click Review Start"
        Me.Review_Start.SetFocus
        Cancel = True
        Exit Sub
    End If

    If Len(Me.Review_Complete & vbNullString) = 0 Then
        MsgBox "Please Click Review Complete"
        Me.Review_Start.SetFocus
        Cancel = True
        Exit Sub
    End If
End Sub

I have also changed the Null checks as

If Len(theControl & vbNullString) = 0 Then

As the above will capture both Null and Zero Length Strings.. as Null <> ZLS

 

[Linda2431]

I like the way this works -- much better!! -- however what do you mean by you changed the Null checks as:
If Len(theControl & vbNullString) = 0 ThenI copied/pasted the code you provided and I do like the way I get the error messages which gives me an opportunity to enter in the required information after the next error message. But, I am still not getting error message if I don't check the boxes "Review Start" and "Review Complete".

I know I'm missing something (as I am a novice) . . . . thank you!!!!

 

[pr2-eugin]

I am sorry I did not realize that the last two were Checkboxes, I re-read your post and saw it did state they were check box..So for those two.. try..

    If Me.Review_Start = 0 Then
        MsgBox "Please Click Review Start"
        Me.Review_Start.SetFocus
        Cancel = True
        Exit Sub
    End If

    If Me.Review_Complete = 0 Then
        MsgBox "Please Click Review Complete"
        Me.Review_Start.SetFocus
        Cancel = True
        Exit Sub
    End If

 

[Linda2431]

That worked PERFECT -- thank you, thank you, thank you!!!! I apprecate your assistance very much!

 

[pr2-eugin]

Most welcome Linda.. Glad to help..

 

[Linda2431]

I have another question on this - I had to take out the last code for the checkbox for Review Complete. I have included the form in the attachment. For this form, the auditors fill in the top part of the form and select "Review Start". They then jump to the sub-form and use that form to fill in information. After the information in the subform is filled out, the auditors then need to click "Review Complete". Is there a process that would ask "Please Click Review Complete" after information is input into the subform?

 

[pr2-eugin]

Hi Linda, I appreciate you attaching the DB, but unfortunately there is no Dummy data, so it is hard to test case and see which method would be appropriate. But anyway, try the Sub Form's Before Update event.

 

[Linda2431]

I believe I tried that to no avail . . . I will try again though. I will fill in some dummy data and queries if it doesn't work and re-post. Thank you!

 

[pr2-eugin]

Yea, as I said, it might be hard to see which event would be best.. So if there is a few data it might help..

Also on a side note.. I am (mostly) away for the weekend, so if you do need help.. I would suggest you upload an MDB version, so other might help you as well..

 

[Linda2431]

Thanks - am creating a DB w/ some test information. This isn't a rush, so next week is fine. I VERY MUCH appreciate your assistance! I'll post something later today - THANKYOU!

 

[Linda2431]

Thanks - for the form "frmAuditResults-Manual", the auditors fill in the LOB, Review Date, Loan #, Auditor, and click "Review Start". They they fill in the information in the sub-form. After filling out the information in the sub-form, they need to go back and click the "Review Complete" box. If the "Review Complete" is not checked, I would like them to receive a message when they try to go to the next record stating "Please check Review Complete". Thank you!!!