Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem with opening form using RecordSetClone

Posted on 2006-05-09
41
Medium Priority
?
298 Views
Last Modified: 2016-08-29
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******************



0
Comment
Question by:fyrerose
  • 23
  • 16
  • 2
41 Comments
 
LVL 2

Expert Comment

by:dorbraxton
ID: 16642934
RecordsetClone is read only so, Yes, that is why you are getting the error.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16642962
What error message and what codeline?

Nic;o)
0
 
LVL 2

Expert Comment

by:dorbraxton
ID: 16642972
Hmm...  That's not quite right...  You are not using the clone, so...   What is the exact error message?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:fyrerose
ID: 16643043
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16643100
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)
0
 

Author Comment

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

Author Comment

by:fyrerose
ID: 16643168
That is the source of frmEntryForm is qryForm.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16643224
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)
0
 

Author Comment

by:fyrerose
ID: 16643310
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.
0
 

Author Comment

by:fyrerose
ID: 16643327
I have no problem changing a field on the form when it's loaded up without using the search form.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16643348
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)
0
 

Author Comment

by:fyrerose
ID: 16643470
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

0
 

Author Comment

by:fyrerose
ID: 16643495
actually I take it back, it's still giving the same error message............argh
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16643518
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)
0
 

Author Comment

by:fyrerose
ID: 16643529
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
0
 

Author Comment

by:fyrerose
ID: 16643590

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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16643594
Hmm, nothing wrong with that as far as I can see.
What's the cboSegment_AfterUpdate code ?

Nic;o)
0
 

Author Comment

by:fyrerose
ID: 16643631
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16644193
OK, got the .mdb but will have to check tomorrow as it's past midnight...

Nic;o)
0
 

Author Comment

by:fyrerose
ID: 16650059
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


0
 
LVL 54

Expert Comment

by:nico5038
ID: 16650543
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)
0
 

Author Comment

by:fyrerose
ID: 16650586
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16650834
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)
0
 

Author Comment

by:fyrerose
ID: 16650941
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16652091
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)
0
 

Author Comment

by:fyrerose
ID: 16652441
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16652703
Sure that the cboNewRenewal has no tag value ?

Nic;o)
0
 

Author Comment

by:fyrerose
ID: 16652726
No it doesn't, should it?
0
 

Author Comment

by:fyrerose
ID: 16653063
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 .
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16653228
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)
0
 

Author Comment

by:fyrerose
ID: 16653314
Good news I don't get the error message

Bad news, almost all of the controls still dissappear
0
 

Author Comment

by:fyrerose
ID: 16653583
Does it matter that intPolicyLimit is a number field and the controls I'm trying to hide are all text fields?
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 2000 total points
ID: 16653660
You need to place the 1, 2, 3, etc. level numbersin the .tag value corresponding with the level the fields need to be made visible.
I found your fields numbered a bit "confusing" as there's e.g. a 9 and 10 on the same line.

Still failing to get an error when navigating after selecting a name :-(

I'll upload my sample (PMA 05-11-06.zip) to the ee-stuff so you can take a look on the working tags and try for the error.

Make sure that you check the references:
Open some VBA code (e.g. by pressing [CTRL+G]) and select Tools/References.
Each libraryname starting with "MISSING" must be unchecked and (e.g. microsoft DAO version x.xx) replaced when necessary.

As access doesn't cleanup added libraries, often unnecessary refenreces are present.
Just delete as many as possible without getting an error executing "Debug/compile all modules"

Nic;o)

Nic;o)
0
 

Author Comment

by:fyrerose
ID: 16653956
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16653974
>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)
0
 

Author Comment

by:fyrerose
ID: 16660082
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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16660152
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)
0
 

Author Comment

by:fyrerose
ID: 16660183
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)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16660339
:-)
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)
0
 

Author Comment

by:fyrerose
ID: 16660449
Wicked!!

I could kiss you lol

Thanks again :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question