CODEWITHSUNDEEP
sqlvbaformsms-accessfilter
DavidD
DavidD

Reputation: 13

Microsoft Access - Crosstab of a filtered form

I'm trying to generate a report similar to a crosstab. The data are from a filtered form (Dates and WorkerID (String)).

On the following line:

Set qdf = dbsReport.QueryDefs(Me.RecordSource)

I'm getting the error:

Error 3265 Item not found in this collection

What am I doing wrong?

Private Sub Report_Open(Cancel As Integer)
 '  Create underlying recordset for report using criteria

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form

    '   Don't open report if frmReg form isn't loaded.
    If Not (IsLoaded("frmReg")) Then
        Cancel = True
        MsgBox "To preview or print this report, you must open " _
                & "frmReg in Form view.", vbExclamation, _
               "Must Open Dialog Box"
        Exit Sub
    End If

    '  Set database variable to current database.
    Set dbsReport = CurrentDb
    Set frm = Forms!frmReg

    '  Open QueryDef object.
    ' Set qdf = dbsReport.QueryDefs("ReportReg")
     Me.RecordSource = "SELECT * FROM [tReg]"
     Set qdf = dbsReport.QueryDefs(Me.RecordSource)

    '  Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

    '  Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count
End Sub

Upvotes: 1

Views: 169

Answers (2)

Parfait
Parfait

Reputation: 107652

The QueryDefs collection takes saved, named queries and not SQL statements. As @Jiggles32 demonstrates, you need to create a named query and then reference it with QueryDefs() call.

However, you can bypass the use of queries by simply directly opening recordsets with OpenRecordset() which is the end result of your needs:

strSQL = "SELECT * FROM [tReg]"

Me.RecordSource = strSQL
Set rstReport = dbsReport.OpenRecordset(strSQL)

'  Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

In fact, you can directly extract a form's recordset using RecordsetClone property (preferred over Recordset if running various operations to not affect form's actual records):

strSQL = "SELECT * FROM [tReg]"

Me.RecordSource = strSQL
Set rstReport = Me.RecordsetClone

'  Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

Upvotes: 0

ccarpenter32
ccarpenter32

Reputation: 1077

It looks like the problem might be a relationship issue between the SQL and the commands and you probably do not have a query setup to take the information you are seeking.

Try this:

sSQL = "SELECT * FROM [tReg]"
Me.RecordSource = sSQL
Set qdf = dbsReport.CreateQueryDef("NewQuery", sSQL)

'This will purge the query after your inteactions are complete
dbsReport.QueryDefs.Delete "NewQuery"

Note: This will not include any interactions for the QueryDef.

Upvotes: 1

Related Questions