Filter a query from VB before form loads for speed.

[Kezia Asare]

I write in reply to your email of todays date, receipt of which is
acknowledged

Hi All

I have a base query that jons together all the data in my Access DB
for each member of staff. This results in a large dataset that I can
then pick out the data I want for a particular staff member.

When I display my "staff details" form I want to be able to filter
this base query to speed up its generation. How can I use VB to set
the filter for a query dynamically? I can only see how to do this on a
form:

With Form_sfrmStaffJobsStatusDisplay
.FilterOn = True
.Filter = "[Pay Number]='3042' AND [Depot]='WI'"
End With

Any ideas/ Thanks in advance,
Chrisso

 

[Chrisso]

Hi All

I have a base query that jons together all the data in my Access DB
for each member of staff. This results in a large dataset that I can
then pick out the data I want for a particular staff member.

When I display my "staff details" form I want to be able to filter
this base query to speed up its generation. How can I use VB to set
the filter for a query dynamically? I can only see how to do this on a
form:

With Form_sfrmStaffJobsStatusDisplay
.FilterOn = True
.Filter = "[Pay Number]='3042' AND [Depot]='WI'"
End With

Any ideas/ Thanks in advance,
Chrisso

 

[Allen Browne]

Typically you set the Filter before the FilterOn, i.e. swap your lines
around.

If Pay Number is a Number field (not a Text field, omit the quotes around
3042.

If the target form is not already open, you could open it filtered like
this:
Dim strWhere As String
strWhere"([Pay Number]='3042') AND ([Depot]='WI')"
DoCmd.OpenForm "Form2", WhereCondition:=strWhere

If you don't need the user to remove the filter, you could even set its
RecordSource rather than its Filter, e.g.:
Private Sub Form_Open(Cancel As Integer)
Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 WHERE "
Const strcTail = "ORDER BY Field12;"
strWhere"([Pay Number]='3042') AND ([Depot]='WI')"
Me.RecordSource = strcStub & strWhere & strcTail
End Sub