Updating a subform based on selection in another subform (1 Viewer)

cowenpa

Member
Local time
Today, 17:21
Joined
Apr 7, 2021
Messages
38
Hi there,

I have a form with a couple of subforms which are based on queries. I would ilke to swap round the bottom two subforms and have it where I click on a PL group and the other section then just shows any data filtered by the Account Number. I can do it with adding a drop down but I already have one of those and I just want to be able to click on e.g. click on 56030 (C. Direct Bonuses) and see those in the Statement Totals filtered by Account Number or 73001 (C Salaries) and see those (screenshot 1)

I'd also like to add a total e.g. total amount of salaries or bonuses depending on what is chosen.

Here (screenshot 2) you can see I have filtered it by R&D in this screenshot from the Income statement pull down and would want to filter these (again with the two bottom boxes swapped round).

I have tried to experiment with master/parent and child but haven't been able to resolve and have tried looking at websites and youtube to try and find a solution.

Thanks in advance

Paul
 

Attachments

  • Screenshot 1.jpeg
    Screenshot 1.jpeg
    123.2 KB · Views: 246
  • Screenshot 2.jpeg
    Screenshot 2.jpeg
    79.1 KB · Views: 263

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:21
Joined
May 7, 2009
Messages
19,169
you also need to add a "reset" button (above the 3rd subform) to clear any filter you made
to the 2nd subform.

when you click/dbl-click on pl-group, you just retrieve the current Recordsource of the 2nd subform and
add another "filter" to it.
 

cowenpa

Member
Local time
Today, 17:21
Joined
Apr 7, 2021
Messages
38
you also need to add a "reset" button (above the 3rd subform) to clear any filter you made
to the 2nd subform.

when you click/dbl-click on pl-group, you just retrieve the current Recordsource of the 2nd subform and
add another "filter" to it.
Pls dumb it down for me - I want to be able to click a line/row on the PL Totals Subform and it update the other subform (Statement Section subtotals).

I tried previously both using on click and on change and neither seemed to work. Would I be amending the SQL that the query is based on for the Statement Section Subtotals - how do I do it so it specifies Account Number = Selected Account number (from the specific row selected)

Thanks

Paul
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:21
Joined
Feb 19, 2002
Messages
42,971
If you want the second subform to be dependent on the first subform, use a query as the recordsource of the second form and reference the first.

Where SomeField = Forms!mainform!.subform1.form!SomeField

Then in the Current event of the first subform, requery the second:

Me.Parent!subform2.Form.Requery
 

cowenpa

Member
Local time
Today, 17:21
Joined
Apr 7, 2021
Messages
38
Hi @Pat Hartman

So am I right in saying that I amend the query so that Account Number = [Forms]![Department Overview]![CAB - P&L Totals subform].[Form]![Account Number] as in screenshot 3 (attached) where this query is the recordsource for that subform .

The requery code am I doing that as VBA code? I tried a few combinations but I'm getting syntax errror Expected End of Sentence

Private Sub Form_Current()
Me.Parent!"CAB - Statement Section Totals".Form.Requery where the subform is called "CAB - Statement Section Totals". I tried doing a reqeury with a macro but it didn't seem to let me choose that form
End Sub

or should I be requerying the query instead?

As mentioned in my intro - I'm a big rusty not having done Access in over 5 years

Thanks

Paul
 

Attachments

  • screenshot3.jpg
    screenshot3.jpg
    112.9 KB · Views: 196

cowenpa

Member
Local time
Today, 17:21
Joined
Apr 7, 2021
Messages
38
Ignore - I think I figured it out now thanks:

share.getcloudapp.com/wbuw0b14

Probably just had the syntax slightly out
am using Forms![Department Overview]![CAB - Statement Section Totals subform1].Form.Requery and it looks to be working

Thanks - I'm sure I'll be back on the forum in due course when I get stuck again

Paul
 

Users who are viewing this thread

Top Bottom