Using VBA to Delete All Controls from an Access 2003 Form

I have a form that needs to have all of the controls deleted each time it loads.  The number of controls and their names vary based on the results of a query, so I have code that creates new controls and control names each time the form is loaded.  Before I create the controls, I need to delete the controls created from the previous time the form was loaded.  i tired the following code, but it returns an error message:

Dim Ctrl As Control


For Each Ctrl In Forms!addresses

    DeleteControl Forms!addresses, Ctrl

Next Ctrl

Any ideas how to delete all controls on the form without knowing in advance exactly how many there are?
jrimmeleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hi jrimmele,
This is a doomed methodology I'm afraid.

There is a restriction of 754 controls per form during the lifetime of the form.  Each time you replace 20 old controls with 20 new ones you are eating into the 754.

You should create a new form every time.

Pete
rockiroadsCommented:
why dont u just delete the form then create a new one? would that not be simpler?

to access controls

its

    For Each ctl In Me.Controls
        DeleteControl Me.Name, ctl.Name
    Next ctl



Note  u have to open form in design mode in order to do this

rockiroadsCommented:
sorry when I meant recreate form, I meant from a template form - one that you can simply copy

the loop I gave you deletes all controls, you best add a tag (if not already used) or prefix the control name with something and place and if within the loop, so u only delete what u want

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Craig YellickDatabase ArchitectCommented:
I've done something similar but rather than deleting and creating controls, what I've done is create the maximum number of controls I need ahead of time and then make the ones I don't need invisible during the form load process, then size the form to match.  This works great if all controls are, for example, textboxes or checkboxes. It does not work for a mix of control types.
harfangCommented:
Follow Pete's advice. It's also much easier to program (you can count on all default properties).

However, a question should not get unaswered on EE, so:

DeleteControl requires two string parameters, like this:

    DeleteControl "addresses", Ctrl.Name

What's more, the loop will either break or skip controls, as you are modifying the loop control collection from within the loop. This can be overcome with this:

    With Form!addresses
        Do While .Controls.Count
            DeleteControl .Name, .Controls(0).Name
        Loop
    End With

This being said, do follow Pete's advice ;)
(°v°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
just bear in mind that deleting  or adding anything to the form,

can only be done in forms Design View...
jrimmeleAuthor Commented:
ok..that all makes sense, but it leads me to one more question...

The form I am creating has a command button with code attached to it.  if I delete the form and recreate it, how would I recerate the code that is attached to the command button?
rockiroadsCommented:
ok, when talking about deleting form, and recreating it, I was talking about a template form.

Say you have a form called frmTemplate

So what u do is

DoCmd.DeleteObject acForm, "frmFormUsed"
DoCmd.CopyObject , "frmFormUsed", acForm, "frmTemplate"

your frmTemplate has all the code




But knowing Access and how prone it is to corruption, I guess you need to monitor and regularly compact/repair the database

I think CraigYellick has a very good idea

Create as many controls as you need
then make them all hidden

then in code, simply unhide as many as you want, but your restricted by the number of controls you create.
You will also have to code in the positioning i.e. left, top positions

this is pretty easy though

peter57rCommented:
I confess to liking Rockiroads solution but it does mean that you can never use the application as an mde or use the app in an Access runtime-only setting.
(But I have to have a gun at my head before I use either of these myself).

Pete
jrimmeleAuthor Commented:
I tried creating the form from a template, but the problem is that the template only preserves the properties of the form, not the controls.  So, my template has a command button which does not get re-createded when I create a new form based off of it.  I can create the button itself each time a new form is created, but that still leaves me with the problem of attaching code to the click event for the button...
peter57rCommented:
I don't think the solution was to use a template in its strict Access sense, just to have a ready-made form there which you could copy when you needed it.
The code from Rockiroads last response does this.

CopyObject copies the whole form and its code module as a form with the designated name.

Pete
jrimmeleAuthor Commented:
Yes, I just tried to post a comment, but I guess I didn't save it...I used CreateForm using a template.  That did not save the controls on my template form.  However, when I use CopyObject, the controls are also copied.
rockiroadsCommented:
Pete is right with what I was referring to

create yourself a form (from blank, no need for template). You already have one because you are working on one now

u can use that as your form that you use as the basis to create copies of
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.