• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1199
  • Last Modified:

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.
0
jkassil
Asked:
jkassil
2 Solutions
 
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
 
jkassilAuthor 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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
 
jkassilAuthor 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
 
jkassilAuthor Commented:
Thanks everyone
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now