Tab Control Form Crashes Database When I Switch Tabs

[theosgood]

Access 2003 format database, running in Access 2007

I have a tabbed control form with 10 tabs. It has worked well for a year.
On one tab, I have 93 Ad Panels
I need to add Zone 18 plus one more Ad Panel.
I created the control and associated code. Once by copying another zone and once from scratch.
It does what it is supposed to on the tab. But when I switch to the next tab in the form, Hierarchy, the database crashes with the error below. (Ad Region is hidden). When I remove Ad Zone 18 everything works again.
I've tried the debug that is in the error but it only opens SQL Server with errors.



The code for the Ad Zone tab is below and the form and error message attached.
Any ideas on why it crashes when you change tabs?

Private Sub RICO_Click()
    TurnOnPanelinZone "RICO"
End Sub

'This sub sets the color and font wieght for the checked panel on the Ad Panel/Zone tab
Sub TurnOnPanelinZone(Panel As String)
    Dim strLabel As String
    Dim strPct As String
    Dim vRegion As String
    Dim vPanel As String
    
    vPanel = Panel
    strLabel = Panel & "_Label"
    strPct = Panel & "_Pct"
    
    vRegion = DLookup("[region_name]", "qryAdPanel", "[panel] =""" & vPanel & """")
    
    If Me.Controls(Panel) Then
        Me.Controls(strLabel).ForeColor = lngOnColor
        Me.Controls(strPct).BorderColor = lngOnColor
        Me.Controls(strPct).BorderStyle = 1
        Me.Controls(strPct).ForeColor = lngOnColor
        Me.Controls(vPanel).BorderColor = lngOnColor
        Me.Controls(strLabel).FontBold = True
        Me.Controls(strPct).FontBold = True
        Me.Controls(vRegion).Value = True
    Else
        Me.Controls(strLabel).ForeColor = lngOffColor
        Me.Controls(strPct).BorderColor = lngOffColor
        Me.Controls(strPct).ForeColor = lngOffColor
        Me.Controls(strPct).FontBold = False
        Me.Controls(strLabel).FontBold = False
        Me.Controls(vRegion).Value = False
    End If
End Sub

Private Sub ChkZone18_Click()
    TurnOnPanelListZ zone.Zone18, Me.ChkZone18
End Sub
'zone.Zone18 = 1800
'Me.ChkZone18 = -1

'This creates a recordset of all the Ad Panels in a Zone
'Runs the TurnOnPanelinZone. Sets the checkbox to Clicked
Private Sub TurnOnPanelListZ(ZoneNumber As Integer, chckbox As Boolean)
   Dim PanelLst As ADODB.Recordset
   Set PanelLst = New ADODB.Recordset
   Dim PanelStr As String
   Dim vRegion As String
      
   PanelLst.Open "Select panel, region_name from tblAdPanel where ad_zone_number = " & ZoneNumber, CurrentProject.Connection
   
   Do While Not PanelLst.EOF
        PanelStr = PanelLst.Fields("panel").Value
        vRegion = PanelLst.Fields("region_name").Value
        
        If chckbox = True Then
            Me.Controls(PanelStr).Value = True
            Me.Controls(vRegion).Value = True
            TurnOnPanelinZone (PanelStr)
            PanelLst.MoveNext
        Else
            Me.Controls(PanelStr).Value = False
            Me.Controls(vRegion).Value = False
            TurnOnPanelinZone (PanelStr)
            PanelLst.MoveNext
        End If
   Loop
   
End Sub

[June7]

There is a limit on number of controls that can be on a form or report. It is possible you have exceeded that limit.

Number of controls and sections that you can add over the lifetime of the form or report is 754, and that includes deleted controls

http://office.microsoft.com/en-us/ac...010341462.aspx

[theosgood]

That was in the back of my head someplace but thanks for the reminder. I was at 851 controls. I've wanted to re-design the backend table structure for a year now and update the form accordingly, But no one wants to pay for new work, just keep it running. I did eliminate one tab and that got me down to 541 controls. Works like it's supposed to again. Thanks.

[June7]

Then I guess the limit doesn't include deleted controls although that's what I have repeatedly read.

[theosgood]

Been away but wanted to finish this. I had read the same thing about the counts including deleted. I put a button on the form with the simple code below, ran it at 841, deleted the tab, compacted and repaired, new button count was 521. It appears "including deleted" is not accurate.

<code>
Private Sub Command1069_Click()
Dim ctl As Access.Control
Dim lngCount As Long

For Each ctl In Me
lngCount = lngCount + 1
Next ctl

MsgBox lngCount
End Sub
<code/>