Filter content of a field in a subform based on fields in Main Form 1

[titanl]

Hi,

I have a form with a subform and would like the choice I make in the subform to base on the content of two combobox;[Sex] and [SchoolNo] in the Main form. I have already written the code which is working ok.

The problem is that if I want to change the Sex of the student I need to click again on the SchoolNo for the data in the subform to be filtered. I've been able to work around that by putting the same code on the Event procedures for Sex_AfterUpdate and the Form_Current. Like I've said it is working fine but I just wondering if there is an easier way of doing it. Please find the code below:

Private Sub SchoolNo_AfterUpdate()

Select Case Level

    Case Is = "P"
    strQ = "SELECT Events.EventID, Events.EventName, Events.Category FROM Events WHERE Events.Category Like '" & Gender & "*' And CategoryID Between 1 And 2 ORDER BY Events.Category;"
    [AthleteTake subform].Form.cmbEventID.RowSource = strQ
    Case Is = "P/S"
    strQ = "SELECT Events.EventID, Events.EventName, Events.Category FROM Events WHERE Events.Category Like '" & Gender & "*' And CategoryID Between 1 And 3 ORDER BY Events.Category;"
    [AthleteTake subform].Form.cmbEventID.RowSource = strQ
    Case Is = "S"
    strQ = "SELECT Events.EventID, Events.EventName, Events.Category FROM Events WHERE Events.Category Like '" & Gender & "*' And CategoryID Between 2 And 3 ORDER BY Events.Category;"
    [AthleteTake subform].Form.cmbEventID.RowSource = strQ
    Case Else
    strQ = "SELECT Events.EventID, Events.EventName, Events.Category FROM Events WHERE Events.Category Like '" & Gender & "*' And CategoryID Between 4 And 5 ORDER BY Events.Category;"
    [AthleteTake subform].Form.cmbEventID.RowSource = strQ

End Select

End Sub

Thank you for any help or suggestion.

titanl

 

[MajP]

When you change the recordsource of the subform it does not automatically requery the subform. You have to force it. You need to add at the end of you procedure

[AthleteTake subform].Form.requery

 

[titanl]

Hi MajP,

Thanks for the tip. I've tried it on the Form_Current() procedure and it certainly makes it work better than before. However, when I tried to change the sex of the athlete the records in the subform do not automatically refresh to display events for the corresponding sex.

My question is on which event do I have to put the procedure?

1. Form_Current()
2. Sex_AfterUpdate()
3. SchoolNo_AfterUpdate()
or
4. On all 3?

Thanks

 

[MajP]

Most likely you would have to do it on all three. It is hard to tell without seeing it. But if you only have it in the on current event then it requeries only when you change records and not when you change the combos. You do not want to write the same code twice. Have a common routine and call it from multiple places

public sub changeSubForm(maybe some parameters)
Your code here
end sub

Private sub SchoolNo_afterUpdate()
call changeSubForm
'or if you need to pass a parameter
'call changeSubForm(some value)
end sub

Private sub Sex_afterUpdate()
call changeSubForm
'or if you need to pass a parameter
'call changeSubForm(some value)
end sub

Just so you know. You are really setting me up with the Sex After Update event. I have so many good jokes, but I would get kicked off the site. Normally nothing happens in that event.

 

[titanl]

Hi again MajP!

Thanks again for the reply and suggestions. Had actually copied the code on on all three Event Procedures and it is working great. However, your suggestions do make sense (actually I've created and called routines before but it had just slip my mind - I guess it must be due to the Sex After Update event)

Hey! Nice joke by the way!!! Could not stop chuckling. You really made my day mate. Cheers and have a nice day!