How to Select second Recordset from first recordset

[FrustratedAlso]

Do you want the whole database? Where would I put it?

[FrustratedAlso]

I've been able to fix the problem with my database? Found the problem by creating a blank database from an old backup and then importing things one by one till it broke.

The line that is failing is : Set qryDef = CurrentDb.QueryDef("qrySalesRec")



Attachment 7374

[AccessMSSQL]

You need to use

set qryDef = Currentdb.Querydefs("qrySalesRec")

[FrustratedAlso]

I still get error
Attachment 7391

Where does strSql1 get executed?

    'Build SQL strings, and get the records.
    'select Records to Count
        strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
        Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
        qryDef.SQL = strSql1
        
    'Count the Records
        strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
        Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)

[ssanfu]

Where does strSql1 get executed?

Hold on....crash course time.

Think of a query as a "virtual table". It looks like a table and acts like a table.. and you can join multiple tables together to form a "new", bigger virtual table. "Virtual" because it disappears after the code finishes executing. (But the saved query stays)
So now you have code like this:

   'Build SQL strings, and get the records.
   'select Records to Count
   strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
   Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
   qryDef.SQL = strSql1

So there is a saved query named "qrySalesRec". When the code is executed, the line in blue in the above code modifies the SQL of the saved query. If you open the query and look at it in SQL view, you will see that the criteria for the field "Venue" is the last value that was used when the query was last executed.

Then the next bit of code is:

   'Count the Records
   strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
   Set SalRec2 = CurrentDb.OpenRecordset(strSql2, dbOpenDynaset)

This creates a recordset in code. The SQL is using the saved query "qrySalesRec" as the "table" to select the records from. You cannot create a recordset based on a recordset.
Then the recordset "SalRec2" is created based on the saved query (qrySalesRec) which has as the recordsource the SQL string "strSql1".

OK...

You were getting the error because you were referencing the field in the recordset wrong. The fields are zero based. So you need to use:

      strOut = SalRec2(0)

Here is the code I tried (with modifications):

Public Function sPeriod(OutLet As String) As Variant
   On Error GoTo Err_Handler
   'Purpose:   Count Distinct Month/Year in Sales Records.
   'Return:    Integer, or 0 if no matches.
   'Arguments: None
   '
   'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
   '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
   '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
   '           4. Returning more than 255 characters to a recordset triggers this Access bug:
   '               http://allenbrowne.com/bug-16.html
   Dim SalRec2 As DAO.Recordset     'Related records
   Dim strSql1 As String            'SQL statement
   Dim strSql2 As String            'SQL statement
   Dim strOut As Variant           'Output string to concatenate to.
   Dim qryDef As DAO.QueryDef
   
   'Initialize to 0
   sPeriod = 0


   'Build SQL strings, and get the records.
   'select Records to Count
   strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
   Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
   qryDef.SQL = strSql1
   'Count the Records
   strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
'   Set SalRec2 = DBEngine(0)(0).OpenRecordset(strSql2, dbOpenDynaset)
   Set SalRec2 = CurrentDb.OpenRecordset(strSql2, dbOpenDynaset)

   ' Check for Count
   If Not (SalRec2.BOF And SalRec2.EOF) Then
      strOut = SalRec2(0)
   End If

   SalRec2.Close
   'Return the result
   sPeriod = strOut

Exit_Handler:
   'Clean up

   Set SalRec2 = Nothing
   Exit Function
Err_Handler:
   MsgBox "Error " & Err.Number & ": " & Err.Description & Err.Source
   Resume Exit_Handler
End Function

At least it returned the correct value in my test table..

Also you should have these two lines at the top :

Option Compare Database
Option Explicit

Whew....

[FrustratedAlso]

I am still getting the same 3265 error!!! This is exactly the code I used

Option Compare Database
Option Explicit
Public Function sPeriod(OutLet As String) As Variant
On Error GoTo Err_Handler
    'Purpose:   Count Distinct Month/Year in Sales Records.
    'Return:    Integer, or 0 if no matches.
    'Arguments: None
    '
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    
    Dim SalRec2 As DAO.Recordset     'Related records
    Dim strSql1 As String            'SQL statement
    Dim strSql2 As String            'SQL statement
    Dim strOut As Variant           'Output string to concatenate to.
    Dim qryDef As DAO.QueryDef
    'Initialize to 0
    sPeriod = 0
    
    
    'Build SQL strings, and get the records.
    'select Records to Count
        strSql1 = "SELECT DISTINCT(Month(Sales.[SaleDate])) as sMth,(Year(Sales.[SaleDate])) as sYr FROM Sales WHERE Sales.[Venue] = '" & OutLet & "'"
        Set qryDef = CurrentDb.QueryDefs("qrySalesRec")
        qryDef.SQL = strSql1
        
    'Count the Records
        strSql2 = "SELECT Count([sMth]) FROM qrySalesRec"
        Set SalRec2 = CurrentDb.OpenRecordset(strSql2, dbOpenDynaset)
    ' Check for Count
    If Not (SalRec2.BOF And SalRec2.EOF) Then
      strOut = SalRec2(0)
    End If
    
    SalRec2.Close
       'Return the result
       sPeriod = strOut
     
Exit_Handler:
    'Clean up
     
     Set SalRec2 = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & Err.Source
    Resume Exit_Handler
End Function

[ssanfu]

Try changing

strOut = SalRec2(0)

to
strOut = SalRec2.Fields(0)


If you open "qrySalesRec" by double clicking in the database window, are there records returned?
Have you single stepped through the function? What line does it error on?

[FrustratedAlso]

I'm not sure if I did this right: I created a macro to run the function. I set a breakpoint at sPeriod = 0. I ran the macro, never gets to breakpoint, fails immediately. The error "3265: Item not found in this collection.DAO.Query.Defs".

"Item" is a hidden member of "Dao.QueryDefs", but not a member of "Collection". I am not sure why the query thinks it should be or why it is not.

[ssanfu]

Do you have a reference set to "Microsoft DAO 3.6 Object Library"?
Have you compiled the code? (menu DEBUG / COMPILE)

If no errors, then

In the IDE, I set a breakpoint at the line "sPeroid = 0"
Then I opened the Immediate Window <ctl-G> and entered:

  ? sPeriod("ABBA")

Change "ABBA" to a venue (outlet) that you know is in your table.
Press ENTER.
The yellow indicator should appear at "sPeroid = 0". Press the F8 key to advance one line.

[FrustratedAlso]

I did not have a reference to "Microsoft DAO 3.6 Object Library"
I went to references and tried to select it but it gave a conflict message
I had these items selected:

1. Visual Basic for Applications
2. MicroSoft Access 12.0 Object Library
3. Ole Automation
4. MicroSoft Access 12.0 DataBase Engine Object Library

I unchecked item 4 and replaced it with Microsoft DAO 3.6 Object Library

I then followed your directions
I highlighted the sPeriod = 0 and used the toggle Breakpoint option on debug menu
opened the immediate window entered ? sPeriod("8 Magnolia Blvd")
It immediately gives the 3265 error and when you click OK on the error box, program ends does not break for step thru

Attachment 7453

[ssanfu]

Any chance of posting your dB or PM it to me? It sounds like there is a field name issue.

The code executes for me, but I created the table and code, which might be different from yours.

[FrustratedAlso]

Here is stripped db

[ssanfu]

The problem is you don't have a saved query named "qrySalesRec".
Create and save a query named "qrySalesRec". I used "SELECT [SaleDate] FROM Sales"
Then execute the code using

? sPeriod("8 Magnolia Blvd")

in the immediate window.
--------------------------------

Also, I noticed that you have a lot of lookup fields in your tables. This is considered a bad practice.
See: http://access.mvps.org/access/tencommandments.htm
And follow the link in #2: "Lookup Fields"

[FrustratedAlso]

Thanks for all your help, I had to divert my efforts elsewhere for a while.
This is a work in progress, thought it would be easier to setup and manipulate in access until I got it the way I need it. Unfortunatlely I'm not sure it is. The Function works now But here is the problem, I can't use it to sum because of the where clause. What I trying to do is report total by venue with a total of periods times the rental amount per period.