Display Record Count from Subform in Tab (1 Viewer)

rfaircloth

Registered User
Joined
Jun 26, 2014
Messages
15
I have a form that contains a number of tabs, each tab then contains a subform. The basic gist is that there are clients and each client chart needs to be audited to make sure that the every clients chart has all of the correct information in it. So if for example, each client needs an initial treatment plan, there would be a tab called Initial Treatment Plan that would contain a subform (continuous form) displaying all of the clients that are missing this information. I would like to display the number of records that are being displayed in each subform in the tab next to the name to make it easier for the auditor to know how many which tabs have content to be updated. For example, if there are 10 clients that are missing their initial treatment plan, the tab would read "Initial Treatment Plan (10)".

I was able to get a total number of rows in datasheet view, but I don't know if there is a way to have that field as a hidden field in continuous form view that can have its value displayed in the tab. Any help would be greatly appreciated!
 

rfaircloth

Registered User
Joined
Jun 26, 2014
Messages
15
I figured out how to get a hidden field on the continuous form that contains the row count. I created a textbox and set its Control Source to "=Count(*)" and it counts the number of records displayed. Now I'm trying to figure out how to put that number in the tab name.
 

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,536
This may be of some help.
 

rfaircloth

Registered User
Joined
Jun 26, 2014
Messages
15
No go, I am able to name the tab with plan text using the property sheet, but I need to concatenate the field to something like this: "Initial Treatment Plan (" & intFieldValue & ")". I think that it is going to have to be done in VBA code on the back end. I'm thinking something like:

Me.IntTreatmentPlan.Caption = Me.Subform.Textfield.Value

I have used VB script for web programming, but I'm not that familiar with VBA and how to reference things properly, especially since the data that I need has to come from a subform. It looks like I can reference items from the main form when but not from within a subform.
 

vbaInet

AWF VIP
Joined
Jan 22, 2010
Messages
26,374
Place a hidden textbox on the header or footer section of your form and enter =Count(*) in it. Use this value to add to the caption of the page.
Code:
Me.pageName.Caption = "Initial Treatment Plan (" & Me.txtCount & ")"
You will obviously need to update this count up-to-date so think about including the code in relevant events that will affect the number.
 

rfaircloth

Registered User
Joined
Jun 26, 2014
Messages
15
I can't get this to work. My forms and subforms don't seem to be able to see each other's Controls. When I enter the code into the main form it only sees the Tab control and not the hidden field. When I enter the code in the subform it only sees the hidden field and not the Tab control.
 

vbaInet

AWF VIP
Joined
Jan 22, 2010
Messages
26,374
The textbox should be placed in each subform. Your subform control has a name so you should use it to reference the hidden textbox in this format:
Code:
Me.NameOfPage.Caption = "Initial Treatment Plan" & Me.SubformControlName.txtHidden & ")"
And don't try to reference the tab control, you don't need. Reference the page directly.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom