Array of Form controls

[Guest]

Hi, im trying to loop through a list of form controls to determine what has
been changed, now im completley lost here so if someone can point out where
im going wrong id appreciate it.. thanks in advance

Dim frm As Form
Set frm = Forms!main
Dim ctrl As Object
ctrla = Array("ppm_omr", "ppm_lmr", "ppm_oillc", "ppm_oildc", "ppm_ib_irm",
"ppm_ib_id", "ppm_ob_irm", "ppm_ob_id" _
, "ppm_monthly1", "ppm_monthly2", "ppm_monthly3", "ppm_monthly4",
"ppm_monthly5", "ppm_monthly6" _
, "ppm_bmonthly1", "ppm_bmonthly2", "ppm_bmonthly3", "ppm_bmonthly4",
"ppm_bmonthly5", "ppm_bmonthly6" _
, "ppm_quarterly1", "ppm_quarterly2", "ppm_quarterly3", "ppm_quarterly4",
"ppm_quarterly5", "ppm_quarterly6" _
, "ppmx_lampsize", "ppmx_installed", "ppmx_installedmeter", "ppmx_serial")

For Each varitem In ctrla
Set ctrl = varitem
If (frm!ctrl.OldValue <> frm!ctrl.Value) Then
MsgBox varitem & "is differant"
End If

Next varitem

im trying to use the value of an array as the control name on a form, i keep
getting an error saying access cant find the field varitem

 

[Allen Browne]

Your array contains strings (the name of the controls), so you may be able
to do something like this:

Dim ctl As Control
Dim strItem as String
Dim i As Long

ctrla = Array(...

For i = LBound(ctrla) To UBound(ctrla)
strItem = ctrla(i)
Set ctl = Me.Controls(strItem)
If ctl.Value = ctl.OldValue Then
'do nothing
Else
Debug.Print ctl.Name & " is different"
End If
Next

Note that the "do nothing" is designed to cope with the cases where
ctl.Value is null or where ctl.OldValue is null, or where the 2 values are
different. If that's a new concept, see the 6th item in this article:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

It might be possible to write more generic code that loops through all the
controls on the form, figures out which ones have a Control Source property
(lines and labels don't), skips those that are unbound (Control Source is a
zero-length string), or bound to expressions (starts with =), or bound to
calculated query fields (the Field in the RecordsetClone of the form has no
SourceTable), and then tests the others for whether Value and .OldValue
match. The code in this link effectively does that:
http://allenbrowne.com/ser-56.html