How to reuse a query in VBA

[twgonder]

I want to do a Sql DAO recordset in VBA.
Then I want to reuse that same recordset in successive Sql recordsets.
I don’t want any of these used from a query object, all are built with VBA procedure code.
I haven’t yet seen an example of how this is done.
I’ll try a simple example to explain:
1. The big query: Select * FROM tbl_Permissions WHERE AppID=4012
2. Now I want to use the results of that query just done to start going through iteration options: Select * FROM (above query1) WHERE UserID=1059
3. If there aren’t any records found in step 2, then I want to do: Select * FROM (above query1) WHERE UserGrp=79
4. and on and on we go.



I’m trying to avoid redoing the first query up to six times (or more). I’m just not sure how to reference it in the FROM portion of the subsequent queries that will use the Set command.

Does this make sense? How can I reference the query done in step 1 in all the subsequent VBA queries?
Thanks

[June7]

Review https://www.devhut.net/how-to-filter...ing%20End%20If

[twgonder]

I'll read through June7's suggestion,

Here is what I tried, but obviously the second wsql didn't work:

Public Sub tTwgReuseQuery()
  'Try to reuse one query in another
  Dim daDb As DAO.Database, wSql As String, daRsr1 As DAO.Recordset, daRsr2 As DAO.Recordset


  Set daDb = CurrentDb


  wSql = "Select * from tbl_4_Entity WHERE NwResBeID=0102"
  Set daRsr1 = daDb.OpenRecordset(wSql, dbOpenSnapshot)
  
  If daRsr1.RecordCount <> 0 Then
    daRsr1.MoveFirst
    Do Until daRsr1.EOF
      'Debug.Print daRsr1!Nm1,
      daRsr1.MoveNext
    Loop


    wSql = "Select * from daRsr1 WHERE Ps=2"
    Set daRsr2 = daDb.OpenRecordset(wSql, dbOpenSnapshot)


    If daRsr2.RecordCount <> 0 Then
      daRsr2.MoveFirst
      Do Until daRsr2.EOF
        'Debug.Print daRsr2!Nm1,
        daRsr2.MoveNext
      Loop
    End If
  End If
    
  daRsr1.Close
  daRsr2.Close
  Set daRsr1 = Nothing
  Set daRsr2 = Nothing
  Set daDb = Nothing


End Sub

[June7]

That is not like code provided in the article.

Public Sub tTwgReuseQuery()
  'Try to reuse one query in another
  Dim daDb As DAO.Database, wSql As String, daRsr1 As DAO.Recordset, daRsr2 As DAO.Recordset

  Set daDb = CurrentDb

  wSql = "Select * from tbl_4_Entity WHERE NwResBeID=0102"
  Set daRsr1 = daDb.OpenRecordset(wSql, dbOpenSnapshot)
  
  If daRsr1.RecordCount <> 0 Then
    daRsr1.MoveFirst
    Do Until daRsr1.EOF
      'Debug.Print daRsr1!Nm1,
      daRsr1.MoveNext
    Loop

    daRsr1.Filter = "Ps=2"
    Set daRsr2 = daRsr1.OpenRecordset

    If daRsr2.RecordCount <> 0 Then
      daRsr2.MoveFirst
      Do Until daRsr2.EOF
        'Debug.Print daRsr2!Nm1,
        daRsr2.MoveNext
      Loop
    End If
  End If
    
  daRsr1.Close
  daRsr2.Close
  Set daRsr1 = Nothing
  Set daRsr2 = Nothing
  Set daDb = Nothing

End Sub

[twgonder]

@June7 post #4, I know, it's what I tried before I posted and read the article.

I am trying this, after reading the article, but there's a small problem,
The second record set isn't getting filtered (as best as I can tell).
I know lcnt2 should return 5 (for those with Ps=2),
but it's returning 36, the same as the original record set.
For some reason the filter isn't working in either of the two ways I tried (first commented out):

Public Sub tTwgReuseQuery()
  'Try to reuse one query in another
  Dim daDb As DAO.Database, wSql As String, daRsr1 As DAO.Recordset, daRsr2 As DAO.Recordset
  Dim lcnt1 As Long, lcnt2 As LongLong
  lcnt1 = 0: lcnt2 = 0
  Set daDb = CurrentDb


  wSql = "Select * from tbl_4_Entity WHERE NwResBeID=0102"
  Set daRsr1 = daDb.OpenRecordset(wSql, dbOpenSnapshot)
  
  If daRsr1.RecordCount <> 0 Then
    daRsr1.MoveFirst
    Do Until daRsr1.EOF
      Debug.Print daRsr1!Ps,
      lcnt1 = lcnt1 + 1
      daRsr1.MoveNext
    Loop


    'daRsr1.Filter = "[Ps]=2"
    Set daRsr2 = daRsr1
    daRsr2.Filter = "[Ps]=2"


    With daRsr2
      If .RecordCount <> 0 Then
        .MoveFirst
        Do Until .EOF
          'Debug.Print daRsr2!Nm1,
          lcnt2 = lcnt2 + 1
          .MoveNext
        Loop
      End If
    End With
  End If
  Debug.Print lcnt1, lcnt2


  daRsr2.Close
  'daRsr1.Close
  Set daRsr1 = Nothing
  Set daRsr2 = Nothing
  Set daDb = Nothing


End Sub

I think it's following the code sample from Daniel Pineault, however I'm not using a form's record set...?

[June7]

I did not use form recordset either and code worked for me.

[CJ_London]

I would be inclined to use .eof rather than movelast/recordcount

This simple function sets an initial query with a criteria then loops through different 'sub criteria' until some records are found (or none if that is the case

The initial recordset returns these records

CustomerID
GALED
GODOS
GOURL
GREAL
GROSR

and returns this message
1 records found with filter [CustomerID] like 'GA*'

changing case1 from GA to GZ the function then returns this message
2 records found with filter [CustomerID] like 'GO*'

Function getRS() As DAO.Recordset
Const pSql = "SELECT * FROM nwdCustomers WHERE [CustomerID] like 'G*'"
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim rs2 As DAO.Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset(pSql)
If Not rs.EOF Then
    rs.Filter = "[CustomerID] like 'GX*'"
    Set rs2 = rs.OpenRecordset
    i = 0
    Do While rs2.EOF
        i = i + 1
        Select Case i
    
            Case 1: rs.Filter = "[CustomerID] like 'GA*'"
            Case 2: rs.Filter = "[CustomerID] like 'GB*'"
            Case 3: rs.Filter = "[CustomerID] like 'GC*'"
            Case 4: rs.Filter = "[CustomerID] like 'GD*'"
            Case 5: rs.Filter = "[CustomerID] like 'GO*'"
            Case Else: Exit Do
        End Select
        Set rs2 = rs.OpenRecordset
    
    Loop
    If rs2.EOF Then
        Debug.Print "no records found with any filter"
    Else
        rs2.MoveLast
        Debug.Print rs2.RecordCount & " records found with filter " & rs.Filter
        Set getRS = rs2.OpenRecordset
        
    End If
Else
    Debug.Print "no initial records found"
End If




End Function

clearly the filter can be anything (valid) you like, based on other fields etc

[twgonder]

@ June7 post #6

Did you use my code and change a table and field names?
Or did you use some other code?

[twgonder]

@CJ_London, post#7

I think we covered this before, on using recordcount. Sometimes I need to see if one or two records came in, and if more an error has occurred. So, I like recordcount for its flexibility. Have you seen a problem using it?

I'll go through your example to see if there's something there that I missed in mine. Thanks.

[twgonder]

After a bleary-eyed day of looking at code, I missed this important part, and changed three lines of code:

daRsr1.Filter = "[Ps]=2"
 Set daRsr2 = daRsr1.OpenRecordset
 'daRsr2.Filter = "[Ps]=2"

Thanks for the example CJ_London

[alexcray]

The DAO library provides a programmatic interface for interacting with Access databases, including executing queries.

[CJ_London]

Sometimes I need to see if one or two records came in, and if more an error has occurred.

I was responding to your question as asked

3. If there aren’t any records found in step 2, then I want to do: Select * FROM (above query1) WHERE UserGrp=79
4. and on and on we go

[twgonder]

As to my post#9 question, today I found the situation where .EOF is better than .recordcount.
My code always worked, because there was at least one record there.