Form and subform query

  • Thread starter Thread starter Saylindara
  • Start date Start date
S

Saylindara

I have a main form to show an Event (course/training etc.) and a subform to
show every job title in the organisation and whether the event is mandatory
for that job title. The link table is EventJobTitleStatus, with Status being
whether the course is mandatory or not for that particular job title. That
works fine (what a relief).

For clarity, I would like a text box on the main (Event) form which says
"Mandatory all" or "Mandatory selected staff" based on what's on the subform.
FYI the Event form is based on a query.

Is that possible?
 
Hi

There are a few methods to what you want - but I would keep it very simple.

Just add a text box to your main form to count the number of times the word
mandatory is shown in your status control on the sub form. If that equalls
the total number of records in the status field then it is Mandatory for all
- if not, then it isn't.
 
Sorry to be dim, but how do you do that?

Wayne-I-M said:
Hi

There are a few methods to what you want - but I would keep it very simple.

Just add a text box to your main form to count the number of times the word
mandatory is shown in your status control on the sub form. If that equalls
the total number of records in the status field then it is Mandatory for all
- if not, then it isn't.
Click to expand...
 
Add a control to the subform (in the footer or header)
Name it txtAllMandatory
Set its source to
= Count(*)= Count(IIF(Status="Mandatory",1,Null))
That should return True (-1) or False (0)

Now on the main form add a control
Set its source to reference the control on the subform
=IIF([Name of subform Control].[Form].[txtAllMandatory],"Mandatory
All","Mandatory Selected Staff")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Hi

Create a text box in the footer of your subform ( call it ManditoryCount)

=Sum(IIf([Status]="Manditory",1))
Make this visible = No if you want

Create a text box on your main form with this as the control

=IIf(([SubForm].[Form].[RecordsetClone].[RecordCount]-[SubForm].[Form]![ManditoryCount])>0,"Not Manditory for everyone","Manditory for everyone")

Change the subform name to what it really is
 
Thank you Wayne and John too. I'm going to have a strong cup of tea and then
I'll try both these.

Wayne-I-M said:
Hi

Create a text box in the footer of your subform ( call it ManditoryCount)

=Sum(IIf([Status]="Manditory",1))
Make this visible = No if you want

Create a text box on your main form with this as the control

=IIf(([SubForm].[Form].[RecordsetClone].[RecordCount]-[SubForm].[Form]![ManditoryCount])>0,"Not Manditory for everyone","Manditory for everyone")

Change the subform name to what it really is

--
Wayne
Manchester, England.



Saylindara said:
Sorry to be dim, but how do you do that?
Click to expand...
Click to expand...
 
Back
Top