For each... and the forms collection (1 Viewer)

[JamesMcS]

Afternoon everyone! Wondered if you could help me out on this rather simple one...

What I need to do is add a string to the tag property of certain textboxes in certain forms in my DB. How do I say "for each form in currentdb.forms"? Here's what I've got so far:

Sub ReTag_Controls()
Dim Frm As Form
Dim ctl As Control

For Each Frm In CurrentProject.AllForms

    If InStr(Frm.Name, "Subform") Then
    
        
        For Each ctl In Frm.Controls
        
            If Not InStr(ctl.Name, "GPD") And Not InStr(ctl.Name, "_") And Not InStr(ctl.Name, "Tagged") And ctl.Type = acTextBox Then
            
                ctl.Tag = ctl.Tag & "No_Export"
                
            End If
            
        Next ctl
    
    End If

Next Frm

End Sub

I'm getting a type mismatch on the currentproject.allforms line. Grr...

 

[gemma-the-husky]

when I have done stuff like this, I have had to examine the documents container, to find the forms - maybe current project.all forms will do the same

you then have to open each form in design mode, make your changes, and then save it.

so maybe you can do it your code, by adding

docmd.openform acdesign (aircode) before trying to iterate the controls

at the end you will have to save the form with docmd.closefrom

you probably will need a doevents as well, as the screen gets messy otherwise.

hope this helps

 

[JamesMcS]

Hi Dave, thanks very much for that - I'm looking at declaring the variables as objects at the moment as per some code snippets I'm googling.

Hope you're well buddy!

 

[JamesMcS]

OK, now I've got this far:

Sub ReTag_Controls()
Dim Proj As Object
Dim Frm As Object
Dim ctl As Control

Set Proj = Application.CurrentProject

For Each Frm In Proj.AllForms

If Frm.Type = acForm And InStr(Frm.Name, "Subform") Then
    
        For Each ctl In Frm.Controls
        
            If Not InStr(ctl.Name, "GPD") And Not InStr(ctl.Name, "_") And Not InStr(ctl.Name, "Tagged") And ctl.Type = acTextBox Then
            
                ctl.Tag = ctl.Tag & "No_Export"
                
            End If
            
        Next ctl
    
End If

Next Frm

End Sub

and it's erroring on the for each ctl in frm.controls - "object doesn't support this property or method." Nearly there.....

 

[LPurvis]

In this instanc, frm is an object variable of an AccessObject type.
It contains no reference to the object itself per se. And the form isn't necessrily open. (A requirement to examine its child controlls collection.)

You could open each form first.

Dim frmObj as Access.Form
...
DoCmd.OpenForm frm.Name, acDesign
Set frmObj = Forms(frm.Name)
For Each ctl In frmObj.Controls
...

Edit: Dup info just noticed - bit late here, sorry. But hopefully aircode helps give some direction?

 

[MarkK]

You can also return all the form names in the database from the MSysObjects system table using SQL like ...

SELECT Name
FROM MSysObjects
WHERE Type = -32768;

 

[Galaxiom]

You can also return all the form names in the database from the MSysObjects system table using SQL like ...

Here is another way to do that. (Linq would be so proud )

The Forms Collection only holds opened forms but the AllForms has them all. It won't show the design information like controls but you can get the Name property.

I expect it is really just another way to query the mSysObjects table and would probably be able to return much the same data as lagbolt's query technique offers. It does save having to remember -32768 which of course is negative 2^15, the biggest negative integer.

Note however the forms are not actually treated as forms in this context.

So while you can do this with the Forms Collection:

Dim frm As Form
 
For Each frm In Forms
    Debug.Print frm.Name
Next

You need this for the AllForms Collection

Dim frm As Object
 
For Each frm In CurrentProject.AllForms
    Debug.Print frm.Name
Next

Object does the job but I would be curious if anyone knows what kind of object more specifically.

Edit: Now I see I have just repeated the repetition.

 

[JamesMcS]

Thanks for your inputs all - it's month end at the moment but I'll check these methods out when that's all done and let you know how I get on

 

[gemma-the-husky]

To add to my previous - if currectentproject.allforms does the same as this, then you can use that. I think in A97, there may not have been that option, so this worked instead. I think the usage is similar to what LPurvis described above.

    Set dbs = CurrentDb
    Set ctr = dbs.Containers!Forms

    For Each doc In ctr.Documents
        docmd.openform doc.name, acdesign
        ...
        docmd.closeform doc.name acsaveall     
        doevents
    next

I think the acdesign, and acsaveall are correct, but I haven't checked.
doevents is not necessary, bit does keep the screen tidy. (try it without and see!)

you need to be careful about whether you process the current active form, depending on what you want to do.

You can use this code to manipulate forms. eg I use this code to set all forms to consistent settings, eg set background form image, set help file library and so on.

 

[ChrisO]

I think I must be missing the point here.

If we can use the following calculated control to store "No_Export" in its Tag property…

If Not InStr(ctl.Name, "GPD") And Not InStr(ctl.Name, "_") And Not InStr(ctl.Name, "Tagged") And ctl.Type = acTextBox Then
    ctl.Tag = ctl.Tag & "No_Export"
End If

then we should be able to use the same calculation to decide if the control should be exported or not.

As far as I can see at the moment, the control name and type must comply with the calculation; that I would assume is a given.
But it is not a given that someone will remember to run the Tag update code.
So when it gets to the point of using the Tag property it could fail if the update has not been run.

Perhaps we could look upon this as storing a calculated value.

So that may be just another way to skin the cat…don’t skin it at all.

Chris.

 

[MarkK]

And in keeping with the history of repetition in this thread, I'd like to echo Chris's observation that if you have some process that accurately tags controls, then you don't need to store the result of that process by actually tagging those controls. Run that process as and when required.
What I routinely do if I want to maintain and expose a subset of controls on a form is this ...

Property Get MyControlSet() As Variant
  MyControlSet = Array(Me.Text0, Me.Text2, Me.Text4)
End Property

This advertises in code that the controls in question are some sort of important sub-group. This property is also available via intellisense so it is easy to reference and other database objects can easily consume it.
Mark

 

[Galaxiom]

Stopher came up with a fascinating construction to associate controls a few months ago. He put the controls on a transparent tab.

Can't find the post but we discussed several other ways to do it including tag, name patterns, subforms and collections.

 

[Galaxiom]

As far as I can see at the moment, the control name and type must comply with the calculation; that I would assume is a given.
But it is not a given that someone will remember to run the Tag update code.
So when it gets to the point of using the Tag property it could fail if the update has not been run.

If the goal is a persistent change to the Tag property as part of a routine operation then the whole idea is flawed.

Any process that cannot be done in Runtime mode should not be part of normal operation of the database.

The Tag should be a permanent, unchanging value. If the correlation of the Tag with some particular action needs to be user definable, that correlation should be stored in a table or something like an ini file.

 

[ChrisO]

Galaxiom.

I don’t know who you are replying too.
But if you quote me and then do not shift focus from that quote to another person then it is implied you are replying to me.

So, if your intension is to reply to me then you are ‘preaching to the converted’ because I was the first in this thread to suggest it should not be done at all.

Now technically, this is incorrect:-
>>If the goal is a persistent change to the Tag property as part of a routine operation then the whole idea is flawed.<<
The incorrectness centres on the term ‘routine operation’ and its ambiguity of use.
If the ‘routine operation’ is at user runtime then it is correct.
But if the ‘routine operation’ is at development time it is incorrect.

So we can now have a look at the ‘routine operation’ at development time.
It is technically valid to do global configuration changes during development. Things like form colours, fonts and the like can be done globally during development. But, during development these global changes are run many times; they become part of a ‘routine operation’. So the developer makes some changes and then runs the global fix. They then make more changes and run the global fix again.

But in this case it does not need to be done and could lead to an error and indeed a total mess in the Tag property.
Take for example this line of code given:-

ctl.Tag = ctl.Tag & "No_Export"

After 5 runs of the ‘routine operation’ at development time the Tag property looks like this:-
No_ExportNo_ExportNo_ExportNo_ExportNo_Export
and that is not the intension.

So that is the third fault of the code as written so far. The first is that it is not necessary, the second is that the code may not be run and cause an error and the third is that the Tag property gets trashed.


-------------------------------
Galaxiom, please let me be very clear about this.
In post #7 you had not picked up on any of the reasons not to do this.
In post #10 I stated reservations about doing it at all.
In post #11 lagbolt reinforced the idea.
At this point now, you are ‘preaching to the converted’ by replying to the first person, me, to state any reason not to do it.
If you are not replying to me then please try to remove the ambiguity, unintentional or otherwise.

Please don’t try to obfuscate the matter as many others do on the web…please be more careful.
-------------------------------


Chris.

 

[Galaxiom]

Galaxiom.

I don’t know who you are replying too.
But if you quote me and then do not shift focus from that quote to another person then it is implied you are replying to me.

Chris,

Your imputation that a quote implies a reply is nothing more than your confused interpretation. I, and I am sure many other posters, consider all replies are addressed to The Board unless otherwise specifically addressed in the text to aanother poster. Indeed the only place the word "reply" appears on the board is the email message, "... has replied to the thread ...".

Observe that the button that initiates a post including a quote is called "Quote" rather than "Reply". Any smiliarity in action to the Reply button in an email application does not change that meaning.

A quote is used to maintain continuity. Threads often diverge into multiple trains of thought. A quote may be used as the basis for either a reply or a post that builds upon the concepts in the quote.

Galaxiom, please let me be very clear about this.
In post #7 you had not picked up on any of the reasons not to do this.
In post #10 I stated reservations about doing it at all.
In post #11 lagbolt reinforced the idea.
At this point now, you are ‘preaching to the converted’ by replying to the first person, me, to state any reason not to do it.

In post #7 I simply enlarged on lagbolt's post without reading the whole thread (as did Leigh). As indicated in the edit I subsequently realised that aspect had already been covered.

In my last post I clearly restated the implied meaning of your post as a general principle. Your pointless pedantic analysis of the meaning of "routine" seems motivated more by your sense of indignation than a productive contribution. My intent was reasonably clear though I admit I could have been improved by including the first two sentences in a single paragraph.

Quite frankly the rest of your post looks a lot like "grandstanding" mixed with a liberal dose of paranoia that another poster would poach your kudos.

Please don’t try to obfuscate the matter as many others do on the web…please be more careful.

Since it would appear that you have experienced this same confusion at other sites perhaps you could consider the problem is actually your misinterpretation of the meaning of "Quote" rather than with the use of quotes by other posters.

Failing that I would suggest you simply, "get over it".