Full Version: Loop Thru Forms In Currentdb.allforms ?
UtterAccess Forums > Microsoft® Access > Access Forms
I'm trying to change the background color on a form (whether the form is currently open or not), based on the click event of a label.
Private Sub labelbrown_Click()
Dim myform As String
Dim mycolor As String
mycolor = Me.labelbrown.BackColor
myform = Me.List3.Value
Dim frm As Object
For Each frm In CurrentProject.AllForms
If frm.NAME = myform Then
frm.Sections(acDetail).BackColor = mycolor
End If
Next frm
End Sub

It doesn't seem that I have the frm.Sections(acDetail).BackColor = mycolor
Specifically, once I Dim something as an Object, there is no intellisense after the item. Why? I tried declaring as a form, that doesn't work at all. (why?)
What should I do..?
Hi Isaac,
On object is just a generic entity so I don't think Intellisense can figure out what you intend to use it for.
Just my 2 cents...
Actually, AllForms is not same collection as Forms. Each element of All* collections are actually AccessObject which is not same thing as Form object.
herefore, you'd do something like this:
Dim ao As Access.AccessObject
Dim frm As Access.Form
'Ensure all forms are closed
Do Until Forms.Count = 0
  DoCmd.Close acForm, Forms(0).Name
For Each ao In CurrentProject.AllForms
   DoCmd.OpenForm ao.Name, acDesign, WindowMode:=acHidden
   Set frm = Forms(ao.Name)
   With frm
   End With
   DoCmd.Close acForm, frm.Name
Thanks -
Oended up running this code, which by the way the backcolor for the label that was pressed was 128.
How every one of my forms says that I changed the record source to "128". Of course I had a very recent backup, I'm fine, but .... I can't tell what I"m doing wrong?
Can you post the current code? Are you explicitly setting .Section(acDetail).Backcolor = 128 within the code?
When you decalare a particular object type in VBA, you only get the intellisense for the base class that you have declared. Consider Controls for instance, there's a bunch of different types of Controls (tab controls, text boxes, combo boxes, buttons, etc etc). You can declare a VBA object reference by it's specific control type or by it's base class. Here's an example of two different ways to declare a tab control:
Dim TabCtrl As Access.TabControl
Dim ctl As Access.Control
Set TabCtrl = Me.ThisTabControl
Set ctl = Me.ThisTabControl

Now you have two object variables that point to the same object, even though they are declared as different types of objects. The TabControl's intellisense will give you all methods/properties that apply to a TabControl. The Control's intellisense will only give you methods/properties that are common among all of it's "child" classes (textboxes, tab controls, buttons, etc). Now consider this: even though the intellensense doesn't exist to reference a Control's Page() collection, as long as the control it references has a page collection, you can still use it:
Dim ctl1 As Control
DIm ctl2 As Control
Set ctl1 = Me.ThisTabControl
Set ctl2 = Me.ThisTextboxControl
Debug.Print ctl1.Pages(0).Caption 'perfectly legal
Debug.Print ctl2.Pages(0).Caption 'perfectly illegal

So, when referring to a generic or base class that the actual object derives from, we often don't see the intellisense because we want to use something that's specfic to our type of object. But just because the intellisense doesn't show it, that doesn't mean it can't be used - it just means that VBA can't apply it to every possible reference to the Controls objects.
All that said, Object is the base control. All other VBA objects (Forms, Recports, Recordsets, Connections, Controls, etc: whatever you apply using "Set") are derived from this, and you can therefore use Object as a generic reference to pretty much anything, and you can execute methods and read properties just as you would if you had instead declared it as it's specific type - just no intellisense to help you, and make sure you don't screw up because you won't find out until runtime.
The Object datatype you declared in your procedure is perfectly legal, if not exactly the most desirable. When you declare as a generic object type, you tell VBA that you'll ensure everything will be ok rather than the other way around.
(This is the core concept behind late binding as well - in the automation sense at least. Consider that if we program Outlook from Access VBA but don't want to set a reference to it... instead of having all the olSomeName stuff, instead we declare everything as an Object, and when the code runs hopefully it finds the Outlook specific methods/properties that you are telling it to with the object that you provide)
Hopefully this was more help than it was confusing...
Just to point a niggle - what Jack says is generally true of many objects, except for the oddball AccessObject. For inexplicable reasons, you cannot use AccessObject as a generic object as you could with Control.
You can do this:
Dim ctl As Access.Control
Set ctl = Me.MyTextbox 'OK

FOr to make it more explicit:
Dim ctl As Access.Control
Dim txt As Access.TextBox
Set txt = Me.MyTextBox 'OK
Set ctl = txt 'Still OK because Access.TextBox is "derived" from Access.Control

With AccessObject objects, this does not work:
Dim frm As Access.Form
Dim ao As Access.AccessObject
Set frm = Forms(0) 'First open form
Set ao = frm 'Not OK; type mismatch error

Hence the weird reach-around in the code I posted above for looping through AllForms (which contains AccessObjects, not Forms, despite the name) by using its Name property to open a instance of form of the same name. There's similar effect when we try to use DAO.Document which is also named after Access objects but are not representative of those. (think of Access.AccessObject & DAO.Document as analogous to a book cover - it's not the actual book itself neither is it derived from any kind of book but the cover tells us what this book contains, what it's about...)
Hope that also helps and doesn't muddle the water.
I just want to say I greatly appreciate the replies, I am just waiting for a few more hours to dig in deeper. Did something stupid, I replaced the current copy of the .mdb I'd been working on, with a backup copy I bragged about in one of my earlier posts....Only realized that my backup copy had been corrupted during my backup process and I really didn't have a backup copy, except one so old it would require gigantic rework. So I am now waiting for the server folks to restore to me the copy they promise to backup each night..from last night.
Way every negative joke I've ever made about the IT dept be forgotten by the gods! (for now at least)
I will post back once I have my beloved .mdb back, as well as re-think my backup process.
Back up often!
Omake a ZIP (backup) of every database before I open it. I also make a ZIp every hour and before and after major changes. I then make another backup when I exit for the day. I will something have 20+ backups in a day as I work.
Back up often!
Before testing I back up the front end and the back end(s). This also me to roll back the data if needed. Another reason to split your app from the start.
Back up often!
Between my workplace LAN and the house and my external drive where my dev files reside, I rotate saving backups to 5 HDDs across three machines plus the external. You can NEVER have too many backups! (actually, the rotation scheme is an ISO requirement for our live database backup, including having weekly copies that are in separate physical locations in case of fires, etc - gotta love working with the medical and aerospace industries)
've contemplated burying CD backups in the backyard just in case!
.. and I also use Cloud space to store copies for my backups on two different sites!
You guys are too funny! No, i say that with my foot all the way down my throat. the thing is I know better than what I did, so shame on me.
I am usually pretty good, almost obsessive (which sounds like the norm) on backing things up. I figured that out early - (one of few things). I actually do a variety of weird things that I can get away with, including zipping and sending to my home address (nothing wrong with yahoo plus to store valuable items!). and I do have a ton of backups, I guess the most recent one I had just hadn't actually gotten backed up like I thought. I must have either ran my backup process while it was open, or something weird, cuz it appeared to be there but was only a tiny file not the real file.
I'm glad of this post and now i will backup twice as often and more carefully -
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.