Confused about VBA operation and looking for a solution (1 Viewer)

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
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:
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

1621990691667.png


So what it should show up as is:

1621990956551.png


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
 

Attachments

  • 1621990848519.png
    1621990848519.png
    32.9 KB · Views: 503

theDBguy

I’m here to help
Staff member
Local time
Today, 03:33
Joined
Oct 29, 2018
Messages
21,357
Hi. Responding on my phone, so I only took a quick glance at your post. If you're trying to synchronize two separate subforms, there's usually no need to use any code. You should be able to do it by simply using the Linked Master/Child Fields properties and an unbound Textbox on the main form that represents the related field from the first subform.

Sent from phone...
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
Hi. Responding on my phone, so I only took a quick glance at your post. If you're trying to synchronize two separate subforms, there's usually no need to use any code. You should be able to do it by simply using the Linked Master/Child Fields properties and an unbound Textbox on the main form that represents the related field from the first subform.

Sent from phone...
I have hidden fields on the forms for date which are turned on or off depending on which month is chosen - I'll record a video to show what is happening
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
I have recorded a video to explain the problem in more depth at My VBA Problem

The issue I have is that it runs back through the Subroutine after updating the RecordSource and as a result the cursor moves from whatever line is selected back to the top so the resultant requery is for the wrong item selected. Video is about 3 mins long.

Thanks

Paul
 

Minty

AWF VIP
Local time
Today, 10:33
Joined
Jul 26, 2013
Messages
10,353
Have a look at the bookmark function.
Basically, you have to record the ID of the record you are on, then do your requery, then reposition/find the appropriate record on the sub form.

@pbaldy has a method that might help here: http://baldyweb.com/Requery.htm
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
It is changing the record selected after a recordsource update and before the requery - would I still use a bookmark feature? As you will see from the video it will rerun teh subroutine after updateing the recordsource so not sure bookmarking will help in this instance.

Thanks

Paul
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
Hi there - I have recorded a short video to explain the problem at My issue with RecordSource

In summary when the VBA runs and the RecordSource is updated it reruns the subroutine for some reason and during that process it changes the focus from currently selected row to the top row and then when it does a requery for the subform below it is doing it for the first row rather than the selected row.

I have also posted this on VBA forum

Thanks

Paul
 

Ranman256

Well-known member
Local time
Today, 06:33
Joined
Apr 9, 2015
Messages
4,337
doing a requery will reset the cursor to 1st row.
you could get the keyID, then requery, then return to that record.
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
doing a requery will reset the cursor to 1st row.
you could get the keyID, then requery, then return to that record.
I do a requery on the top subform and the cursor does not return to top - only when I update recordsource and it re-runs the subroutine as you can see in the video. The cursor change is before the requery. I'd like it to update the recordsource then continue and not re-run the module.
 

bastanu

AWF VIP
Local time
Today, 03:33
Joined
Apr 13, 2010
Messages
1,401
Could you please post the entire code you have in the Current event of the datasheet subform and a screen shot of the main form in design view showing the two subforms that need to be synchronized? Ideally uploading a small db sample with a couple dummy records would be best.

Cheers,
 

cowenpa

Member
Local time
Today, 10:33
Joined
Apr 7, 2021
Messages
38
Could you please post the entire code you have in the Current event of the datasheet subform and a screen shot of the main form in design view showing the two subforms that need to be synchronized? Ideally uploading a small db sample with a couple dummy records would be best.

Cheers,
I show the code being run through in the video with the forms at My video of the problem - did you see it? It shows that after the recordsource update with SQL code it rerunds the module - it is at this point that the cursor goes back to the top of the middle subform. I don't have any problems with the top subform with it updating the 2nd subform -all the code does here is a requery and cursor stays on either 1. sales or 2. cos - depending on what has been selected.

If I just requery in the second subform then the 3rd updates correctly but the monthly values are wrong in the 2nd subform.

This is it shown in Design View:
1622080006184.png
 

bastanu

AWF VIP
Local time
Today, 03:33
Joined
Apr 13, 2010
Messages
1,401
Showing the code in a video does not help much.... your video cuts off the VBE window. Why do you need to change the record source of the subform? When you do that it automatically requeries it and you cannot stop that so you have to deal with it (by using the bookmark method or maybe store the unique id then use Docmd.FindRecord to go back). A while ago I uploaded a sample for you (post #22 https://www.access-programmers.co.u...sed-on-the-tab-that-is-selected.317284/page-2) which shows you how to use the tab control to get the value (month) without having to switch record sources. In that case it would be just a matter of setting up the right master child linking to the third subform.

Uploading a small sample db will help you a lot to get a working solution for this, not many people like to troubleshoot problems by watching videos (maybe I'm just old school, but I find that when you supply a sample you offer more to the helper than just a "here is my problem" cry for help as we don't have to recreate tables, add sample data, write code from stopped videos, etc.).
Cheers,
 
Last edited:

Users who are viewing this thread

Top Bottom