UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Changing Subform Label.caption According To Subforms Record Count, Access 2016    
 
   
multicoat
post Apr 9 2020, 04:48 AM
Post#1



Posts: 158
Joined: 23-August 07
From: Melbin, Austrialia


Hello
Been a long time and its good to see you are all here still. kisses.gif

I have a Main form for the Employees of a business

On the Main form there is Subform1 which lists the Employment Agreements that applied through the course of their employment. Eg a factory worker will start on the Factory Workers Agreement, then they might be "promoted" to sales so then the Sales Workers Agreement applies, then later down the track they maybe promoted to management so then the Management Workers Agreement applies. (Each employee can have many Agreements)

Subform1 is linked to the Main form by the usual Master/Child connection.

On the Main form there is also Subform2 which lists the Skill/Responsibility Levels that the employee had during each Agreement. Eg a factory worker under the Factory Workers Agreement starts at Fact Level1, then after a bit of experience goes up to Fact Level2, then Factory Supervisor and so on. When they move to Sales, they start at SalesLevel1, then SalesLevel2 etc (Each Agreement can have many Skill/Responsibility Levels)

Subform2 is linked Master/Child to Subform1 via a calculated control on the Main form so that as the user moves through the list of Agreements, subform2 automatically updates the list of Skill/Responsibility Levels that applied to the employee at various times through employment.

I have designed it this way because we want to see lists of data

I want to change the caption above Subform2 depending on the quantity of records it displays. If there are zero records I want to use the caption to tell the user to click a button, if there are records I want to use the caption to tell the user what to do with the information.

I tried using a sub firing from the OnCurrent Event in Subform2.form to change the subform2 control's caption but OnCurrent does not fire if there are no records in the subform. The OnApplyFilter event cant be used because it fires before the Subform2 data is updated.

So then I set the sub to fire from Subform1 OnCurrent (and even tried events in the Main form) but the sub to change Subform2 caption runs before before the Subform2 data has been updated so it uses the wrong record count from Subform2 and hence the wrong caption can be displayed.

It seems I need an event that is WhenSubformDataHasFinishedLoading

I know I can do what I want by delinking the standard Master/Child connection and control record sources and captions with subs but, for simplicity, I would like to do this the old fashioned way.

Experience has taught me that I am missing something.

Thanks in advance


--------------------
Chris Sweetnam
Go to the top of the page
 
DanielPineault
post Apr 9 2020, 04:59 AM
Post#2


UtterAccess VIP
Posts: 7,336
Joined: 30-June 11



You may find https://www.devhut.net/2016/08/09/display-t...on-a-main-form/ helpful.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
multicoat
post Apr 11 2020, 07:52 PM
Post#3



Posts: 158
Joined: 23-August 07
From: Melbin, Austrialia


Thanks Daniel

I think I must be missing something as your count methods are very familiar to me.

Approach 1 does not work if a query has no results

Approach 2 does not work if form AllowAdditions property = false (Which is my case)

Approach 3 is the one I have been using but it accepted in a calculated control and when using in VB, the value is calculated before the new subform records have been loaded so the value counted is the previous quantity of records not the current set.

iconfused.gif

--------------------
Chris Sweetnam
Go to the top of the page
 
tina t
post Apr 11 2020, 08:04 PM
Post#4



Posts: 6,588
Joined: 11-November 10
From: SoCal, USA


QUOTE
set the sub to fire from Subform1 OnCurrent

i don't know what you're using to count the records in Subform2, but you should be able to use the Current event of Subform1, and use the DCount() function to count the targeted records at the table level, the table used as RecordSource of Subform2, rather than at the form level. that way it won't matter if the records in Subform2 have loaded yet or not, and DCount() should return a zero if no targeted records are found in the table.

hth
tina
This post has been edited by tina t: Apr 11 2020, 08:05 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
multicoat
post Apr 11 2020, 08:56 PM
Post#5



Posts: 158
Joined: 23-August 07
From: Melbin, Austrialia


Thanks Tina
Going with that solution

--------------------
Chris Sweetnam
Go to the top of the page
 
multicoat
post Apr 11 2020, 08:56 PM
Post#6



Posts: 158
Joined: 23-August 07
From: Melbin, Austrialia


Edit
Approach 3 is the one I have been using but it NOT accepted in a calculated control and when using in VB, the value is calculated before the new subform records have been loaded so the value counted is the previous quantity of records not the current set.

--------------------
Chris Sweetnam
Go to the top of the page
 
projecttoday
post Apr 12 2020, 02:30 AM
Post#7


UtterAccess VIP
Posts: 12,168
Joined: 10-February 04
From: South Charleston, WV


I'm surprised that the AllowAdditions property would interfere with the RecordCount property. Are you sure on this?

--------------------
Robert Crouser
Go to the top of the page
 
tina t
post Apr 12 2020, 10:05 AM
Post#8



Posts: 6,588
Joined: 11-November 10
From: SoCal, USA


you're welcome, Chris, we're all glad to help. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 06:46 PM