jrimmele
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just bear in mind that deleting or adding anything to the form,
can only be done in forms Design View...
can only be done in forms Design View...
ASKER
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?
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?
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
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
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
(But I have to have a gun at my head before I use either of these myself).
Pete
ASKER
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...
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
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
ASKER
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.
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
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
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