Problem with Requery not working

[Pete Beatty]

I have a large table (Roster) that contains names of people and their
personal information. Within the table is a variable( 'District' ) that
groups the individuals in to functional areas. each area has an individual
that provides updates (restricted that group only), and there is a master
operator that has access to all groups.

I have a master form that contains a drop down box and a sub form. If a
group is selected in the drop down, the sub form is supposed to refresh with
that groups data,

here are some specifics:

the Roster table contains district values, they are numeric, beginning with
1 and going to 99.

each individual logs on to the system and the system identifies the group or
groups the individual is authorized to access. The master operator uses a
group number of 0 to indicate full access to all districts.

if a person can access more than one group, the drop down list is modified
to show the districts they can access.

when a selection is made, the code will create a filter and apply it to the
filter property in the sub form. PROBLEM: The sub form will not refresh.

here is the code:

master form:


Private Sub District_Combo_Change()
Me!xrefFilter.Value = BldFilterStr(Me!District_Combo.Value)
Me![Roster List subForm].Requery

End Sub


Function BldFilterStr(nDistrict As Integer)
Dim strSQL As String
If IsNull(nDistrict) Then
nDistrict = 0
End If
If nDistrict = 0 Then
strSQL = "cndNumber is null "
strSQL = strSQL + " ORDER BY Lastname"
Else
strSQL = "(cndNumber is null AND district=" & nDistrict & ")"
End If
BldFilterStr = strSQL
End Function

Sub Form (Roster List subForm)

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Parent.xrefFilter.Value
Me.Refresh

End Sub

Private Sub Form_Current()
Parent.xrefRcdNumber.Value = Me!Roster_RcdNumber.Value
Parent.xrefMbrNumber = Me!Roster_MbrNumber.Value
End Sub


I do not use the Link parameters on the form because of the '0' district
value for all districts.

on the sub form:
RECORD SOURCE is set to ROSTER (the main table)
FILTER is set to "(cndNumber is null AND district=22)"
Order By is set to "LastName, FirstName"
Allow Filters is "Yes"

 

[tina]

to requery a subform object with code running in the main form's module, you
need to specify the subform's Form object, as

Private Sub District_Combo_Change()
Me!xrefFilter.Value = BldFilterStr(Me!District_Combo.Value)
Me![Roster List subForm].Form.Requery

End Sub

also, i'm not too familiar with it but, from reading in Help, i don't think
you're using the ApplyFilter event correctly. that event runs when a filter
is applied, so it's used more to check the validity of a filter, or perhaps
show/hide form controls, etc. it doesn't look like you would use that event
to apply a filter itself.

Sub Form (Roster List subForm)

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Parent.xrefFilter.Value
Me.Refresh

End Sub

so if the intent of the above code is to apply a filter within the subform,
you need to determine when you want the filter applied - OnCurrent,
AfterUpdate, etc. then put your code in the appropriate event, as

Me.Filter = Parent.xrefFilter.Value
Me.FilterOn = True

hth

I have a large table (Roster) that contains names of people and their
personal information. Within the table is a variable( 'District' ) that
groups the individuals in to functional areas. each area has an individual
that provides updates (restricted that group only), and there is a master
operator that has access to all groups.

I have a master form that contains a drop down box and a sub form. If a
group is selected in the drop down, the sub form is supposed to refresh with
that groups data,

here are some specifics:

the Roster table contains district values, they are numeric, beginning with
1 and going to 99.

each individual logs on to the system and the system identifies the group or
groups the individual is authorized to access. The master operator uses a
group number of 0 to indicate full access to all districts.

if a person can access more than one group, the drop down list is modified
to show the districts they can access.

when a selection is made, the code will create a filter and apply it to the
filter property in the sub form. PROBLEM: The sub form will not refresh.

here is the code:

master form:


Private Sub District_Combo_Change()
Me!xrefFilter.Value = BldFilterStr(Me!District_Combo.Value)
Me![Roster List subForm].Requery

End Sub


Function BldFilterStr(nDistrict As Integer)
Dim strSQL As String
If IsNull(nDistrict) Then
nDistrict = 0
End If
If nDistrict = 0 Then
strSQL = "cndNumber is null "
strSQL = strSQL + " ORDER BY Lastname"
Else
strSQL = "(cndNumber is null AND district=" & nDistrict & ")"
End If
BldFilterStr = strSQL
End Function

Sub Form (Roster List subForm)

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Parent.xrefFilter.Value
Me.Refresh

End Sub

Private Sub Form_Current()
Parent.xrefRcdNumber.Value = Me!Roster_RcdNumber.Value
Parent.xrefMbrNumber = Me!Roster_MbrNumber.Value
End Sub


I do not use the Link parameters on the form because of the '0' district
value for all districts.

on the sub form:
RECORD SOURCE is set to ROSTER (the main table)
FILTER is set to "(cndNumber is null AND district=22)"
Order By is set to "LastName, FirstName"
Allow Filters is "Yes"

 

[Pete Beatty]

Tina,
Thanks for your reply. The comment about the using the form object let to a
surprise. The FilterOn wasa set to "False", even though the FilterOn
property ws set in the prooperty view of the subform. Strange.

Once I reset the 'Filter ON' to True, everything started working again.

to requery a subform object with code running in the main form's module,
you
need to specify the subform's Form object, as

Private Sub District_Combo_Change()
Me!xrefFilter.Value = BldFilterStr(Me!District_Combo.Value)
Me![Roster List subForm].Form.Requery

End Sub

also, i'm not too familiar with it but, from reading in Help, i don't
think
you're using the ApplyFilter event correctly. that event runs when a
filter
is applied, so it's used more to check the validity of a filter, or
perhaps
show/hide form controls, etc. it doesn't look like you would use that
event
to apply a filter itself.

Sub Form (Roster List subForm)

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Parent.xrefFilter.Value
Me.Refresh

End Sub

so if the intent of the above code is to apply a filter within the
subform,
you need to determine when you want the filter applied - OnCurrent,
AfterUpdate, etc. then put your code in the appropriate event, as

Me.Filter = Parent.xrefFilter.Value
Me.FilterOn = True

hth

I have a large table (Roster) that contains names of people and their
personal information. Within the table is a variable( 'District' ) that
groups the individuals in to functional areas. each area has an individual
that provides updates (restricted that group only), and there is a master
operator that has access to all groups.

I have a master form that contains a drop down box and a sub form. If a
group is selected in the drop down, the sub form is supposed to refresh with
that groups data,

here are some specifics:

the Roster table contains district values, they are numeric, beginning with
1 and going to 99.

each individual logs on to the system and the system identifies the group or
groups the individual is authorized to access. The master operator uses
a
group number of 0 to indicate full access to all districts.

if a person can access more than one group, the drop down list is
modified
to show the districts they can access.

when a selection is made, the code will create a filter and apply it to the
filter property in the sub form. PROBLEM: The sub form will not
refresh.

here is the code:

master form:


Private Sub District_Combo_Change()
Me!xrefFilter.Value = BldFilterStr(Me!District_Combo.Value)
Me![Roster List subForm].Requery

End Sub


Function BldFilterStr(nDistrict As Integer)
Dim strSQL As String
If IsNull(nDistrict) Then
nDistrict = 0
End If
If nDistrict = 0 Then
strSQL = "cndNumber is null "
strSQL = strSQL + " ORDER BY Lastname"
Else
strSQL = "(cndNumber is null AND district=" & nDistrict & ")"
End If
BldFilterStr = strSQL
End Function

Sub Form (Roster List subForm)

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Me.Filter = Parent.xrefFilter.Value
Me.Refresh

End Sub

Private Sub Form_Current()
Parent.xrefRcdNumber.Value = Me!Roster_RcdNumber.Value
Parent.xrefMbrNumber = Me!Roster_MbrNumber.Value
End Sub


I do not use the Link parameters on the form because of the '0' district
value for all districts.

on the sub form:
RECORD SOURCE is set to ROSTER (the main table)
FILTER is set to "(cndNumber is null AND district=22)"
Order By is set to "LastName, FirstName"
Allow Filters is "Yes"