Hi there - I have a datasheet and would like it so that when I select an option from that datasheet in that subform then the subform below is updated to reflect details for that line item.
I have the following code:
There are month and department tabs which I haven't shown in the images below and so it is evaluating the status of the month and whichever Income Statement Secetion and Department is selecteed in the top section e.g. 2. COS and CAB
and what happens is that after it updates the recordsource then it goes back to the top and runs the code from Set form2 = Me.Form again so whatever was selected e.g. 2nd or 3rd option goes back to the top selection in the datasheet so in the example if I choose C Technology Fees Aff then it defaults back to C Technology Fees Aff.
If I run the code without it jumping to the top then it works fine and it runs Me.Parent.Controls![P&LGroupForm].Requery
So what it should show up as is:
I'd like it so that when the form initially loads it fills in the monthly totals but when I make a change by selecting a different line item then it simply does a requery of the form below but not sure what the event procedure is required.
I have tried Private Sub Form_AfterUpdate() and Private Sub Form_Click() but neither of those fire when I choose a different datasheet selection - is there a VBA subroutine I can use other than Private Sub Form_Current()
I've spent quite a bit of time on it and quite get it to do what I need. Pls ask if need any clarification.
Thanks
Paul
I have the following code:
Code:
Private Sub Form_Current()
Set form2 = Me.Form
For Each ctrl In form2
If ctrl.Tag = "Hide" Then ctrl.ColumnHidden = True
Next ctrl
Select Case Me.Parent.MonthTabControl2.Value
Case 0
form2.SumOfFeb.ColumnHidden = False
form2.RecordSource = "SELECT Forecast.[P&L_Group], Count(Forecast.[P&L_Group]) AS [PLCount], Forecast.Dep, Forecast.Income_statement_section, Sum(Forecast.Feb) AS SumOfFeb FROM Forecast GROUP BY Forecast.[P&L_Group], Forecast.Dep, Forecast.Income_statement_section HAVING (((Sum(Forecast.Feb))>0 Or (Sum(Forecast.Feb))<0) AND ((Forecast.Dep)=[Forms]![MonthyDeptTest]![FrmMonthDeptQuery].[Form]![Dep]) AND ((Forecast.Income_statement_section)=[Forms]![MonthyDeptTest]![FrmMonthDeptQuery].[Form]![Income_statement_section]));"
There are month and department tabs which I haven't shown in the images below and so it is evaluating the status of the month and whichever Income Statement Secetion and Department is selecteed in the top section e.g. 2. COS and CAB
and what happens is that after it updates the recordsource then it goes back to the top and runs the code from Set form2 = Me.Form again so whatever was selected e.g. 2nd or 3rd option goes back to the top selection in the datasheet so in the example if I choose C Technology Fees Aff then it defaults back to C Technology Fees Aff.
If I run the code without it jumping to the top then it works fine and it runs Me.Parent.Controls![P&LGroupForm].Requery
So what it should show up as is:
I'd like it so that when the form initially loads it fills in the monthly totals but when I make a change by selecting a different line item then it simply does a requery of the form below but not sure what the event procedure is required.
I have tried Private Sub Form_AfterUpdate() and Private Sub Form_Click() but neither of those fire when I choose a different datasheet selection - is there a VBA subroutine I can use other than Private Sub Form_Current()
I've spent quite a bit of time on it and quite get it to do what I need. Pls ask if need any clarification.
Thanks
Paul