Referencing Access SubForm fields using variables

I am using variables to hold field names so that I can reference various fields on my form...
Example
      Dim lvlName_1(5) as string
      lvlName_1(1) = "fieldname1"
      lvlName_1(2) = "fieldname2"
      etc...

      For i = 1 To 5
         If Nz(Me(lvlName_1(i)), "") = "" Then
            Me(lvlName_1(i)).BackColor = ORANGE
            dataCheck = False
            statusINT = 0
           Else
            Me(lvlName_1(i)).BackColor = WHITE
         End If
      Next i

Does anyone know if I can use this technique to reference fields on a subform?

I expected to do...
      Dim lvlName_2(5) as string
      lvlName_2(1) = "me.mainform.subform.forms!fieldname1"
      lvlName_2(2) = "me.mainform.subform.forms!fieldname2"
      etc...

      For i = 1 To 5
         If Nz(Me(lvlName_2(i)), "") = "" Then
            Me(lvlName_2(i)).BackColor = ORANGE
            dataCheck = False
            statusINT = 0
           Else
            Me(lvlName_2(i)).BackColor = WHITE
         End If
      Next i

But alas, this did not work.
Jerry KassilSystem EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can use the Controls collection of the form to do this,

dim ctl as control
for each ctl in me.controls
       debug.print ctl.name

next


what is the ultimate goal here?
0
Jerry KassilSystem EngineerAuthor Commented:
I want to highlight a number of fields to alert the user that a value is required by changing the controls background color from white to orange. I also enable or disable buttons on the form allowing the user to progress to the next step in the process based on whether all the required fields have been populated.
0
Rey Obrero (Capricorn1)Commented:
see this similar thread


http://www.experts-exchange.com/Database/MS_Access/Q_26716098.html#a34478055


post back if you encounter any problem.. and off course you must tweak the codes to suit your requirements (the code is generic)
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Helen FeddemaCommented:
First, you have to understand the difference between fields and controls.  When you drag fields to a form in Access, they are given the same name as their fields, but controls have different properties than fields, so it is a very good idea to apply a naming convention, such as the Leszynski Naming Convention (LNC).  You can download my free add-in that automatically applies the LNC to database objects and controls:

Access 2000-2003
=======================================
http://www.helenfeddema.com/Files/code10.zip
http://en.wikipedia.org/wiki/Leszynski_naming_convention

Access 2007-2010
====================================
http://www.helenfeddema.com/Files/code63.zip

For doing something to controls on a form, such as changing their back color, cycle through the controls, and do something based on the value saved to the control's Tag property.  Here is some sample code:

Public Sub CycleControls(frm As Access.Form)

   Dim ctl As Access.Control
   
On Error Resume Next

      For Each ctl In frm.Controls
         Debug.Print ctl.Name 
         If ctl.Tag = "Lock" Then
            ctl.Locked = True
         End If
      Next ctl
         
End Sub

=================

Private Sub Form_BeforeUpdate(Cancel As Integer)
      
   Dim ctl As Access.Control
   
   For Each ctl In Me.Controls
      Debug.Print "Testing " & ctl.Name
      If ctl.Tag = "Required" Then
         Debug.Print "Control value: " & ctl.Value
         If Nz(ctl.Value) = "" Then
            ctl.BackColor = vbRed
            Cancel = True
         Else
            ctl.BackColor = vbWhite
         End If
      End If
   Next ctl

End Sub

========================

Public Sub SwitchVisibility(frm As Access.Form, blnValue As Boolean)
'Created by Helen Feddema 21-Dec-2010
'Last modified by Helen Feddema 21-Dec-2010

On Error Resume Next

   Dim ctl As Access.Control
   
      For Each ctl In frm.Controls
         Debug.Print ctl.Name
         If ctl.Tag = "Switch" Then
            ctl.Visible = blnValue
         End If
      Next ctl
         
End Sub

Open in new window

0
Jerry KassilSystem EngineerAuthor Commented:
Thanks Helen. My apologies for not being precise in my comments. I do understand the difference between controls and fields. Your technique will work on both the main form and it's sub-form? How would you check that the subform indeed has records, cause I would also like to trap for this too?
0
PatHartmanCommented:
For main forms you almost always have a single record but for subforms you almost always have many records.  Access only maintains one set of properties for each form so when you set properties of controls on a subform, that setting will apply to ALL visible records.  So if you set the background of controlA to Orange, all visible instances of controlA will be orange.   You would put this code to set the properties in the Current event of the subform.  You would not run it from the main form.

If that doesn't work for you, you will need to use conditional formatting and format each control individually based on some value of a field of the current record.  So, you might want to set negative amounts to red so you would use conditional formatting on the amount control to set the font to red for amounts less than 0.
0
Rey Obrero (Capricorn1)Commented:
@ jkassil

if you will take a look at the link / thread I posted,

you will see that you need to use the beforeupdate of the form to find those empty controls that are required.

as I've said, if you encounter a problem post back.

.

.btw, to refer to the controls in the subform the codes will be like this


dim ctl as control, ctl1 as control
for each ctl in me.controls
       debug.print ctl.name

      if ctl.controltype=acsubform then

           for each ctl1 in me(ctl.name).controls
                debug.print ctl1.name
           next
   
 
       end if
next
0
Gustav BrockCIOCommented:
You will do it like this:

     Dim lvlName_1(5) as string

     lvlName_1(1) = "fieldname1"
     lvlName_1(2) = "fieldname2"
     etc...

     For i = 1 To 5
         If Nz(Me.SubformControlName.Form(lvlName_1(i)), "") = "" Then
            Me.SubformControlName.Form(lvlName_1(i)).BackColor = ORANGE
            dataCheck = False
            statusINT = 0
         Else
            Me.SubformControlName.Form(lvlName_1(i)).BackColor = WHITE
         End If
     Next i

Note please, that SubformControlName is the name of the subform control holding the subform, not the name of the subform.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry KassilSystem EngineerAuthor Commented:
Thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.