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


2 Answers
2

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.

QueryDefs()

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


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.

QueryDef

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.