Recordset Clone Filtering issue

[derekroger]

Hi There,

So I have come to a roadblock regarding recordset filtering.

I have a sub procedure that creates a clone of the form's recordset to check that at least one record matches the filter criteria before it is applied to the form (to avoid errors):

Public Sub sub_CheckRecordCount()
On Error GoTo eh:
Dim rs As Recordset
Set rs = Me.Recordset.Clone

mbIsOKToFilter = True

With rs
.Filter = msFilter
If .RecordCount = 0 Then
mbIsOKToFilter = False
MsgBox "Sorry, there are no records that match the specified criteria.", vbExclamation, "No Records"
End If
End With

ex:
Set rs = Nothing
Exit Sub

eh:
MsgBox "Error '" & Err.Description & "' in procedure sub_CheckRecordCount of Form frmExtract"
Resume ex

Now this works perfectly for most filter strings. But as soon as my filter string look a little like the following (i.e. 2 levels of conditions) ...

(Field1 = 1 OR Field2 = 1 OR Field3 = 1) AND (Field4 = -1)

... an error occurs. And the odd thing is, this exact filter string works seamlessly on the actual recordset - it's only the clone that throws an error.

So I'm at my wits end at the moment ... any help would be greatly appreciated.

 

[dkinley]

Not sure if this has anything to do with but I always set the filter prior to setting the recordset.

For instance, you have

Set rs = Me.Recordset.Clone
With rs
   .Filter = msFilter
...

What I am is proposing is something like this ...

rs.Filter = msFilter
Set rs = Me.Recordset.Clone
...

This was just the way I learned it and have always done it this way - I can't attempt to reverse the structure on one of my existing db's right now to see if I get an error at the moment, but a wild idea ....

-dK

 

[derekroger]

Thanks for your reply dkinley.

No, I'm not able to apply a filter before I set the recordset variable rs.

 

[LPurvis]

You're using an ADP?
i.e. these are ADO recordsets?
(There's nothing here to give definitive indication as to which it would be - other than the implication that these filters are immediately applied, which is not the case with DAO recordsets).

So, what is the exact error message?
That the "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." ?

Cheers.

 

[derekroger]

Sorry, I should've specified that.

I'm using an ADP with ADO recordsets. And yes, that's the error message I get.

 

[dkinley]

Hmmm. Try explicitly declaring the recordset?

Dim rs As ADODB.Recordset

-dK

 

[derekroger]

Nope, no good.

 

[LPurvis]

I'm only surprised that you say this works on the recordset but not the clone.
There's no apparent reason for that.
However the filter expression you're applying is absolutely standard to receive this kind of result.
Multiple OR operations (as well as other options) aren't always supported in a resolvable way. If you can't rejig the Filter expression (I don't see how you'll be able to here) then you are just left with re-opening a recordset. Undesirable I know, but the underlying database engine will always have more support for these operations than ADO's own.

Sorry. Cheers.

 

[derekroger]

Okay, thanks for you help. Looks like I'll have to rethink it all.

 

[derekroger]

If anyone finds this via Google, I ended up using a stored procedure to count the filtered number of records (as the Access DB uses a MS SQL backend) instead of a clone of the recordset.

 

[LPurvis]

That's fair enough, though it raises a couple of considerations.
If your application was likely to have to consider scal;ability (i.e. if the number of users was to become large) then the data you fetch is of greater importance (it always is to my mind).
Firstly, performing a subsequent SP call to do a record count is better than loading records locally only to count them. But it's still another request.

If you have many records loaded locally, then iterating through the recordset clone to look for matches to the would-be filter criteria and counting those would be potentially heavy on client processing - but it would save that server request.
However - that would then beg the question, why so many records locally?
There's seldom need to load many records into a form. As required is a much better policy.

See how you go.

Cheers.