Filter or reload recordset?

[BruceM]

I have a Purchase Order database of standard design (Main PO table with a
related LineItems table; user interface form/subform).

Most of the time users will want to see a limited recordset when the PO form
loads. For instance, it may be that they want to see only POs for which
approvals are not yet complete, or POs after a certain date. It is not
clear yet exactly what recordset will be presented when the PO form loads,
but it will certainly not be all records in the PO table. However, there
will come times when users such as the financial people will want to see all
of the records, so the recordset needs to have all records available.

I suppose I could use a parameter query, but I would have to load the
recordset with the default parameters, then pass parameters from the Search
part of the form (unbound text boxes for selecting a single vendor's orders,
a date range, etc.) as needed. If I am to go that route I suspect it would
be better to redefine the recordset as a string (strSQL), then use
Me.Recordset = strSQL.

This brings me to a question. I hope I can ask it clearly. I believe that
any time I pass new parameters to a query it has the effect of reloading the
recordset. If so, building a recordset string strikes me as more efficient
way to go if the approach is to load a new recordset.

The other approach is to filter. Again, I would use the Seach text boxes to
build a filter string, then apply the string. Is this higher overhead than
reloading a recordset?

To summarize, the PO form will open with a limited recordset. The PO form
can be opened from several other forms. The recordset will differ depending
on the calling form. Is there an advantage one way or the other to loading
the recordset at run time, then redefining and reloading it as needed during
the user session?

Related question: If I go the recordset route rather than the filter route
I would list all of the fields when I build the SQL string. Most of the
string will be the same in all cases. Since I would have to build the
recordset from several events I would make that string a constant in the
Declarations section of the PO form's code module, unless there is a better
approach.

 

[Allen Browne]

Access is smart enough not to fetch all records if it doesn't need to.

If you OpenForm with a WhereCondition, only the required records are read.

If you want to filter out existing records on open, just set DataEntry to
True, or do this:
Private Sub Form_Open(Cancel as Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

If you don't want users to remove the filter easily, assign the
RecordSource.

 

[BruceM]

Thanks for the reply.

I don't want Data Entry. The most likely scenario over time is that older
records will be omitted from the recordset that loads, but in all cases
users will need the ability to see other records. For one thing, the same
Purchase Order may be generated again and again, so I adapted your code to
duplicate the main form record and the related subform records, except with
the current date. Works wonderfully.

When I open the PO form from the switchboard (my own, not the one built with
the Switchboard Manager) I want to apply the default restriction. When I
open the PO form from a Vendors form I want to limit the displayed POs to
just ones for that vendor. There may be other restrictions or limits once
things are up and running, so I want to keep as much flexibility as I can.

Users will need full ability to narrow or broaden the recordset, so from
what I understand a Where condition combined with filtering on demand is a
good approach. I had already started down that road, but I thought it would
be a good idea to ask before I went further.

 

[Klatuu]

If you want to open the form from different events on you "switchboard", you
can use the Where argument of the OpenForm method. I'm sure you know how
that is done. What it is doing is setting the Filter and FilterOn properties
of the form, For example if you opened it with:
Docmd.OpenForm "frmPO", acNormal, , "[vendorID] = " & Me.cboVendor

Then look at the form's Filter property you would see something like:
[vendorID] = 397

So, once open, you can change the records being presented by manipulating
the Filter and FilterOn properties of the form.

I have encluded this code as an example for you. It is from one of my apps
where the user has several combo boxes with which they can filter the form.
Each of the combos has in its AfterUpate event =SetIntitFilters()

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

 

[BruceM]

Thanks for the reply. I didn't know that about the Filter and FilterOn
properties being set by the Where argument of OpenForm.

The code you included is similar to what I am doing. I call my function in
the After Update event of each Search text box, but I may end up using a
command button after selecting all the parameters if I see a need to do it
that way. The result is the same in either case.

The project has been evolving as I proceed. For instance, after some
testing the financial person asked for some changes. Also, I have seen in
some cases that the initial appraoch was not the best one. I think I got
myself into some difficulty by passing OpenArgs to the PO form's Load event,
and filtering there rather than using the Where argument of OpenForm. I
think I can use as a general rule from now on that if the recordset is to be
limited by default (as I am doing) it is best to do so at the earliest
opportunity.

I expect AddAnd is a function to append " AND " to each string before adding
the next part. I have been adding " AND " to each string, then stripping it
off the end of the final string. I doubt it makes a lot of difference, but
AddAnd makes it easier to read the code, I think. I'll give that a try the
next time I build a string of this sort.

If you want to open the form from different events on you "switchboard",
you
can use the Where argument of the OpenForm method. I'm sure you know how
that is done. What it is doing is setting the Filter and FilterOn
properties
of the form, For example if you opened it with:
Docmd.OpenForm "frmPO", acNormal, , "[vendorID] = " & Me.cboVendor

Then look at the form's Filter property you would see something like:
[vendorID] = 397

So, once open, you can change the records being presented by manipulating
the Filter and FilterOn properties of the form.

I have encluded this code as an example for you. It is from one of my
apps
where the user has several combo boxes with which they can filter the
form.
Each of the combos has in its AfterUpate event =SetIntitFilters()

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Thanks for the reply.

I don't want Data Entry. The most likely scenario over time is that
older
records will be omitted from the recordset that loads, but in all cases
users will need the ability to see other records. For one thing, the
same
Purchase Order may be generated again and again, so I adapted your code
to
duplicate the main form record and the related subform records, except
with
the current date. Works wonderfully.

When I open the PO form from the switchboard (my own, not the one built
with
the Switchboard Manager) I want to apply the default restriction. When I
open the PO form from a Vendors form I want to limit the displayed POs to
just ones for that vendor. There may be other restrictions or limits
once
things are up and running, so I want to keep as much flexibility as I
can.

Users will need full ability to narrow or broaden the recordset, so from
what I understand a Where condition combined with filtering on demand is
a
good approach. I had already started down that road, but I thought it
would
be a good idea to ask before I went further.