Solved

Referencing Access SubForm fields using variables

Posted on 2014-04-02
9
834 Views
Last Modified: 2014-04-03
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
Comment
Question by:jkassil
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:jkassil
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jkassil
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
@ 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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:jkassil
Comment Utility
Thanks everyone
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now