Filtering a DAO Recordset

**GraeagleBill** · 09-29-2021, 04:49 PM

The compiler does not seem to recognize the "If Then Else" structure in the following segment of code. ("Doesn't like the ELSE if there's no "If". ") If I comment out the "With" statement, the code compiles normally. What I'm trying to accomplish is simply process the un-filtered DAO Recordset or a filtered version of the recordset.

How do I handle a dynamic choice of "With"?

Code:

(clip)
Set rsEMAs = DBEngine(0)(0).OpenRecordset(QName)


If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
    Set rsEMAsFltrd = rsEMAs.OpenRecordset
    With rsEMAsFltrd
Else
    With rsEMAs
End If


lngEMACount = .RecordCount
MsgBox lngEMACount
intEMAPartCnt = 1


If lngEMACount > 0 Then
    .MoveFirst
    strToClipBoard = !clipEMA & vbNewLine
    
    .MoveNext
        While Not .EOF
            strToClipBoard = strToClipBoard & !clipEMA & vbNewLine
            .MoveNext
        Wend
    
    ClipBoard_SetText (strToClipBoard)
End If


End With
(clip)

**Micron** · 09-29-2021, 05:11 PM

The error is "End If without Block If "?? You've started a With block, which becomes "nested" in your If block and vba will see the End If inside the With block and complain because there's no starting If inside the With block. Access isn't even going to complain about a missing End With because compile will fail as soon as it reaches the unmatched End If.

If, With and Select Case are 3 blocks I can think of that must be started and ended as nested pairs when they are in fact nested.

How do I handle a dynamic choice of "With"?

Move it to before lngEMACount = ? Confess I have no idea what a dynamic choice of With means.

**GraeagleBill** · 09-29-2021, 05:22 PM

choice of With

I just don't know what else to call it? In one case I need "With rsEMAs" and in the other case "With rsEMAsFltrd". I.e., I have to dynamically choose which DAO Recordset to process.

Move it

Move what?

**Micron** · 09-29-2021, 05:42 PM

Move it was my answer to your question (which I quoted).
Put the entire With block in a decision block regardless of whether that decision is a Select Case block, If block or anything else.

With is a statement that is sort of a short form of reference to an object - it's not about deciding anything at all, so it's not dynamic. Only one object allowed per with block. So if you're attempting to use things like .MoveNext etc. by only typing it once, you cannot. Review blue text if that hasn't registered yet.

Note: I missed your first With statement but what I wrote still applies - more so to the Else, which is just an extension of your If decision block.

**GraeagleBill** · 09-29-2021, 07:42 PM

It now seems that the portion of the code that sets the filter doesn't work, as the count is always one (1) whereas the filtered sets have several records.

I did manually run the query in design mode that is referenced in the variable QName with the "ClassID" set appropriately to observe the records returned.

Code:

Dim strToClipBoard As String
Dim objDAO As Object
Dim I As Integer


bolrsRegEMAs = True


Set rsEMAs = DBEngine(0)(0).OpenRecordset(QName)


If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
    Set rsEMAsFltrd = rsEMAs.OpenRecordset
    Set objDAO = rsEMAsFltrd
Else
    Set objDAO = rsEMAs
End If


'With objDAO              <<<<<<<< Commented out to test filtering
With rsEMAsFltrd        '<<<<<<<< WITH BLOCK works with and without using object variable objDAO


lngEMACount = .RecordCount
MsgBox lngEMACount
intEMAPartCnt = 1


If lngEMACount > 0 Then
    .MoveFirst
    strToClipBoard = !clipEMA & vbNewLine
    
    .MoveNext
        While Not .EOF
            strToClipBoard = strToClipBoard & !clipEMA & vbNewLine
            .MoveNext
        Wend
    
    ClipBoard_SetText (strToClipBoard)
End If


End With

**Micron** · 09-29-2021, 08:43 PM

Not sure what to tell you when I can't see what happens when it runs.
Did you put a break on Set rsEMAsFltrd = rsEMAs.OpenRecordset and check the value of strCurClassID?
Did you check the filter construct from the immediate window ( ?rsEMAs.Filter)
If it's a string it needs quote delimiters?

You should not be surprised that the With block doesn't error (not sure you can say it works because whatever works has nothing to do with the With statement). The With block syntax is either correct or it is not. That all the code in it does (or doesn't) do what you want is irrelevant to that. Your With block pertains to the rsEMAsFltrd object. The references to anything about that object happen to be:
.MoveFirst
!clipEMA
.MoveNext
.EOF
.MoveNext

The first red comment in the new code post has me thinking you're still not understanding what the With block really is so maybe time to research it? I don't seem to be explaining it very well.

Why would you dim a recordset variable as an object instead of a recordset?
There are at least 5 variable declarations not shown. You have them in the code but didn't post them?

**GraeagleBill** · 09-29-2021, 11:13 PM

not sure you can say it works because whatever works has nothing to do with the With statement

I mean the setting of the Recordset object works. I.e., With objDAO having been set above in the If block.

I can't see anything wrong with the filter.

Click image for larger version.

Name: 000.jpg
Views: 15
Size: 88.5 KB
ID: 46320

**Minty** · 09-30-2021, 05:28 AM

Am I missing something here ?
Why not have one recordset object and either apply the filter or don't?

Code:

(clip)
Set rsEMAs = DBEngine(0)(0).OpenRecordset(QName)


If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
End If

If rsEMAs.EOF then exit sub    '(No records)
With rsEMAs 
   .movelast
   .movefirst          ' Recordcount won't be accurate without this
   lngEMACount = .RecordCount
   MsgBox lngEMACount
   intEMAPartCnt = 1

etc. etc.

**GraeagleBill** · 09-30-2021, 08:33 AM

I was following the example given here:
https://docs.microsoft.com/en-us/off...r-property-dao

With your suggestion, I tried to filter the way one would normally filter a form's RecordSource:

Code:

Set rsEMAs = DBEngine(0)(0).OpenRecordset(QName)


'If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
'    Set rsEMAsFltrd = rsEMAs.OpenRecordset
'    Set objDAO = rsEMAsFltrd
'Else
'    Set objDAO = rsEMAs
'End If


'With objDAO
With rsEMAs


lngEMACount = .RecordCount
MsgBox lngEMACount

The DAO Recordset still returns an "un-filtered" set.

**Welshgasman** · 09-30-2021, 08:50 AM

https://docs.microsoft.com/en-us/off...r-property-dao

I have just tried the method as per the above link (which you appeared to do initially) and it works for me.?

Also, as you have two recordsets, why not just set the second to the first .openrecordset, regardless of whether filtered or not? Why objDAO.?

**GraeagleBill** · 09-30-2021, 09:08 AM

If I change the objDAO Dim to RecordSet, as Micron commented on, I get a data type runtime error on statement "Set objDAO = rsEMAsFltrd".

I'm at a loss as to what to try next. The code below continues to return an un-filtered set.

Code:

Dim strToClipBoard As String
Dim objDAO As Object
Dim I As Integer


bolrsRegEMAs = True


Set rsEMAs = DBEngine(0)(0).OpenRecordset(QName)


If (strCurClassID >= 2 And strCurClassID <= 5) Then
    rsEMAs.Filter = "ClassID = " & strCurClassID
    Set rsEMAsFltrd = rsEMAs.OpenRecordset
    Set objDAO = rsEMAsFltrd
Else
    Set objDAO = rsEMAs
End If


With objDAO


lngEMACount = .RecordCount
MsgBox lngEMACount

**Welshgasman** · 09-30-2021, 09:26 AM

I do not know what to say?

Here is my test code, adapted from another test I did for another forum

Code:

Sub testWithLoop()
Dim db As DAO.Database, rs As DAO.Recordset, rs1 As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select Top 20 ID from Transactions")
rs.Filter = "ID < 10"
Set rs1 = rs.OpenRecordset
rs.MoveLast
rs1.MoveLast
rs.MoveFirst
rs1.MoveFirst

Debug.Print rs.RecordCount
Debug.Print rs1.RecordCount

Do Until rs.EOF
    With rs
        Debug.Print "rs is " & !ID
        With rs1
            Debug.Print "rs1 is" & !ID
            .MoveNext
        End With
    .MoveNext
    End With
Loop
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

End Sub

and here is the output in Debug

Code:

 20 
 7 
rs is 2
rs1 is2
rs is 3
rs1 is3
rs is 4
rs1 is4
rs is 6
rs1 is6
rs is 7
rs1 is7
rs is 8
rs1 is8
rs is 9
rs1 is9
rs is 10

**Micron** · 09-30-2021, 09:29 AM

I too was wondering why filter a recordset but didn't bother to ask. Methinks if all I need in the end is one recordset I'd just create that with a query or sql statement that does the job.

If you're saying that you replicate this with a test query with criteria 5 and it works, then I'm stumped. For a moment I thought perhaps the If block is not entered but if that were true I suspect it would error because the object wouldn't get set. While it may not be an issue now, it's ripe for an unexpected runtime error if something is not between 2 and 5 and you try to act on an object that = Nothing.

This might be one that needs a sample db posted, assuming you stay the course.

BTW, you didn't answer about the declarations so I'll just say you don't seem to be referencing the exact library when you can. If you have ADO and DAO referenced and don't specify the library for things like recordsets, you can end up trying to do DAO stuff on an ADO recordset or vice-versa. That's a point for the future, not this particular issue.

**Welshgasman** · 09-30-2021, 09:47 AM

I am assuming you have walked through the code?

**GraeagleBill** · 09-30-2021, 09:54 AM

Yes.
Off to the dentist......... "I'll be back"

Filtering a DAO Recordset

Thread Tools

Filtering a DAO Recordset

Similar Threads

Filtering - Table or recordset

MS Access Filtering Query Recordset

Theory I hope to work with recordset and filtering another recordset

Recordset Filtering

create table filtering a recordset

Posting Permissions