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

Posted on 2012-03-28
Medium Priority
Last Modified: 2012-03-29
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()
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?

Question by:LimeRidge29
LVL 29

Accepted Solution

IrogSinta earned 600 total points
ID: 37780265
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

LVL 14

Assisted Solution

pteranodon72 earned 400 total points
ID: 37781414
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


Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

807 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