Access VBA Looping Through Controls Shows Field Value As Control Name

Question

I have a function that loops through all of the controls on a form. If the tag says "audit", it tracks the changes made to that field. It's worked fine until now. Now, it is throwing an error of "Operation is not supported for this type of object". When I turn off the error handling, and check the code, for some reason it is feeding the actual field VALUE in as the control name. Any help would be very much appreciated. Here's the code:

Dim rst As Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM ChangeLog"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

datTimeCheck = Now()
strUserID = Environ("USERNAME")
For Each ctl In Screen.ActiveForm.Controls
    If ctl.Tag = "Audit" Then
        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
            With rst
                .AddNew
                ![ChangeTimeStamp] = datTimeCheck
                ![UserId] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![LeadID] = Lead
                ![EstID] = Estimate
                ![EOINumber] = Order
                ![InstalID] = Install
                ![FieldName] = ctl.ControlSource
                ![FieldValueBeforeChange] = Nz(ctl.OldValue, "Blank")
                ![FieldValueAfterChange] = Nz(ctl.Value, "Blank")
                .Update
            End With
        End If
    End If
Next ctl

Parfait · Answer 1 · 2016-05-24T15:42:34.780

1

Not all controls have the ControlSource property such as command buttons, rectangles, and lines. Only data-driven objects like textboxes, checkboxes, comboboxes, listboxes would have this property. So, consider conditioning your search with the textbox ControlType. Hence, the challenge of looping through all form controls!

...
For Each ctl In Me.Form.Controls
     If ctl.ControlType = acTextBox And _
        ctl.Tag = "Audit" And _
        Nz(ctl.Value) <> Nz(ctl.OldValue) Then
            With rst
                .AddNew
                ![ChangeTimeStamp] = datTimeCheck
                ![UserId] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![LeadID] = Lead
                ![EstID] = Estimate
                ![EOINumber] = Order
                ![InstalID] = Install
                ![FieldName] = ctl.ControlSource
                ![FieldValueBeforeChange] = Nz(ctl.OldValue, "Blank")
                ![FieldValueAfterChange] = Nz(ctl.Value, "Blank")
                .Update
            End With
    End If
Next ctl

edited May 24 '16 at 15:42

answered May 24 '16 at 00:28

Parfait

104,375
17
94
125

I appreciate the suggestion. I will definitely be able to use that to make this more efficient. However, that does not solve the problem. It is still producing the same error. I have a textbox that has a name of "Address", and the value on the particular record I'm testing with is "444 Shadow Hill". When I run the code, it is erring out and showing ctl as "444 Shadow Hill" when I hover over it at the debugging screen. – Jason Jones May 24 '16 at 01:02
On what line does the debugger highlight? On `ctl.ControlSource`? – Parfait May 24 '16 at 01:52
on "For Each ctl In Screen.ActiveForm.Controls" – Jason Jones May 24 '16 at 10:55
Have you tried referencing the form in a different way, such passing it as an argument in this sub `mySub(frm As Form)` and then changing the offending line to `For Each ctl In Frm.Controls`? You'd then supply the form as an argument with the sub call. – Matt Hall May 24 '16 at 11:59
I will make that change and see if it helps. I was attempting to avoid that, as this is my audit tracking code for every single form on the system, so that change would require updating every form. But, I will see if it helps on this form and go from there. It really seems to me that this is a serious bug, however. There's no circumstance that I can think of where, with ctl set up how it is, that it should ever be picking up the VALUE of the fields instead of the name. Either way, I'll update with how that code change works out. – Jason Jones May 24 '16 at 12:30
I believe it is more a process than programming issue. Why update for all forms if they are not actively being updated? Place this code in a standard module and pass the form name as a parameter and run it with same trigger event: `For each ctl in Forms(formparamname).Controls` – Parfait May 24 '16 at 14:10
This code doesn't update all forms at once. This code is called in the "before update" event of each form, to check if any changes were made to the data, and log the changes that occurred. The parameters of the function are fed in from each form. That's what I meant by having to update each form if I add a parameter for form name. I'm not disputing that that will be the best method to move forward. But, it doesn't solve my confusion or hesitance at WHY I'm getting this error that doesn't seem like it should be possible. – Jason Jones May 24 '16 at 15:24
@JasonJones - Consider using `Me.Form.Controls` over `Screen.ActiveForm.Controls` which may be impacted by focus of windows. I wish you could screenshot the *showing ctl as "444 Shadow Hill" when I hover over it at the debugging screen* as I have trouble visualizing, but you need at least 10 pts on SO to add to images to your post. Maybe a link from outside site? – Parfait May 24 '16 at 15:42
When you are in debugging mode, you can hover over variables and see what value they are holding at that point in the code. When I hover my mouse over the variable "ctl" at the point where it says "For Each ctl etc...", it shows the value being held as "444 Shadow Hill", which is the VALUE of the control, not the name of the control. Nothing I can see in my code explains how that could happen. I can't post an image right now but I will try to do it once I get home for the evening. – Jason Jones May 24 '16 at 16:55
https://www.dropbox.com/s/nekcas9gkzcnynh/Capture.JPG?dl=0 Here is a link to an image of what i am talking about, Parfait. Also, Matt, I made the changes you suggested, but I am still receiving the same error. I appreciate the attempt, though. – Jason Jones May 24 '16 at 17:02
First, debuggger is highlighting the `if Nz(ctl.Value)...` line not the `For Each ctl` line as you mention above. Second, `ctl` and `ctl.Value` are the same thing. In fact, the intellisense that hints when typing VBA code does not have `.Value` option for me on 2013. So the hovered address value is the current value of textbox. Try removing `.Value`. Finally, I cannot recreate your issue. Try [De-compile/Re-compile db with Compact & Repair](http://stackoverflow.com/questions/3266542/how-does-one-decompile-and-recompile-a-database-application) as sometimes VBA code can throw strange errors. – Parfait May 24 '16 at 21:22
The debugger is highlighting that section because of the of the .value section. Because "444 Shadowlawn" is not a real control, which is causing the error. When i dimension "ctl" at the top, I'm dimensioning it as a Control. My code is saying for each control do this. So, the VBA should be seeing ctl as a control, not as a control value. I'm starting to think it's just a nasty bug. Compiling did not work, nor did compact & repair. I appreciate your attempt at assistance. It's looking more and more like I'm going to have to rebuild from scratch. – Jason Jones May 24 '16 at 23:18
Calling ctl is referencing the value much like calling the cell in Excel VBA returns the value. Understand the object model of control with various properties: Name, Type, OldValue, Tag, etc. Value is the main property which does not need explicit call, again like Excel's `Cell.Value.` – Parfait May 24 '16 at 23:31

MC Dotta · Answer 2 · 2022-10-04T05:56:17.210

It's a little bit late but I ran into the same issue... And I realize that this is the comparison that throws the error. If you set the oldvalue to a variable and the current value to a variable, comparing both variables is OK.

DO NOT WORK

If me.mycontrol.oldvalue <> me.mycontrol.value then

DO WORK

myvariableOld = me.mycontrol.Oldvalue 
myvariableNew = me.mycontrol.Value
If myvariableOld <>  myvariableNew then

Access VBA Looping Through Controls Shows Field Value As Control Name

2 Answers2