?
Solved

Using VBA to Delete All Controls from an Access 2003 Form

Posted on 2006-04-03
13
Medium Priority
?
969 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?
0
Comment
Question by:jrimmele
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 668 total points
ID: 16361858
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 668 total points
ID: 16361876
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16361893
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 11

Expert Comment

by:Craig Yellick
ID: 16361895
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
 
LVL 58

Accepted Solution

by:
harfang earned 664 total points
ID: 16361929
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16361968
just bear in mind that deleting  or adding anything to the form,

can only be done in forms Design View...
0
 

Author Comment

by:jrimmele
ID: 16362173
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16362414
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
 
LVL 77

Expert Comment

by:peter57r
ID: 16362481
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
 

Author Comment

by:jrimmele
ID: 16362788
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
 
LVL 77

Expert Comment

by:peter57r
ID: 16363066
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
 

Author Comment

by:jrimmele
ID: 16363110
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16364891
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question