Display message if form on Tab-Control is Blank

[Chris]

I use the following Function to display a message box on
the main form if the selected tab-Sub-form is empty. It works if I'm logged
in with access level 1, but if I'm logged in with 2 or > then nothing. I
watch the process blow right past the "If IsNull(Forms![MAINTAIN
CAR]![cntPAActions].Form![INV_RPT_ACTION]) Then" statement and go to the
"Else" part. NOTE: Previously, I had used this code to lock the user's
rights on
the form. So no blank form is available to Users with Access Level > 1.

*******************************************
'This procedure checks the users access level as
'defined in the "tblPasswordTable" table. The Main Menu
If Forms![Main Menu]![AccessLevel] > 1 Then
frmFormName.AllowAdditions = False
frmFormName.AllowDeletions = False
frmFormName.AllowEdits = False
Else
End If
********************************************


THIS IS THE CODE I CAN'T SEEM TO GET TO WORK RIGHT.
*********************************************
Function MessageMaintainCARS()
On Error GoTo Err_MessageMaintainCAR
'**************************************************************************************
'Name: MessageMaintainCARS
'Purpose: This function is used to display a message when no record shows in
' the related sub-form.
'
'Author: Chris Premo
'Date: October 23, 2009
'Called by: This function is called by the "On Change" event of the "cntForm"
' Tab Control form on the "Maintain CARS" form
'**************************************************************************************

'This procedure enables a message for users not able to edit the data.
'The message tells the user that no sub-form record exists for the
current case.
If Forms![Main Menu]![AccessLevel] > 1 Then

'Control number for cmdPAAcction
If Forms![MAINTAIN CAR]!cntForm.Pages(0).Visible = True And
Forms![MAINTAIN CAR]!cntForm.Value = 0 Then
If IsNull(Forms![MAINTAIN
CAR]![cntPAActions].Form![INV_RPT_ACTION]) Then
Forms![MAINTAIN CAR]![ErrorMsg].Visible = True
Forms![MAINTAIN CAR]![ErrorMsg].Caption = "No PA Action
Record Exists for this Case!"
Else
Forms![MAINTAIN CAR]![ErrorMsg].Visible = False
End If
'Control number for cmdSearchWarrant
ElseIf Forms![MAINTAIN CAR]!cntForm.Pages(1).Visible = True And
Forms![MAINTAIN CAR]!cntForm.Value = 1 Then
Forms![MAINTAIN CAR]![cntWarrants].Visible = True
If IsNull(Forms![MAINTAIN CAR]![cntWarrants]) Then
Forms![MAINTAIN CAR]![ErrorMsg].Visible = True
Forms![MAINTAIN CAR]![ErrorMsg].Caption = "No Search Warrant
Record Exists for this Case!"
Else
Forms![MAINTAIN CAR]![ErrorMsg].Visible = False
End If
'Control number for cmdViolations
ElseIf Forms![MAINTAIN CAR]!cntForm.Pages(2).Visible = True And
Forms![MAINTAIN CAR]!cntForm.Value = 2 Then
If IsNull(Forms![MAINTAIN CAR]![cntViolations].Form![VIOL_CD])
Then
Forms![MAINTAIN CAR]![ErrorMsg].Visible = True
Forms![MAINTAIN CAR]![ErrorMsg].Caption = "No Violation
Record Exists for this Case!"
Else
Forms![MAINTAIN CAR]![ErrorMsg].Visible = False
End If
'Control number for cmdArrests
ElseIf Forms![MAINTAIN CAR]!cntForm.Pages(3).Visible = True And
Forms![MAINTAIN CAR]!cntForm.Value = 3 Then
If IsNull(Forms![MAINTAIN
CAR]![cntArrests].Form![cARREST_SURREND]) Then
Forms![MAINTAIN CAR]![ErrorMsg].Visible = True
Forms![MAINTAIN CAR]![ErrorMsg].Caption = "No Arrest Record
Exists for this Case!"
Else
Forms![MAINTAIN CAR]![ErrorMsg].Visible = False
End If
'Control number for cmdDisposition
ElseIf Forms![MAINTAIN CAR]!cntForm.Pages(4).Visible = True And
Forms![MAINTAIN CAR]!cntForm.Value = 4 Then
If IsNull(Forms![MAINTAIN CAR]![cntDisposition].Form![disp_cd
lookup]) Then
Forms![MAINTAIN CAR]![ErrorMsg].Visible = True
Forms![MAINTAIN CAR]![ErrorMsg].Caption = "No Disposition
Record Exists for this Case!"
Else
Forms![MAINTAIN CAR]![ErrorMsg].Visible = False
End If
Else
End If
Else
End If

Exit_MessageMaintainCARS:
Exit Function

Err_MessageMaintainCARS:
MsgBox Error$
Resume Exit_MessageMaintainCARS

End Function

 

[Chris]

Maybe it would ge easier to just hide the Tabs when there is not sub record
data. How would I do this????

 

[Chris]

Woohoo, found the answer on the WEB!!

cntName is the name of the form, cntForm is the name of the tab Control object
"Case_No" is a control on the main form ("Maintain CARS").
*********************************************
Dim mySubForms As New Collection
mySubForms.Add [Forms]![Maintain Car].cntPAActions
mySubForms.Add [Forms]![Maintain Car].cntWarrants
mySubForms.Add [Forms]![Maintain Car].cntViolations
mySubForms.Add [Forms]![Maintain Car].cntArrests
mySubForms.Add [Forms]![Maintain Car].cntDisposition
Dim myPages As New Collection
myPages.Add [Forms]![Maintain Car]!cntForm.Pages(0)
myPages.Add [Forms]![Maintain Car]!cntForm.Pages(1)
myPages.Add [Forms]![Maintain Car]!cntForm.Pages(2)
myPages.Add [Forms]![Maintain Car]!cntForm.Pages(3)
myPages.Add [Forms]![Maintain Car]!cntForm.Pages(4)
Dim rs As Recordset
Dim i As Integer
DoCmd.GoToControl ("CASE_NO")
For i = 1 To mySubForms.Count
Set rs = mySubForms.Item(i).Form.RecordsetClone
If (rs.RecordCount = 0) Then
myPages.Item(i).Visible = False
Else
myPages.Item(i).Visible = True
End If
Next i