[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-03-28
2
Medium Priority
?
529 Views
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()
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
Comment
Question by:LimeRidge29
[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
2 Comments
 
LVL 29

Accepted Solution

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

0
 
LVL 14

Assisted Solution

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
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…

649 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