Link to home
Start Free TrialLog in
Avatar of fyrerose
fyrerose

asked on

Problem with opening form using RecordSetClone

I'm using the following code to open up my main form from a search form.  The search feature works fine and opens the form as it should, however when I navigate to another record I get an error message pointing toward the BeforeUpdate event of my main form.

Is this because it's opening using the recordsetclone and if so how can I fix that?

*********** Begin Search Form Code******************
Option Compare Database

'**********************************
'Created by Roger Carlson         *
'Roger.Carlson@spectrum-health.org*
'Rog3erc@aol.com                  *
'**********************************

Dim itemindex As Integer

Function Bsearch(low As Integer, high As Integer, target As String) As Integer
'Non-recursive version
Dim mid As Integer
Do
    mid = Int(low + high) / 2
    If low > high Then
        Bsearch = -1
        Exit Function
    End If
    If low = high Then
        Bsearch = low
        Exit Function
    End If
    If StrComp(target, Left(lstEmp.ItemData(mid), Len(target)), 1) = 0 Then
        If StrComp(target, Left(lstEmp.ItemData(mid - 1), Len(target)), 1) = 0 Then
            high = mid - 1
        Else
            Bsearch = mid
            Exit Function
        End If
    ElseIf StrComp(target, Left(lstEmp.ItemData(mid), Len(target)), 1) = -1 Then
        high = mid - 1
    Else
        low = mid + 1
    End If
Loop

End Function

Private Sub lstEmp_DblClick(Cancel As Integer)

   'Dimension variables.
   Dim formname As String, SyncCriteria As String
   Dim frm As Form, rs As DAO.Recordset

   'Set the formname to "frmEntryForm," the form that will be
   'synchronized.
   formname = "frmEntryform"

   'Check to see if the Main form is open. If it
   'is not open, open it.
   If Not SysCmd(acSysCmdGetObjectState, acForm, formname) Then
      DoCmd.OpenForm formname
   End If

   'Define the form object and Recordset object for
   'the Main form.
   Set frm = Forms(formname)
   Set rs = frm.RecordsetClone

   'Define the criteria used for the synchronization.
   
    SyncCriteria = BuildCriteria("NamedInsuredTableID", dbBoolean, Me.lstEmp.Column(1))

   

   'Synchronize the corresponding record in the Products form to
   'the current record in the subform.
 
   rs.FindFirst SyncCriteria
 

   'If a record exists in the Products form, find the
   'matching record.
   If rs.NoMatch Then
      MsgBox "No match exists!", 64, formname
   Else
      frm.Bookmark = rs.Bookmark
   End If

Exit_ReportList_Click:
    Exit Sub

Set rs = Nothing

End Sub

Private Sub txtTarget_Change()
If txtTarget.Text = "" Then
    lstEmp.Selected(0) = True
    lstEmp.Selected(0) = False
Exit Sub
Else
    itemindex = Bsearch(0, lstEmp.ListCount - 1, txtTarget.Text)
End If

If itemindex = -1 Then
    Beep
Else
    lstEmp.Selected(itemindex) = True
End If
End Sub

*********** End Search Form Code******************

*********** Begin Main Form BeforeUpdate Code******************

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Last saved date and time plus who made the changes

Me.dteSaveDate = Now()
Me.txtUserChange = CurrentUser

End Sub

*********** End Main Form BeforeUpdate Code******************



Avatar of dorbraxton
dorbraxton
RecordsetClone is read only so, Yes, that is why you are getting the error.
Avatar of nico5038
nico5038
Flag of Netherlands image
What error message and what codeline?

Nic;o)
Avatar of dorbraxton
dorbraxton
Hmm...  That's not quite right...  You are not using the clone, so...   What is the exact error message?
Avatar of fyrerose
fyrerose

ASKER

The error code I get is this:

Run-time error '-2147352567 (80020009)':
Update or CancelUpdate without AddNew or Edit

I click debut and it takes me to the main form (frmMainEntry) BeforeUpdate event.

If I remove the above BeforeUPdate code the error stops appearing.
Avatar of nico5038
nico5038
Flag of Netherlands image
Hmm, what's the control source (query) of the form ?
When it's a read-only query, then this can be explained.
Just open the query in the query editor and try to change a field to check.

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

The control source is "qryForm".  If I open it up in the Query editor I can easily change one or more of the fields.
Avatar of fyrerose
fyrerose

ASKER

That is the source of frmEntryForm is qryForm.
Avatar of nico5038
nico5038
Flag of Netherlands image
Whe you change a field on the form, does that work too ?
When not you'll notice a "read-only" message in the status bar bottom left.

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

I also have the following in place

Private Sub Form_Current()
'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

    If Me.NewRecord = True Then
        Me.frmAL.Visible = False
        Me.frmALPofUL.Visible = False
         
    Else
   
    cboSegment_AfterUpdate
   
    End If
 
Private Sub Form_AfterInsert()

Me.txtSubmissionNumber = Me.NamedInsuredTableID & Me.dteSubmissionDate & Me.txtNamedInsured

End Sub
   
It seems that when I open the form via the search form the record is being edited.  If I click end and then the esc button and then navigate to another record I don't get that error message agan.
Avatar of fyrerose
fyrerose

ASKER

I have no problem changing a field on the form when it's loaded up without using the search form.
Avatar of nico5038
nico5038
Flag of Netherlands image
Check the Default values of the fields, perhaps an empty field is filled and thus triggering the form's update..
The code looks OK, although the Form_AfterInsert is strange, but no problem when txtSubmissionNumber i no field in your table...

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

This is the whole piece of code I've got in the BeforeUpdate event and only the last piece of it is what is giving my problems.  If I remove the 2 last lines everything works just fine


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msgVar As String, strSetFocus As String
strSetFocus = ""

'Code generously given by George Velasco in FACRE's IT department

'**************************************Begin Field Validation Code******************************************
   'Start with a generic message beginning with a carriage return
    msgVar = "The following fields are required." _
            & vbCrLf & "Please go back and fill in the necessary information." & vbCrLf & vbCrLf

   
 'Submission Date********************************************************************************************
 
    If IsNull([dteSubmissionDate]) Or [dteSubmissionDate] = "" Then
    'Concatenate the field name to the generic message for a custom message box.
       msgVar = msgVar & "     Submission Date" & vbCrLf
            'If this is the first null field, set as focus
            If (strSetFocus) = "" Then
               'Content of variable string not important, this is to keep focus from being set elsewhere.
               strSetFocus = "dteSubmissionDate"
            End If
       Me.dteSubmissionDate.SetFocus
    Cancel = True
    End If
'Named Insured**********************************************************************************************
   If IsNull([txtNamedInsured]) Or [txtNamedInsured] = "" Then
      msgVar = msgVar & "     Named Insured" & vbCrLf
        If (strSetFocus) = "" Then            'If this is the first null field, set as focus, otherwise this if statement is skipped over.
           strSetFocus = "Contract Number"    'Content of variable string not important, this is to keep focus from being set elsewhere.
           End If
        Me.txtNamedInsured.SetFocus
    Cancel = True
    End If
'State******************************************************************************************************
    If IsNull([txtState]) Or [txtState] = "" Then
        msgVar = msgVar & "     State" & vbCrLf
            If (strSetFocus) = "" Then
                strSetFocus = "Ceding Company"
            End If
         Me.txtState.SetFocus
    Cancel = True
    End If
'Business Type**********************************************************************************************
     If IsNull([txtBusinessType]) Or [txtBusinessType] = "" Then
    msgVar = msgVar & "     Business Type" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "txtBusinessType"
            End If
        Me.txtBusinessType.SetFocus
    Cancel = True
    End If
'Coverage Type**********************************************************************************************
    If IsNull([txtCoverageType]) Or [txtCoverageType] = "" Then
    msgVar = msgVar & "     Coverage Type" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "txtCoverageType"
            End If
        Me.txtCoverageType.SetFocus
    Cancel = True
    End If
'New/Renewal************************************************************************************************
    If IsNull([cboNewRenewal]) Or [cboNewRenewal] = "" Then
    msgVar = msgVar & "     New/Renewal" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "cboNewRenewal"
            End If
        Me.cboNewRenewal.SetFocus
    Cancel = True
    End If
'Branch*****************************************************************************************************
    If IsNull([txtBranch]) Or [txtBranch] = "" Then
    msgVar = msgVar & "     Branch" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "txtBranch"
            End If
        Me.txtBranch.SetFocus
    Cancel = True
    End If
'SIC Code***************************************************************************************************
    If IsNull([lngSICcode]) Or [lngSICcode] = "" Then
    msgVar = msgVar & "     SIC Code" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "lngSICcode"
            End If
        Me.lngSICcode.SetFocus
    Cancel = True
    End If
'Policy Limit***********************************************************************************************
    If IsNull([intPolicyLimit]) Or [intPolicyLimit] = "" Then
    msgVar = msgVar & "     Policy Limit" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "intPolicyLimit"
            End If
        Me.intPolicyLimit.SetFocus
    Cancel = True
    End If
'Line Guide*************************************************************************************************
    If IsNull([txtLineGuideLimit]) Or [txtLineGuideLimit] = "" Then
    msgVar = msgVar & "     Line Guide" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "txtLineGuideLimit"
            End If
        Me.txtLineGuideLimit.SetFocus
    Cancel = True
    End If
'Expiration Date********************************************************************************************
    If IsNull([dteExpDate]) Or [dteExpDate] = "" Then
    msgVar = msgVar & "     Expiration Date" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "dteExpDate"
            End If
        Me.dteExpDate.SetFocus
    Cancel = True
    End If
   
'Policy Number**********************************************************************************************
    If IsNull([lngPolicyNumber]) Or [lngPolicyNumber] = "" Then
    msgVar = msgVar & "     Policy Number" & vbCrLf
        If (strSetFocus) = "" Then
            strSetFocus = "lngPolicyNumber"
            End If
        Me.lngPolicyNumber.SetFocus
    Cancel = True
    End If
   
'***********************************************************************************************************
   
   
    If (strSetFocus) <> "" Then
    MsgBox msgVar, vbInformation, "Oooops....."
    End If
'******************End Field Validation Code************************

'Last saved date and time plus who made the changes

Me.dteSaveDate = Now()
Me.txtUserChange = CurrentUser

End Sub

Avatar of fyrerose
fyrerose

ASKER

actually I take it back, it's still giving the same error message............argh
Avatar of nico5038
nico5038
Flag of Netherlands image
Hmm, can you upload a zipped test .mdb to www.ee-stuff.com and post the link here so we can have a look ?

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

I think this is the culprit.  If I remove it I don't get the error message at all and the search form works as it should.

Unfortunately I need some way of displaying one of those 2 records (or neither if new record) when navigating between records.

Private Sub Form_Current()
'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

    If Me.NewRecord = True Then
        Me.frmAL.Visible = False
        Me.frmALPofUL.Visible = False
         
    Else
   
    cboSegment_AfterUpdate
   
    End If
   
   
End Sub
Avatar of fyrerose
fyrerose

ASKER


I think this is the culprit.  If I remove it I don't get the error message at all and the search form works as it should.

Unfortunately I need some way of displaying one of those 2 records (or neither if new record) when navigating between records.

                                                                                       /\
                                                                                        |
                                                                                        | - 2 forms (not records)
     

Private Sub Form_Current()
'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

    If Me.NewRecord = True Then
        Me.frmAL.Visible = False
        Me.frmALPofUL.Visible = False
         
    Else
   
    cboSegment_AfterUpdate
   
    End If
   
   
End Sub
Avatar of nico5038
nico5038
Flag of Netherlands image
Hmm, nothing wrong with that as far as I can see.
What's the cboSegment_AfterUpdate code ?

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

Private Sub cboSegment_AfterUpdate()
'Make both AL Unit Rate and AL Percent of Underlying Forms invisible
'unless the below conditions are met (RMS & CMU)

    Me.frmAL.Visible = False
    Me.frmALPofUL.Visible = False
   
   
Select Case Me.cboSegment
'When RMS is selected in combobox (cboSegment) AL Unit Rates form becomes
'visible
'When CMU is selected in combobox (cboSegment) AL Percent of Underlying
'becomes visible

Case "RMS"
    Me.frmAL.Visible = True
   
     
Case "CMU"
    Me.frmALPofUL.Visible = True
   
   
Case Else
'nothing

End Select

intPolicyLimit_AfterUpdate

End Sub

Private Sub intPolicyLimit_AfterUpdate()
Me.Painting = False

Select Case Me.intPolicyLimit

Case 1

'Make the 1st Layer visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = False
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = False
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = False
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = False
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = False
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calccuml1LayerTotal
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 950
    Me.boxPricingGrid.Width = 7500

               
Case 2

'Make 1st & 2nd Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc2cumlLayerTotal
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 1250
    Me.boxPricingGrid.Width = 7500
       

Case 3

'Make 1st, 2nd & 3rd Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc3cumlLayerTotal
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 1550
    Me.boxPricingGrid.Width = 7500
   

Case 4

'Make 1st, 2nd, 3rd & 4th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc4cumlLayerTotal
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 1850
    Me.boxPricingGrid.Width = 7500

   
Case 5

'Make 1st, 2nd, 3rd, 4th & 5th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 2150
    Me.boxPricingGrid.Width = 7500
   

Case 6

'Make 1st, 2nd, 3rd, 4th, 5th & 6th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = Nz(Me.curReinsPremLayer6, 0)
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 2450
    Me.boxPricingGrid.Width = 9000

   
   
Case 7
'Make 1st, 2nd, 3rd, 4th, 5th, 6th and 7th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = Nz(Me.curReinsPremLayer6, 0) + Nz(Me.curReinsPremLayer7, 0)
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 2750
    Me.boxPricingGrid.Width = 9000
   
Case 8

'Make 1st through 8th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = Nz(Me.curReinsPremLayer6, 0) + Nz(Me.curReinsPremLayer7, 0) + Nz(Me.curReinsPremLayer8, 0)
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 3050
    Me.boxPricingGrid.Width = 9000

Case 9

'Make 1st through 9th layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = True
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = True
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = True
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = True
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = True
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = True
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = Nz(Me.curReinsPremLayer6, 0) + Nz(Me.curReinsPremLayer7, 0) + Nz(Me.curReinsPremLayer8, 0) _
    + Nz(Me.curReinsPremLayer9, 0)
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 3350
    Me.boxPricingGrid.Width = 9000

Case 10

'Make 1st through 10th layer visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = True
    Me.lbl10thM.Visible = True

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = True
    Me.calc10LayerGL.Visible = True

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = True
    Me.calc10LayerAL.Visible = True

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = True
    Me.cur10thLayerPremium.Visible = True

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = True
    Me.calc10cumlLayerTotal.Visible = True

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = True
    Me.curReinsPremLayer10.Visible = True
   
    Me.curTotalLayerPremium = Me.calc5cumlLayerTotal
    Me.curTotalReinsPremium = Nz(Me.curReinsPremLayer6, 0) + Nz(Me.curReinsPremLayer7, 0) + Nz(Me.curReinsPremLayer8, 0) _
    + Nz(Me.curReinsPremLayer9, 0) + Nz(Me.curReinsPremLayer10, 0)
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = Me.sngAgentCommission * Me.curTotalUmbChargedPremium
    Me.curTotalUmbrellaLessComm = Me.curTotalUmbChargedPremium - Me.curAgentCommission
    Me.boxPricingGrid.Height = 3650
    Me.boxPricingGrid.Width = 9000


Case Else

    Me.lbl1stM.Visible = False
    Me.lbl2ndM.Visible = False
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = False
    Me.calc2LayerGL.Visible = False
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = False
    Me.calc2LayerAL.Visible = False
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = False
    Me.cur2ndLayerPremium.Visible = False
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = False
    Me.calc2cumlLayerTotal.Visible = False
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
    Me.curTotalLayerPremium = 0
    Me.curTotalReinsPremium = 0
    Me.curTotalUmbChargedPremium = Nz(Me.curTotalLayerPremium, 0) + Nz(Me.curTotalReinsPremium, 0)
    Me.curAgentCommission = 0
    Me.curTotalUmbrellaLessComm = 0
    Me.boxPricingGrid.Height = 950
    Me.boxPricingGrid.Width = 7500

End Select

Me.Painting = True

End Sub
Avatar of nico5038
nico5038
Flag of Netherlands image
OK, got the .mdb but will have to check tomorrow as it's past midnight...

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

I changed the form as follows removing the reference to intPolicyLimit_AfterUpdate:

Private Sub Form_Current()
'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

    If Me.NewRecord = True Then
        Me.frmAL.Visible = False
        Me.frmALPofUL.Visible = False
       
   

    Me.lbl1stM.Visible = False
    Me.lbl2ndM.Visible = False
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = False
    Me.calc2LayerGL.Visible = False
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = False
    Me.calc2LayerAL.Visible = False
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = False
    Me.cur2ndLayerPremium.Visible = False
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = False
    Me.calc2cumlLayerTotal.Visible = False
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 950
    Me.boxPricingGrid.Width = 7500
   
   
    Else
   
    Me.Painting = False
   
    cboSegment_AfterUpdate
   
    Me.Painting = True
   
    End If

End Sub

*********************

Private Sub cboSegment_AfterUpdate()
'Make both AL Unit Rate and AL Percent of Underlying Forms invisible
'unless the below conditions are met (RMS & CMU)

    Me.frmAL.Visible = False
    Me.frmALPofUL.Visible = False
   
   
Select Case Me.cboSegment
'When RMS is selected in combobox (cboSegment) AL Unit Rates form becomes
'visible
'When CMU is selected in combobox (cboSegment) AL Percent of Underlying
'becomes visible

Case "RMS"
    Me.frmAL.Visible = True
   
     
Case "CMU"
    Me.frmALPofUL.Visible = True
   
   
Case Else
'nothing

End Select

Select Case intPolicyLimit



Case 1

'Make the 1st Layer visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = False
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = False
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = False
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = False
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = False
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 950
    Me.boxPricingGrid.Width = 7500

               
Case 2

'Make 1st & 2nd Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = False
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = False
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = False
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = False
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = False
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 1250
    Me.boxPricingGrid.Width = 7500
       

Case 3

'Make 1st, 2nd & 3rd Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = False
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = False
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = False
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = False
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = False
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 1550
    Me.boxPricingGrid.Width = 7500
   

Case 4

'Make 1st, 2nd, 3rd & 4th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = False
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = False
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = False
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = False
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = False
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 1850
    Me.boxPricingGrid.Width = 7500

   
Case 5

'Make 1st, 2nd, 3rd, 4th & 5th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = False
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = False
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = False
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = False
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = False
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = False
    Me.curReinsPremLayer6.Visible = False
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 2150
    Me.boxPricingGrid.Width = 7500
   

Case 6

'Make 1st, 2nd, 3rd, 4th, 5th & 6th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = False
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = False
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = False
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = False
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = False
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = False
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 2450
    Me.boxPricingGrid.Width = 9000

   
   
Case 7
'Make 1st, 2nd, 3rd, 4th, 5th, 6th and 7th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = False
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = False
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = False
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = False
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = False
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = False
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 2750
    Me.boxPricingGrid.Width = 9000
   
Case 8

'Make 1st through 8th Layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = False
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = False
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = False
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = False
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = False
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = False
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 3050
    Me.boxPricingGrid.Width = 9000

Case 9

'Make 1st through 9th layers visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = True
    Me.lbl10thM.Visible = False

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = True
    Me.calc10LayerGL.Visible = False

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = True
    Me.calc10LayerAL.Visible = False

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = True
    Me.cur10thLayerPremium.Visible = False

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = True
    Me.calc10cumlLayerTotal.Visible = False

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = True
    Me.curReinsPremLayer10.Visible = False
   
   
    Me.boxPricingGrid.Height = 3350
    Me.boxPricingGrid.Width = 9000

Case 10

'Make 1st through 10th layer visible

    Me.lbl1stM.Visible = True
    Me.lbl2ndM.Visible = True
    Me.lbl3rdM.Visible = True
    Me.lbl4thM.Visible = True
    Me.lbl5thM.Visible = True
    Me.lbl6thM.Visible = True
    Me.lbl7thM.Visible = True
    Me.lbl8thM.Visible = True
    Me.lbl9thM.Visible = True
    Me.lbl10thM.Visible = True

    Me.calc1LayerGL.Visible = True
    Me.calc2LayerGL.Visible = True
    Me.calc3LayerGL.Visible = True
    Me.calc4LayerGL.Visible = True
    Me.calc5LayerGL.Visible = True
    Me.calc6LayerGL.Visible = True
    Me.calc7LayerGL.Visible = True
    Me.calc8LayerGL.Visible = True
    Me.calc9LayerGL.Visible = True
    Me.calc10LayerGL.Visible = True

    Me.calc1LayerAL.Visible = True
    Me.calc2LayerAL.Visible = True
    Me.calc3LayerAL.Visible = True
    Me.calc4LayerAL.Visible = True
    Me.calc5LayerAL.Visible = True
    Me.calc6LayerAL.Visible = True
    Me.calc7LayerAL.Visible = True
    Me.calc8LayerAL.Visible = True
    Me.calc9LayerAL.Visible = True
    Me.calc10LayerAL.Visible = True

    Me.cur1stLayerPremium.Visible = True
    Me.cur2ndLayerPremium.Visible = True
    Me.cur3rdLayerPremium.Visible = True
    Me.cur4thLayerPremium.Visible = True
    Me.cur5thLayerPremium.Visible = True
    Me.cur6thLayerPremium.Visible = True
    Me.cur7thLayerPremium.Visible = True
    Me.cur8thLayerPremium.Visible = True
    Me.cur9thLayerPremium.Visible = True
    Me.cur10thLayerPremium.Visible = True

    Me.calccuml1LayerTotal.Visible = True
    Me.calc2cumlLayerTotal.Visible = True
    Me.calc3cumlLayerTotal.Visible = True
    Me.calc4cumlLayerTotal.Visible = True
    Me.calc5cumlLayerTotal.Visible = True
    Me.calc6cumlLayerTotal.Visible = True
    Me.calc7cumlLayerTotal.Visible = True
    Me.calc8cumlLayerTotal.Visible = True
    Me.calc9cumlLayerTotal.Visible = True
    Me.calc10cumlLayerTotal.Visible = True

    Me.lblReinsPremium.Visible = True
    Me.curReinsPremLayer6.Visible = True
    Me.curReinsPremLayer7.Visible = True
    Me.curReinsPremLayer8.Visible = True
    Me.curReinsPremLayer9.Visible = True
    Me.curReinsPremLayer10.Visible = True
   
    Me.boxPricingGrid.Height = 3650
    Me.boxPricingGrid.Width = 9000



End Select

End Sub


Avatar of nico5038
nico5038
Flag of Netherlands image
Hmm, I can't get the error reproduced in your sample database.

For compacting your code for the visibility I would use the Tag property of the fields.
By setting them to the "level value" like 3 you can use a loop like:

dim ctl as control

For each ctl in me.controls
      if nz(ctl.tag) > 0 then
         if ctl.tag <=intPolicyLimit
              ctl.visible = true
         else
              ctl.visible = false
         endif
      endif
next

When you select all "level controls" you can assign the tag value (under the Other tab of the properties window) in "one go".

Getting the idea ?

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

Ummm....

that's great but I don't understand how it works, methinks this is outside the realm of my understanding right now lol

The error was produced by opening the frmSearchByNamedInsured form, double clicking on one of the entries and then attempting to either navigate forward or backwards.
Avatar of nico5038
nico5038
Flag of Netherlands image
Every control on a form has a Tag property you can use to set in edit mode to any value you want and to test from code.
Just check the properties of a field and see under the Other tab the Tag property.
The For each/Next will check all controls of the form and issue the setting of the .visible property.

I'll ty to reproduce the error again.

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

Great, but I'm getting an error message when moving to a new record:

Run-time error '2165':
You can't hide a control that has the focus
Avatar of nico5038
nico5038
Flag of Netherlands image
Just set the control before the loop to e.g. a button or combo with:
Me.cboSegment.setFocus
' here the rest of the code

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

I'm still getting that error message about the focus.

This is what I'm using:

P.S.  Should I post this as a separate question?

Private Sub Form_Current()
'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

   
       
        'Define "ctl" as a control
        Dim ctl As Control
        Me.cboNewRenewal.SetFocus
        'Loop through each tagged control
        For Each ctl In Me.Controls
            'If tag is greater than zero then
            If Nz(ctl.Tag) > 0 Then
                'If tag is less or equal to value of intPolicyLimit then
                If ctl.Tag <= intPolicyLimit Then
                    'Set control visible if equal or lesser to value of intPolicyLimit
                    ctl.Visible = True
                Else
                    'Otherwise make control invisble
                     ctl.Visible = False
                End If
            End If
        'Loop through next tagged control
        Next
   
End Sub
Avatar of nico5038
nico5038
Flag of Netherlands image
Sure that the cboNewRenewal has no tag value ?

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

No it doesn't, should it?
Avatar of fyrerose
fyrerose

ASKER

Looks like the code is trying to make all of the controls on the form invisible that don't match intPolicyLimit (that would be almost every single control on the form)

'Define "ctl" as a control
        Dim ctl As Control
        Me.cboNewRenewal.SetFocus
        'Loop through each tagged control
        For Each ctl In Me.Controls
            'If tag is greater than zero then
            If Nz(ctl.Tag) > 0 Then <-------- picks up every single control on the form
                'If tag is less or equal to value of intPolicyLimit then
                If ctl.Tag <= intPolicyLimit Then
                    'Set control visible if equal or lesser to value of intPolicyLimit
                    ctl.Visible = True
                Else
                    'Otherwise make control invisble
                     ctl.Visible = False - <------------ makes all controls whose tag doesn't match intPolicyLimit value invisible (even those I didn't tag)
                End If
            End If
        'Loop through next tagged control
        Next
   
End Sub

Results in all controls dissappearing .
Avatar of nico5038
nico5038
Flag of Netherlands image
Strange, looks like the tag is seen as a string, try:

'Define "ctl" as a control
        Dim ctl As Control
        Me.cboNewRenewal.SetFocus
        'Loop through each tagged control
        For Each ctl In Me.Controls
            'If tag is greater than zero then
            If Val(ctl.Tag) > 0 Then
                'If tag is less or equal to value of intPolicyLimit then
                If Val(ctl.Tag) <= intPolicyLimit Then
                    'Set control visible if equal or lesser to value of intPolicyLimit
                    ctl.Visible = True
                Else
                    'Otherwise make control invisble
                     ctl.Visible = False
                End If
            End If
        'Loop through next tagged control
        Next

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

Good news I don't get the error message

Bad news, almost all of the controls still dissappear
Avatar of fyrerose
fyrerose

ASKER

Does it matter that intPolicyLimit is a number field and the controls I'm trying to hide are all text fields?
ASKER CERTIFIED SOLUTION
Avatar of nico5038
nico5038
Flag of Netherlands image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fyrerose
fyrerose

ASKER

Nico,

The reason you couldn't reproduce the error is because the code is in the Form_OnCurrent event and that's missing from the copy you uploaded.

I looked at your copy and the tag code works, but not quite the way I need it to.  I need the controls to change accordingly when going from one record to the next.

I'll tinker with it some and see if I can make it work.

I'll keep you posted.

Thanks!

Rose Marie
Avatar of nico5038
nico5038
Flag of Netherlands image
>I need the controls to change accordingly when going from one record to the next.
For that the OnCurrent code is needed to activate the "AfterUpdate" of the "level combo" :-)

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

OK, I got it to work so now this is what I'm using.

I decided to place the loop in the form_current because part of the code in intPolicyLimit_AfterUpdate is to recalculate the controls and it was creating a couple of problems when called upon from Form_Current.

I've fixed whatever problem I was having with the search form by removing the intPolicyLimit_AfterUpdate reference from Form_Current (it resulted in the record being edited).

I light of the incredible amount of time you spent on helping me with this I want to express my deepest gratitude.

Not only did you help me fix my code but cleaned it up too! :)

Rose Marie

Private Sub Form_Current()

'Define "ctl" as a control
        Dim ctl As Control
        Me.cboNewRenewal.SetFocus
        'Loop through each tagged control
        For Each ctl In Me.Controls
            'If tag is greater than zero then
            If Val(ctl.Tag) > 0 Then '<-------- picks up every single control on the form
                'If tag is less or equal to value of intPolicyLimit then
                If Val(ctl.Tag) <= intPolicyLimit Then
                    'Set control visible if equal or lesser to value of intPolicyLimit
                    ctl.Visible = True
                Else
                    'Otherwise make control invisble
                     ctl.Visible = False '- <------------ makes all controls whose tag doesn't match intPolicyLimit value invisible (even those I didn't tag)
                End If
            End If
        'Loop through next tagged control
        Next

'******************************

'If new record make both AL Unit Rates and AL Percent of Underlying
'forms invisible

If Me.NewRecord = True Then
        Me.frmAL.Visible = False
        Me.frmALPofUL.Visible = False
   
    Else
    'Call procedure from cboSegment_AfterUPdate to display either form accordingly
        cboSegment_AfterUpdate
     
    End If

'**********************************
'Resize boxPricingGrid according to how many layers are being quoted

Select Case Me.intPolicyLimit

Case 1

    Me.boxPricingGrid.Height = 950
    Me.boxPricingGrid.Width = 7500

               
Case 2
 
    Me.boxPricingGrid.Height = 1250
    Me.boxPricingGrid.Width = 7500
       

Case 3
 
    Me.boxPricingGrid.Height = 1550
    Me.boxPricingGrid.Width = 7500
   

Case 4

    Me.boxPricingGrid.Height = 1850
    Me.boxPricingGrid.Width = 7500

   
Case 5

    Me.boxPricingGrid.Height = 2150
    Me.boxPricingGrid.Width = 7500
   

Case 6
   
    Me.boxPricingGrid.Height = 2450
    Me.boxPricingGrid.Width = 9000

   
   
Case 7
   
    Me.boxPricingGrid.Height = 2750
    Me.boxPricingGrid.Width = 9000
   
Case 8

    Me.boxPricingGrid.Height = 3050
    Me.boxPricingGrid.Width = 9000

Case 9

    Me.boxPricingGrid.Height = 3350
    Me.boxPricingGrid.Width = 9000

Case 10

    Me.boxPricingGrid.Height = 3650
    Me.boxPricingGrid.Width = 9000

End Select

End Sub
Avatar of nico5038
nico5038
Flag of Netherlands image
Jus a final remark on your statement:
>I decided to place the loop in the form_current because part of the code in intPolicyLimit_AfterUpdate is to recalculate the controls and it was creating a couple of problems when called upon from Form_Current.

Best to create a separate "Sub" and call that from both the OnCurrent and the combo's AfterUpdate.

Thanks you for your kind words, I'm always pleased to see that something new is learned :-)

Success with your application !

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

I was thinking about creating a separate sub but I don't know how to do it unless it's tied in with another control on the form.  (Can you tell my VBA knowledge has more holes in it than a swiss cheese ? lol)
Avatar of nico5038
nico5038
Flag of Netherlands image
:-)
Just place at the bottom a line with sub and the name:

Sub subName()

' copy/paste here the code

End Sub <= will be inserted by Access after typing the above sub

Now you can refer in the OnCurrent and the AfterUpdate to this with:

Call subName

(The Call can even be dropped, but I prefer to put it there to make the code better readable :-)
Such a sub will prevent that you need to maintain the same code in two places :-)

Nic;o)
Avatar of fyrerose
fyrerose

ASKER

Wicked!!

I could kiss you lol

Thanks again :)