We help IT Professionals succeed at work.

Using VBA to Delete All Controls from an Access 2003 Form

jrimmele
jrimmele asked
on
Medium Priority
1,632 Views
Last Modified: 2012-08-14
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?
Comment
Watch Question

CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2006
Commented:
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

CERTIFIED EXPERT
Top Expert 2006

Commented:
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

Craig YellickDatabase Architect

Commented:
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.
Commented:
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°)
CERTIFIED EXPERT
Top Expert 2016

Commented:
just bear in mind that deleting  or adding anything to the form,

can only be done in forms Design View...

Author

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?
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

CERTIFIED EXPERT

Commented:
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

Author

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...
CERTIFIED EXPERT

Commented:
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

Author

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.
CERTIFIED EXPERT
Top Expert 2006

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.