CODEWITHSUNDEEP
vbams-access
Lux Claridge
Lux Claridge

Reputation: 205

Sub to add controls works when called from one sub but the other

I am getting an error that I just can't figure out. I wrote a sub that opens a form in Design view, deletes all of the dynamic controls, then adds the requested number.

The sub gets called in two different ways. The user opening the form (via a Main Menu form) to fill out a new form (so the dynamic controls are deleted then recreated) OR after the form is created, the user can click a button on the form to add more rows of controls. Both the Main Menu and button form call the same sub, BUT when the button is clicked the code gets stuck and error 29054 'Microsoft Access can't add, rename, or delete the control(s) you requested.' is thrown. The button to debug is deactivated so I can't see what line is actually getting stuck, but when I step through the code this is the last line before the error pops up (the last indent block in the context below):

    With Application.CreateControl("BOM5", acComboBox, acDetail, frm.Controls("Tabs").Pages(PageNum).Name, , ChangeTypeLeft, LastControlTop, ChangeTypeWidth, ControlHeight)

The rest of the code is as follows.

    DoCmd.OpenForm "BOM5", acDesign
    Set frm = Application.Forms("BOM5")

    ' Cycle through controls and set LastControlTop based on the last dynamic control, if any
    For i = 0 To frm.Controls.Count - 1
        Set ctl = frm.Controls(i)
        Debug.Print ctl.Name
        If ctl.Tag Like DYNAMIC_TAG & "*" Then
            If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
                LastControlTop = ctl.Top + ControlHeight + ControlPadding
                FormRowCount = FormRowCount + 1
            End If
        Else
            FormRowCount = (FormRowCount / 6) + 1      ' Convert number of fields to number of rows then add one to start new batch of controls

            Exit For
        End If
    Next

    PageNum = frm.Controls("Tabs").Pages.Count - 1      ' .Pages has an index of 0. Getting PageNum to follow suit with the -1

    ' Add controls for inputting parts
    For FormRowCount = FormRowCount To NewControlCount
        With Application.CreateControl("BOM5", acComboBox, acDetail, frm.Controls("Tabs").Pages(PageNum).Name, , ChangeTypeLeft, LastControlTop, ChangeTypeWidth, ControlHeight)
            .Name = "ChangeType" & FormRowCount
            .Tag = DYNAMIC_TAG
            .RowSourceType = "Table/Query"
            .RowSource = "ChangeType"
        End With

The last for loop has 5 other With Application.CreatControl statements, but I just showed the first one. The other 5 are similar except text boxes instead of combo.

I've had this error before, but I think I resolved it by moving the DoCmd.OpenForm statement to a different part of the code (like out of an if statement or for loop or somewhere that wasn't letting it get called) but I don't think that will resolve it. Besides, the first for loop iterates correctly since I see it grabbing the control height of the last dynamic control.

Upvotes: 1

Views: 301

Answers (1)

Gustav
Gustav

Reputation: 55841

You can't do this. A form or report can only hold a certain amount of controls, deleted or not, so - eventually - it will not accept more controls. At that point, you must recreate the form from scratch.

So, don't delete the controls. Create those you may need and, at any time, hide those not needed and rename the rest if necessary.

Upvotes: 2

Related Questions