• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1208
  • Last Modified:

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?
0
jrimmele
Asked:
jrimmele
  • 4
  • 3
  • 3
  • +3
3 Solutions
 
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
0
 
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

0
 
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.
0
 
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°)
0
 
Rey Obrero (Capricorn1)Commented:
just bear in mind that deleting  or adding anything to the form,

can only be done in forms Design View...
0
 
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?
0
 
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

0
 
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
0
 
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...
0
 
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
0
 
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.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now