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
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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 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