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

Access 2007 - Update TabIndex on Subform Fields on Update of Parent Form Field

Hello All,

I would like to be able to update the column display positions in a Subform (Report_Dtl_subform), based on the selection from a field (Fld_Rpt_Type) on the Parent form.  So when, for example, "Activity" is chosen in Fld_Rpt_Type, the Activity column should appear in Column 2 (TabIndex = 1) on the Subform.  The other two columns (Fld_Client_Name and Fld_Employee_Name) are moved to Col 3 and Col 4.

So to test , I added this code for Fld_Rpt_Type:

Private Sub Fld_Rpt_Type_AfterUpdate()
If Fld_Rpt_Type.Value = "Activity" Then
Form_Report_Dtl_subform.Fld_Activity_Name.TabIndex = 1
MsgBox ("Activity Name Tab = " & Form_Report_Dtl_subform.Fld_Activity_Name.TabIndex)
Form_Report_Dtl_subform.Fld_Client_Name.TabIndex = 2
Form_Report_Dtl_subform.Fld_Employee_Name.TabIndex = 3
End If
End Sub

And I refresh the Subform:
Private Sub Report_Dtl_subform_Enter()
Me.Refresh
End Sub

When I select "Activity", the MsgBox returns "Activity Name Tab = 1", so it appears to be working.  But of course, the columns don't change.  And when I look at the Subform design, the TabIndex properties are not changed.

I also tried updating the Visible property to "No" for the columns that I did not want, but they remained visible.

Clearly I am missing something here.  Or maybe what I am trying to do is just not possible?

Regards,
Chris
0
LimeRidge29
Asked:
LimeRidge29
2 Solutions
 
IrogSintaCommented:
I believe TabIndex is used to determine the order of controls that gets the focus when you press the Tab key.  For example, if your form has textboxes for entering First & Last name, Address, City, State, Zip... every time you press the Tab key your cursor will jump to the control with the next index number.

I can think of 3 ways to accomplish what you want:
1)  Assuming your subform is a continuous tabular form and not a datasheet, you can create a routine that would change the Left property of each control and position them in the order you want them.  This may not be an easy task.

2)  You can create many subforms, one for each of your selections, where each one has the prescribed order.  Then on your Fld_Rpt_Type_AfterUpdate() you would change the source object to point to the corresponding form. (ie.  Me.Report_Dtl_subform.sourceobject = "sfNameOfYourSubform").

3)  Instead of using a subform you can use a listbox on your main form and update the listbox RowSource to match your choice.  Here's a sample code to do just that:
 
Private Sub Fld_Rpt_Type_AfterUpdate()
    Select Case Me.Fld_Rpt_Type
        Case "Activity"
            Me.lbxDetail.rowsource ="Select Activity, ClientName, EmployeeName From YourTableName"        
        Case "Client Name"
            Me.lbxDetail.rowsource ="Select ClientName, EmployeeName, Activity From YourTableName"
        Case "Employee Name"
            Me.lbxDetail.rowsource ="Select EmployeeName, Activity, ClientName From YourTableName"
    End Select        
End Sub  

Open in new window

0
 
pteranodon72Commented:
LimeRidge29,
IrogSinta has given you great suggestions if the subform is not a datasheet. However, if your subform is in datasheet view and you want to re-order the columns the property that you will modify is ColumnOrder. This property and others  (like ColumnHidden, ColumnWidth) that control the display of columns in datasheet mode that don't show up in the Properties sheet in Form Design view but are changeable in VBA.

If Fld_Rpt_Type.Value = "Activity" Then
   Form_Report_Dtl_subform.Form!Fld_Activity_Name.ColumnOrder = 1
   Form_Report_Dtl_subform.Form!Fld_Client_Name.ColumnOrder = 2
   Form_Report_Dtl_subform.Form!Fld_Employee_Name.ColumnOrder = 3
End If

pT72
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: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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