Maxing out a filter string?

[Salad]

I have a log file with a list of records. The log file can be
unfiltered or filtered.

I have a command button to call a data entry form from the log. At
first I was only going to present the record the user selected. Then I
thought, what the heck, give the user all of them but start out at the
record the user is currently on.

If I pass the current filter that resides on the log this causes
problems. For example, if the filter is on Code = "D", the user can't
change the Code to "E" because that would not match the filter and it
would generate an "Update or CancelUpdate Without AddNew or Edit" error
message.

I wrote the following function. On my test it creates a filter string
of 12,000+ characters and the form didn't hickup on opening. Is the max
of a form filter string the max length of a string or is there a place
it bogs down?

Sub TestFormFilter
Dim s As String
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("Table 1", dbOpenSnapshot)
Do While Not rst.EOF
s = s & rst!ID & ","
rst.MoveNext
Loop
s = "ID IN (" & s & ")"
DoCmd.OpenForm "Form1", , , s
MsgBox "Done"
End Sub

[Bob Quintal]

Salad <oi*@vinegar.co m> wrote in
news:W5******** **********@news read1.news.pas. earthlink.net:

I have a log file with a list of records. The log file can be
unfiltered or filtered.

I have a command button to call a data entry form from the
log. At first I was only going to present the record the user
selected. Then I thought, what the heck, give the user all of
them but start out at the record the user is currently on.

Consider opening the form with the primary key of the user
selected record as the openargs parameter of openform, and doing
a seek Cdbl(ID) in the data entry form's open event.

This approach would probably be much faster.

Bob Q.

If I pass the current filter that resides on the log this
causes problems. For example, if the filter is on Code = "D",
the user can't change the Code to "E" because that would not
match the filter and it would generate an "Update or
CancelUpdate Without AddNew or Edit" error message.

I wrote the following function. On my test it creates a
filter string of 12,000+ characters and the form didn't hickup
on opening. Is the max of a form filter string the max length
of a string or is there a place it bogs down?

Sub TestFormFilter
Dim s As String
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("Table 1",
dbOpenSnapshot) Do While Not rst.EOF
s = s & rst!ID & ","
rst.MoveNext
Loop
s = "ID IN (" & s & ")"
DoCmd.OpenForm "Form1", , , s
MsgBox "Done"
End Sub

[Salad]

Bob Quintal wrote:

Salad <oi*@vinegar.co m> wrote in
news:W5******** **********@news read1.news.pas. earthlink.net:

I have a log file with a list of records. The log file can be
unfiltered or filtered.

I have a command button to call a data entry form from the
log. At first I was only going to present the record the user
selected. Then I thought, what the heck, give the user all of
them but start out at the record the user is currently on.
Consider opening the form with the primary key of the user
selected record as the openargs parameter of openform, and doing
a seek Cdbl(ID) in the data entry form's open event.

This approach would probably be much faster.

Bob Q.

Hi Bob. Part of what you say is a thing I do. I open the form like this
Docmd.Openform "Form1",,,strWh ere
Set rst = Forms!Form1.For m.Recordsetclon e
rst.findfirst "ID = " & Me.ID
Set Forms!Form1.for m.bookmark = rst.bookmark... .

By passing the ID list, the filter will contain all records in the log
contained in a restrictive or non-restrictive filter.

I could set the single record's form filter to the log's filter.
However, if the log is filtered restrictively to "Code = 'C' And Amount 100" this creates a problem since I can't change the code or amount values since that would put the records outside of the filter and Access
echoes an error message if one attempts to change the code or amount
fields....at least in this instance that is what occurs.

If there is no filter on the log or a less restrictive filter is in
place, thousands of records could be passed in the filter string using
my function. I know that somewhere along the line, if I attempted to
use my function, the string length would max out the maximum permittable
size of a filter.

It's similar to attempting to stuff a SQL string over (I believe) 2048
characters into a recordsource... Access chokes. So I use a saved or
temp query for these instances.

I decided to limit the string to pass no more than 1000 ids. That way I
know the string is less than whatever limit exists and it is near
instantaneous.. .right now it displays instantaneously .

If I pass the current filter that resides on the log this
causes problems. For example, if the filter is on Code = "D",
the user can't change the Code to "E" because that would not
match the filter and it would generate an "Update or
CancelUpdat e Without AddNew or Edit" error message.

I wrote the following function. On my test it creates a
filter string of 12,000+ characters and the form didn't hickup
on opening. Is the max of a form filter string the max length
of a string or is there a place it bogs down?

Sub TestFormFilter
Dim s As String
Dim rst As Recordset
Set rst = CurrentDb.OpenR ecordset("Table 1",
dbOpenSnapshot) Do While Not rst.EOF
s = s & rst!ID & ","
rst.MoveNext
Loop
s = "ID IN (" & s & ")"
DoCmd.OpenForm "Form1", , , s
MsgBox "Done"
End Sub