Solved

Referencing Access SubForm fields using variables

Posted on 2014-04-02
9
919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39973649
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
ID: 39973659
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39973679
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39973692
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
 

Author Comment

by:jkassil
ID: 39973722
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 37

Expert Comment

by:PatHartman
ID: 39973758
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39973772
@ 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 50

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 39974270
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
ID: 39974874
Thanks everyone
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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