Filtering 2nd Subform from 1st subform

[spartansFC]

Hi

I've got 2 bound subforms on a unbound form, i've created a filter search form based of Allen Browns example. The 1st subform filters perfectly and i've got running subtotals. I now need to somehow filter the 2nd subform based of the criteria in subform1.

What i would like to happen is the user selects a club name and then a week commencing date, subform1 filters to show the total number of children attending the club on that particular week. I also need to show if there is enough staff cover which is where subform2 comes into it, the filter should filter subform2 so that i can run some subtotals to make sure each club has enough cover per day.

It's all a bit confusing, not sure if i have to link both subforms together, not sure how to do this. There is only 2 criteria that i need to filter by so far, they are

dteSessionsDates

SELECT tblOccupancyBuildNewPerm.dteSessionDates FROM tblOccupancyBuildNewPerm GROUP BY tblOccupancyBuildNewPerm.dteSessionDates ORDER BY tblOccupancyBuildNewPerm.dteSessionDates DESC;

clubName

SELECT tblClubs.strClubName FROM tblOccupancyBuildNewPerm INNER JOIN tblClubs ON tblOccupancyBuildNewPerm.lngClubsID=tblClubs.lngClubsID GROUP BY tblClubs.strClubName;

Each subform is created from a query and they both have the relevant info to link together.

dteSessionDates
dteRotadteSessions

lngRotaClubsID
lngClubsID

Is it possible to filter the 2nd subform?

Mikie

 

[RuralGuy]

I usually create an invisible TextBox on the MainForm that containd the value I want from the appropriate SubForm. It is then easy to reference as a filter for your 2nd SubForm. Leave the invisible TextBox Visible until you get it to work as you want.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[spartansFC]

Thanks for the reply ruralguy, i've added 2 textboxes to the mainform

SessionDatesInvisible_txtbox

=[SessionAvailability].[Form]![cmbSearchSessionDates]

InvisibleClubName_txtbox

=SessionAvailability.Form!cmbClubNameSearch

i have both text boxes looking at the correct values, whats the code i need to put in subform2 so that it filters based on these invisible text boxes.

Mikie

 

[RuralGuy]

I don't use filters much so bear with me while we experiment. I would start by putting a filter directly in the Filter property of the 2nd SubForm. Something like "ClubName = Me.Parent.InvisibleClubName_txt" and see if it filters for the ClubName as you make changes in the 1st SubForm.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[dhookom]

Is the a reason for not simply setting the Link Master/Child properties of subform2?

Duane
Hook'D on Access
MS Access MVP

 

[RuralGuy]

Good point Duane! They work well for a filter as well. SpartansFC, give them a try.


(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[spartansFC]

The problem i had with using Child and Master links is that i needed to have the 2 subforms sync/link via 2 criteria so i googled around and found that you can do that, so on subform2 i've put in:

Link child fields:

[lngRotaClubsID];[dteRotadteSessions]

link master fields:

[SessionAvailability].Form![lngClubsID];[SessionAvailability].Form![dteSessionDates]

but subform2 is blank when the form opens, subform1 shows all the data.

so my form design is:

frmSessionAvailability2Main - unbound form
SessionAvailability (subform1) - qrySessionAvailability
frmSessionAvailabilityStaff (subform2) - qrySessionAvailabilityStaff

So there are 2 combo boxes on SessionAvailability (subform1)

SELECT tblOccupancyBuildNewPerm.dteSessionDates FROM tblOccupancyBuildNewPerm GROUP BY tblOccupancyBuildNewPerm.dteSessionDates ORDER BY tblOccupancyBuildNewPerm.dteSessionDates DESC;

there is also a command button Search_cmdbutton which has alan brownes search code in, subform1 works great, everything filters correctly, just need to get subform2 to sync display properly.

So just an example of what i want to happen, the user will select a date from cmbSearchSessionDates, click Search_cmdbutton, subform 1 filters to show which children are accessing the club on that day, then subform2 should sync to show which staff are in that day to see if we have enough cover.

Mikie

 

[RuralGuy]

Try a Me.frmSessionAvailabilityStaff.FORM.Requery in the OnLoad event of the MainForm.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit

 

[spartansFC]

so i've managed to get the forms syncing, i've used the invisible text boxes and child/master links, i've used:

childlink

dteRotadteSessions;strClubName

masterlink

SessionDatesInvisible_txtbox;InvisibleClubName_txtbox

i've now realised i have to change the form design as it's not working, it has to be a crosstab query, but i still need to filter a crosstab query/form so no doubt that will be my next question.

thanks for all your help and solving my problems again

 

[RuralGuy]

Glad to hear you got things working.

(RG for short) aka Allan Bunch MS Access MVP acXP, ac07 ac10 - winXP Pro, Win7 Pro
Please respond to this forum so all may benefit