Filter form for minimum network traffic

[tuesamlarry]

I have a main form with a text box in Form Header which AfterUpdate finds the
record and displays the main form with correct record and all its subforms
for editing and adding. When I first open the main form there is data showing
so I assume the form has pulled all records from the back-end and is now
waiting for me to filter it. I would like to reduce network traffic but don't
know where and how to turn FilterOn on and off. I have placed
Me.Filter = "(False)"
Me.FilterOn = True
in the form's OnOpen event.
In the form header text box which I enter the record ID I want, i placed
Me.Filter = Me.utbFindClientID
Me.FilterOn = True
in the AfterUpdate event.
When I open the form and enter a valid ID, I get error 3420 Object invalid
or no longer set.
I would like some advice on where/how to filter form so as not to pull more
data than necessary over the network in a multi-user situation using a FE and
BE.
Here is the code that the Form's header text box uses

Dim rs As DAO.Recordset
If Not IsNull(Me.utbFindClientID) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[clntClientID] = """ & Me.utbFindClientID & """"

Me.Filter = Me.utbFindClientID
Me.FilterOn = True



If rs.NoMatch Then
Call MsgBox("Client ID Does Not Exist" _
& vbCrLf & "Check the numbers and try again" _
, vbQuestion, Application.Name)

Else

Me.Bookmark = rs.Bookmark

Select Case MsgBox("Is this the Client you want?" _
& vbCrLf & "Check the Family names
below." _
& vbCrLf & "Press Yes to open this file." _
& vbCrLf & "Press No to enter a new
Client ID" _
, vbYesNo Or vbQuestion Or
vbDefaultButton1, "Client File")

Case vbYes
Me.pageServicesProvided.SetFocus
Me.sfrmServiceProvided.SetFocus
DoCmd.GoToRecord , , acNewRec


Case vbNo
DoCmd.GoToControl ("utbFindClientID")

End Select

End If

Set rs = Nothing

End If



Thank you for your advice in advance.

 

[Allen Browne]

The "Invalid object" error could have many causes. In same cases, a compact,
decompile, compact sequence fixes it.

It could also be a bug in Access. If the form has no records (since you
filtered it that way), and no new record can be added (because
AllowAdditions is No or the source query is read-only), the Detail section
of the form goes completely blank. Controls in the form header/footer still
display, but don't work properly. There's a demonstration of this issue
here:
http://allenbrowne.com/bug-06.html

The approach you have (setting the filter) is fine. Another is to set the
form's RecordSource so it loads no records, e.g.:
SELECT tblClient.* FROM tblClient WHERE (False);
and then change the RecordSource to filter it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a main form with a text box in Form Header which AfterUpdate finds
the
record and displays the main form with correct record and all its subforms
for editing and adding. When I first open the main form there is data
showing
so I assume the form has pulled all records from the back-end and is now
waiting for me to filter it. I would like to reduce network traffic but
don't
know where and how to turn FilterOn on and off. I have placed
Me.Filter = "(False)"
Me.FilterOn = True
in the form's OnOpen event.
In the form header text box which I enter the record ID I want, i placed
Me.Filter = Me.utbFindClientID
Me.FilterOn = True
in the AfterUpdate event.
When I open the form and enter a valid ID, I get error 3420 Object invalid
or no longer set.
I would like some advice on where/how to filter form so as not to pull
more
data than necessary over the network in a multi-user situation using a FE
and
BE.
Here is the code that the Form's header text box uses

Dim rs As DAO.Recordset
If Not IsNull(Me.utbFindClientID) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[clntClientID] = """ & Me.utbFindClientID & """"

Me.Filter = Me.utbFindClientID
Me.FilterOn = True



If rs.NoMatch Then
Call MsgBox("Client ID Does Not Exist" _
& vbCrLf & "Check the numbers and try again" _
, vbQuestion, Application.Name)

Else

Me.Bookmark = rs.Bookmark

Select Case MsgBox("Is this the Client you want?" _
& vbCrLf & "Check the Family names
below." _
& vbCrLf & "Press Yes to open this
file." _
& vbCrLf & "Press No to enter a new
Client ID" _
, vbYesNo Or vbQuestion Or
vbDefaultButton1, "Client File")

Case vbYes
Me.pageServicesProvided.SetFocus
Me.sfrmServiceProvided.SetFocus
DoCmd.GoToRecord , , acNewRec


Case vbNo
DoCmd.GoToControl ("utbFindClientID")

End Select

End If

Set rs = Nothing

End If



Thank you for your advice in advance.

 

[tuesamlarry]

Allen,
Thank you. I did away with the DAO stuff and substituted a modified version
of code from your Search Criteria article. This is what I put in the
AfterUpdate event of the txt box in the header of the form.

Dim strWhere As String
If Not IsNull(Me.utbFindClientID) Then
strWhere = strWhere & "([clntClientID] = """ & Me.utbFindClientID & """)"
Me.Filter = strWhere
Me.FilterOn = True
End If

And in the OnOpen event of the form I put:

Me.Filter = "(False)"
Me.FilterOn = False

Works very nice. When I first open the form, no data is showing, until I
enter a valid Client ID. Is this really all I need to pull a record over the
network in a multi-user FE/BE situation without it pulling in the entire
table? Doesn't get upset if I put in a nonexistent ID. Cursor simply waits
until I put in an ID it can locate. I am still developing this in a single
environment and haven't split yet.


Larry
Access 2007

 

[Allen Browne]

Yep: that's fine.

A non-existent ID value is a valid query: it just doesn't match any records,
but it's not an error.

You can't always control exactly how JET pulls the data through, but it's
quite intelligent.

 

[tuesamlarry]

Allen,
Thanks a million! I have brought tears to the eyes of folks who have labored
for years with a spreadsheet, thanks in large part to your many VBA coding
examples I have been able to incorporate into this database.

Larry

Yep: that's fine.

A non-existent ID value is a valid query: it just doesn't match any records,
but it's not an error.

You can't always control exactly how JET pulls the data through, but it's
quite intelligent.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thank you. I did away with the DAO stuff and substituted a modified
version
of code from your Search Criteria article. This is what I put in the
AfterUpdate event of the txt box in the header of the form.

Dim strWhere As String
If Not IsNull(Me.utbFindClientID) Then
strWhere = strWhere & "([clntClientID] = """ & Me.utbFindClientID &
""")"
Me.Filter = strWhere
Me.FilterOn = True
End If

And in the OnOpen event of the form I put:

Me.Filter = "(False)"
Me.FilterOn = False

Works very nice. When I first open the form, no data is showing, until I
enter a valid Client ID. Is this really all I need to pull a record over
the
network in a multi-user FE/BE situation without it pulling in the entire
table? Doesn't get upset if I put in a nonexistent ID. Cursor simply waits
until I put in an ID it can locate. I am still developing this in a single
environment and haven't split yet.


Larry
Access 2007