Loop thru all controls on a form/report and delete them

[Happy YN]

Is there any way i can loop through all controls on a form/report and delete them.
I have seen a thread about this and I think Pat explained it needs to be done backwards but I'd appreciate it if anyone has achieved this to show me the code.
I keep on getting an error that the count is more than the actual number of controls.
Thanks

 

[llkhoutx]

Here's my stupid question, Why must you do this programmatically? If you're deleting all controls, why can't you open the form/report in design view and mere select all of them, then press the Delete key?

Most, if not all, Access counters start at 0, rather than 1.

'with the form/report open, the following code works well in a class function.
dim ctl as control
for each ctl in forms!frmName (or Reports!ReportName)
'ctl.name is the name of the control
next

I've never needed to delete any controls in a form/report programmatically, I often hide them and then make them visible as appropriate.

Hope the foregoing helps.

 

[Happy YN]

But to delete them is a problem,
What is the code ? delete ctl.name?

Thanks

 

[llkhoutx]

I don't know.

 

[ChrisO]

G’day Happy_NY.

I don’t know either but could not care less except for the following…

If ‘Pat explained it needs to be done backwards’ then she obviously had something in mind…Pat does not usually make mistakes but may have been taken out of context.

In general, a collection that has a count property will (should) update the count property if something is deleted. Kind of makes sense, collection has 100 items, delete one, now it has 99, requery collection count, collection count should now be correct.

But does the requery collection count happen without forcing it, i.e. on delete?

Now with controls I would not bet on it because even if you could delete a control you would not necessarily reclaim the memory used by that control. Furthermore, a control can not be deleted without going in to design mode which leads to a singular conclusion.

Don’t do it.

There are two primary reasons for not doing so. The first is that you may still bump into the error of ‘Maximum controls in the life of a form’ and the second is that you forever limit the database to an MDB file.

Furthermore, llkhoutx asked you a question and the answer was not forthcoming.
Perchance to try again?

Regards,
Chris.

 

[Happy YN]

Thanks
I am writing this report on the fly because the user has control as to which fields appear on it. This isbecause it is fed from a crossstab query and therefore the fieldnames are constantly changing depending on the criteria of the query which is feeding the crosstab.
Hence the report is different each time with different labels.textboxes widths etc - it needs to be done on the fly. Now Icould delete the whole report and start again but becaus i have some formatting which I want to apply to the detail section i want to at least save the empty report with that code in the on format section. so I need to delete all controls.
I trust I have explained myself and it doesn't matter to me if it has to stay an mdb. Also i think if the new controls are the same name as the old ones it will not count themas new controls and stop them at 750
So I still need a method to delete all controls
Thanks

 

[ChrisO]

“Also i think if the new controls are the same name as the old ones it will not count themas new controls and stop them at 750”

I would not bet on that either.

If they have the same name then why delete them because they could become recyclable?

Regards,
Chris.

 

[llkhoutx]

Why not dynamically change the controlsource of form/report controls, textboxes and labels? I do that regularly with reports.

 

[Pat Hartman]

I have a vague recollection of a suggestion to delete from collections backwards. That means that you start with the max index value and decrement the counter rather than starting with 1 and incrementing the counter. If you play computer and run through the code line by line, you'll understand why.

In this case I have a better idea than deleting the controls. Save a copy of the report with NO controls. Then you only have to worry about adding new controls and not deleting the old ones. Also, since you are actually replacing the report each time you make it, you won't have to worry about running into the max controls problem which you most certainly would if you continue on your present path.

 

[Happy YN]

Thanks for the good idea Pat! So what would i do? save a blank report , then open it (in design view), save it as a different name , and start adding controls?
I tried saving the report using docmd.save ,"newreportname" but it says newreportname is not open. If I code docmd.save acreport, "newreportname" I get a different error that the argument is not valid for that object type.
So how would I go about having controls but previewing and printing without saving or even no prompt for saving?
Thanks

 

[john471]

Copy Object

Try

DoCmd.CopyObject [destinationdatabase][, newname][, sourceobjecttype, sourceobjectname]

HTH

Regards

John.

 

[Happy YN]

Thanks
Tried it but when it copies the report it does not copy the underlying code so thats no good. Also when closing the new object I get an error that it cannot be closed??
So I'm back to the old problem
"How can I create a new report object based on a current one including the code?"
Thanks

 

[Pat Hartman]

The TransferDatabase Method/Action properly copies database objects including their class modules.

DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\data\work\msi\msiorders.mdb", acReport, "rptCodeTblRpt", "rptCodeTblRptNew", False

I tested it by referencing the db in which I ran the code and there was no conflict. No error is raised if you run it multiple times. It just writes over the existing object so you don't even have to delete the old report first.

 

[Happy YN]

Thanks Pat. It copies Ok but still does not preserve the code in the original report which is:

Private m_RowCount As Long
 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
m_RowCount = m_RowCount + 1
    If m_RowCount / 2 = CLng(m_RowCount / 2) Then
            Me.Detail.BackColor = 15263976 'Change value to the color you desire
        Else
            Me.Detail.BackColor = 14811135 'Change value to the color you desire
    End If
End Sub

 

[Pat Hartman]

I tested it

- It works for me. The CopyObject didn't copy the report's class module but the TransferDatabase did.

 

[Happy YN]

Finally got it going!
I think it wan't working because the vba interface was open???
Thanks a lot