Can I update a query based on the tab that is selected (1 Viewer)

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
Hi Access gurus,

I have in my access database a Forecast table that has figures for forceasts for February through December. I have included the sums Feb through May below:

DeptView.jpg


I have a form with a tab for each month
monthlies.jpg

I would like it so that if the February tab is selected then it return the Data from the query with departments, Income Statements Sections and the sums for that particular month so for example if tab is February just return for Feb:
Feb.jpg

and then I'll likely have a drop down where they can select which Department they would like to look at and if for example e.g. CAB

Then if they click on 2. COS I would want another subform which displays a breakdown of the Cost of Sales lines for CAB (The below is just dummy data- the actual number of lines would be a lot higher:

1618231248222.png



I want to try and avoid having to have a load of queries and subforms. I currently have quite a lot for another form I created and I am guessing there is a way to optimize this in future as they are largely the same underlying query but with different departments so at present I have 2 queries for each department and 2 matching subforms.

Thanks in advance and if something doesn't make sense or you need further elaboration please let me know and please feel free to dumb it down or point me to a youtube article or website that explains it.

Thanks

Paul
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:27
Joined
May 7, 2009
Messages
19,169
you need to create 12 queries for each month/year.

same sql, only changes the criteria:
for january, this year:

where Month([datefield]) = 1 and Year([datefield]) = Year(Date())

dont' worry of having many (i mean many) queries.
they don't bloat your db.
i've seen as many as 200 queries in one db.
 

Ranman256

Well-known member
Local time
Today, 17:27
Joined
Apr 9, 2015
Messages
4,339
show 1 of 12 queries at tab change:

Code:
Private Sub TabCtl_Change()
subFrm.RowSource = "query.qsMonth" & tabctl.value
End Sub
 

bastanu

AWF VIP
Local time
Today, 14:27
Joined
Apr 13, 2010
Messages
1,401
You only need one query, change the Page Index values for the 12 monthly pages of the tab control to correspond to theirs respective month(1 through 12) then use that in your query (Select * from tblYourTable where Month([YourDateField])=Forms!frmYourMainForm!tabCtl.Value). And you only need one subform as well, leave the 12 subform controls on the tab unbound and in the change event of the tab bind the subform to the current page and release all the others. I use a couple of public functions to do that:

Code:
Public Sub vcShowTabSubforms(strForm As String, strTabControl As String, lngPage As Long)
On Error GoTo vcShowTabSubforms_Error

Dim ctrl As Control

For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages.Item(lngPage).Controls
    If ctrl.ControlType = acSubform Then
        'ctrl.SourceObject = DLookup("[SourceObject]", "[tmpRecordSourceLookup]", "[FormName]= '" & strForm & "' AND [SubformName]= '" & ctrl.Name & "'")
    ctrl.SourceObject="sfrmYourSubform"
End If
Next

vcShowTabSubforms_Exit:
Exit Sub
vcShowTabSubforms_Error:
Application.Echo True
End Sub


Public Sub vcHideTabSubforms(strForm As String, strTabControl As String)
On Error GoTo vcHideTabSubforms_Error

Dim ctrl As Control, pg As Page

For Each pg In Forms(strForm).Form.Controls(strTabControl).Pages
    For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages(pg.Name).Controls
        If ctrl.ControlType = acSubform Then
            ctrl.SourceObject = ""
        End If
    Next
Next pg

vcHideTabSubforms_Exit:
Exit Sub
vcHideTabSubforms_Error:
Application.Echo True
End Sub
In the open event of the main form I call the vcHideTabSubforms Call vcHideTabSubforms(Me.Name, "NameOfTabControl")
and in the Change event of the tab I bind the current subform control:
Code:
Private Sub StudentInfoSub_Change()
Application.Echo False
    Call vcShowTabSubforms(Me.Name, "NameofTabCOntrol", Me.NameOfTabControl.Value)
Application.Echo True
End Sub

Cheers,
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:27
Joined
Jul 9, 2003
Messages
16,245
I made a set of YouTube videos to demonstrate Splitting a Large MS Access Datasheet in to 3 Smaller Datasheets. A natural progression to having a set of subforms displayed flatly on a main form is to have the subforms placed on a Tab control.

Seeing your question here reminded me that I haven't done that YouTube yet! In doing a YouTube as an answer to your question, and also a continuation of my series of videos, made it worth doing.

This Video is not about dates because the gist of my Blog is about dividing up a large data sheet into smaller sections, but the general principles should apply to your date/months problem. Although, by the look of it you've had some very good suggestions already!


This is the YouTube:-

Pagination - Nifty Access​


And this is my blog:-

Split a Large MS Access Datasheet in to 3 Smaller Datasheets​

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:27
Joined
May 7, 2009
Messages
19,169
And you only need one subform as well,
you need to implement bookmarking of some sort.

imagine you are comparing something in one tab and you are in 65 record number.
then you go to another Month's Tab to view the data (this will reset your current viewing record).
when you go back to the previous tab, you are tossed to the first record?
 

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
Thanks for all the comments - I will digest them all (burp) and have a go and no doubt get stuck and be back with some screenshots or a video.

Should be fun!

Paul
 

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
can you elaborate @arnelgp on bookmarking (or anybody else in the know) - I understand what you mean that ideally when you check back on another tab you want it to display what you last displayed there but not sure where I would begin technically to implement that.

Thanks

Paul
 

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
I made a set of YouTube videos to demonstrate Splitting a Large MS Access Datasheet in to 3 Smaller Datasheets. A natural progression to having a set of subforms displayed flatly on a main form is to have the subforms placed on a Tab control.
Thanks for the video - when I'm creating a subform within the tab control it is creating it as a child and in properties it does not seem to have a record source. In your videos you already had the subforms there then you created the tabs after and cut and paste. I tried creating a subform based on the query itself and cut and paste that but that also doesn't seem to have the record source either - just source object. I have tried setting the Source Object of the child to the Query and updating that in the code - I can get it to update the query and display that but not on the page itself

I think the bit I am getting wrong is the requery most likely


Code:
Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef
Set qdf = db.QueryDefs("Forecast Query")
    Select Case TabControl.Value
    Case 1
        qdf.SQL = "SELECT Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Feb FROM Forecast GROUP BY Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Feb HAVING (((Forecast.Feb)>0));"
    Case 2
        qdf.SQL = "SELECT Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar FROM Forecast GROUP BY Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar HAVING (((Forecast.Mar)>0));"


If I do
Code:
DoCmd.OpenQuery "Forecast Query"
DoCmd.Requery

Then the query loads up and shows correct info e.g. Feb for Case 1 and Mar for Case 2 but the info in the child container is still showing Feb data say if I click to March. So seems very close but no cigar.

Thanks

Paul
 
Last edited by a moderator:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:27
Joined
Jul 9, 2003
Messages
16,245
qdf.SQL = "SELECT Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar FROM Forecast GROUP BY Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar HAVING (((Forecast.Mar)>0));"

There's no need to to create the query with querydefs, just put the SQL Statement straight into the forms record source in the manner demonstrated in the video...

You can download a copy of the code I use in my video from here:- https://gum.co/pagin_1a

Use the the coupon code:- pbq7hml to get a free copy...
 
Last edited:

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
Why won't it let you share videos from cloudapp - very frustrating. Let's you quickly and easily record video or screenshots and share? Do I have to upload to Youtube or Google Drive or something?

Spent about another 90 mins watching videos on niftyaccess and tried to do it - the code runs and sets recordsource but the display doesn't show the right month - shows Feb with ? in the data - the subform has a record source of February and displays data.

The other tab is based on the query and code runs to update the code but I need to know how to requery it on the page - very frustrating that can't share video easily.

Paul
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 21:27
Joined
Jul 9, 2003
Messages
16,245
Why won't it let you share videos from cloudapp - very frustrating. Let's you quickly and easily record video or screenshots and share?

I use screencast-o-matic,


it uploads the videos directly to YouTube or to your Google Drive or you can save them locally. It also does screenshots, and gives you a URL to the image. You can also take screenshots out of your videos and use them.

There's a free version of screenomatic that allows you to record up to 15 minutes I believe, but the paid for version which allows for unlimited recording is only about $20 for a year anyway, so I always use the paid for version.
 
Last edited:

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
ok I have figured out what the problem is - the subform I had had a field for February but not one for March, April, May - I have added those in now and when I look at the monthly view I can see the data in the column e.g. if I run the March code I see the March data and if I run the April code I see the April data - but I don't really want to display the fields that I'm not interested in so if looking at March I don't want to have February and April showing - at present those fields show #Name? as the record source is an SQL query that is trying to pull the March data for example

see screenshot
2021-04-14_02-49-29.jpg

IF I can post google drive links I can do that - I will see if cloudapp lets me upload straight to drive or youtube - possibly - no idea why doesn't just let me post a cloudapp link though. For images I am taking a screenshot and saving locally then inserting - usually I'd just use cloudapp which takes a screenshot uploads it and shares the link.

Paul
 

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
I tried hiding in the code e.g. with Forms!subformForecast!Feb.ColumnHidden = -1 and now I don't see February in the datasheet view of the subform called subformForecast but it still displays February in the subform in my tab control (called subfrmForecastApr) - I can't see a hide option or a datasheet view for that form - just Form View, Layout View and Design View and it won't let me do Forms!subfrmForecastApr!Feb.ColumnHidden).

Not really sure how to progress - sorry if being thick.

Also is there a way to have it so that it keeps it's currency e.g. £ and 2 decimal places - I added it to the field in the query and then went to SQL but doesn't seem to carry across.

Thanks

Paul
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:27
Joined
May 21, 2018
Messages
8,463
See demo
 

Attachments

  • HideColumn2.accdb
    1.9 MB · Views: 507

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:27
Joined
May 21, 2018
Messages
8,463
Here is the code

1. First hide all the month columns. To make this easier tag them with something. I used the word "Hide"
2. Loop the controls on your subform and hide the ones tagged "Hide"
3. Get the value of the selected tab. The value starts at 0 so I just added 1. The value of the tab control is the selected tab number.
4. Show that column
the property of the control is ColumnHidden which determines in datasheet view to hide or not.
Code:
Private Sub Form_Load()
HideColumns
ShowColumn
End Sub

Private Sub tabMonth_Change()
HideColumns
ShowColumn
End Sub
Public Sub ShowColumn()
Select Case tabMonth + 1 ' I added 1 just to synch the month no with tab
  Case 1 ' jan
    Me.subMonth.Form.fjan.ColumnHidden = False
  Case 2
    Me.subMonth.Form.ffeb.ColumnHidden = False
  Case 3
    Me.subMonth.Form.fmar.ColumnHidden = False
  Case 4
    Me.subMonth.Form.fApr.ColumnHidden = False
  End Select
End Sub
Public Sub HideColumns()
Dim frm As Access.Form
Dim ctrl As Access.Control
Set frm = Me.subMonth.Form
frm.Id.SetFocus
For Each ctrl In frm
   If ctrl.Tag = "Hide" Then ctrl.ColumnHidden = True
Next ctrl
End Sub
FYI. I only have a single subform and it is not even on the tab control. I shrunk the tab control down to just a line and put it over top of the subform. You can also use an option group instead which may give you other options.

Here is an image doing it with an option group. You can do stuff like pick the option control (checks, radio buttons, toggles, etc). You can set the pressed colors. The code is exactly the same because an option returns values 1,2,3,... also.
option.png
 
Last edited:

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
I attempted hiding fields like this:

But
Here is the code

1. First hide all the month columns. To make this easier tag them with something. I used the word "Hide"
2. Loop the controls on your subform and hide the ones tagged "Hide"
3. Get the value of the selected tab. The value starts at 0 so I just added 1. The value of the tab control is the selected tab number.
4. Show that column
the property of the control is ColumnHidden which determines in datasheet view to hide or not.
Code:
Private Sub Form_Load()
HideColumns
ShowColumn
End Sub

Private Sub tabMonth_Change()
HideColumns
ShowColumn
End Sub
Public Sub ShowColumn()
Select Case tabMonth + 1 ' I added 1 just to synch the month no with tab
  Case 1 ' jan
    Me.subMonth.Form.fjan.ColumnHidden = False
  Case 2
    Me.subMonth.Form.ffeb.ColumnHidden = False
  Case 3
    Me.subMonth.Form.fmar.ColumnHidden = False
  Case 4
    Me.subMonth.Form.fApr.ColumnHidden = False
  End Select
End Sub
Public Sub HideColumns()
Dim frm As Access.Form
Dim ctrl As Access.Control
Set frm = Me.subMonth.Form
frm.Id.SetFocus
For Each ctrl In frm
   If ctrl.Tag = "Hide" Then ctrl.ColumnHidden = True
Next ctrl
End Sub
FYI. I only have a single subform and it is not even on the tab control. I shrunk the tab control down to just a line and put it over top of the subform. You can also use an option group instead which may give you other options.

Here is an image doing it with an option group. You can do stuff like pick the option control (checks, radio buttons, toggles, etc). You can set the pressed colors. The code is exactly the same because an option returns values 1,2,3,... also.
View attachment 90810
Thanks that's working for me but less efficiently as each of my child forms has a different name whereas you have done it with just one subform named the same - not exactly sure how you did that and had it show for each tab as it complained if I had two child forms named the same.

To get round that I have done this:


Case 1
Set frm = Me.subfrmForecastFeb.Form
For Each ctrl In frm
If ctrl.Tag = "Hide" Then ctrl.ColumnHidden = True
Next ctrl
subfrmForecastFeb.Form.RecordSource = "SELECT Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Feb FROM Forecast GROUP BY Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Feb HAVING (((Forecast.Feb)>0));"
Me.subfrmForecastFeb.Form.Feb.ColumnHidden = False
Case 2
Set frm = Me.subFrmForecastMar.Form
For Each ctrl In frm
If ctrl.Tag = "Hide" Then ctrl.ColumnHidden = True
Next ctrl
subFrmForecastMar.Form.RecordSource = "SELECT Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar FROM Forecast GROUP BY Forecast.[Dep], Forecast.[Income statement section], Forecast.[FA & GL], Forecast.[Description/Comment], Forecast.[P&L Group], Forecast.Mar HAVING (((Forecast.Mar)>0));"
Me.subFrmForecastMar.Form.Mar.ColumnHidden = False


Does the job but not as efficiently: - if can help me use just the one form for all tabs then I can do it with the code you provided. Thanks

Thanks

Paul

2021-04-14_04-32-46.jpg
Is there an easy way for me to make the totals column (in this case Mar) to have currency format £ and 2 decimal places? It shows up ok in query view but not in the actual form when I use the SQL - probably something simple

Paul
 

cowenpa

Member
Local time
Today, 21:27
Joined
Apr 7, 2021
Messages
38
Hi Paul (cowenpa) did you look at my post #4?
Yes I did but I got a bit lost following it - any chance you could share a sample perhaps with 3 tabs so I can see what you mean. At the moment I'm working on MVP with the database but I would like to optimise it as I am sure I have lots of redundant subforms and queries at the moment e.g. I have tabs with 3 subforms and repeated for each department (about 6) so that's 18 subforms and about 6 queries - I think it can be done with 3 subforms and 2 queries total

The bit I'm not sure about is creating the subform so that it is linked to each tab - do I just create one subform under January for example - how do I ensure it is unbound and how do I make sure it displays under each month? or maybe a video would be beneficial.

I just want to add this has been a great responsive forum - thanks for all the help - it's great when you are a bit stuck to get answers and not have to wait days/weeks like on some forums. I will get better and apply what I learn in one form to others.. so required help should be less and less. I am on reduced hours as I'm a dialysis patient so I only do about 9 hours a week (3 days a week) instead of 24 - I was working on this in my own time late last night for about 3 hours.

Cheers

Paul
 

Users who are viewing this thread

Top Bottom