Recordset error in vba

[nick243243]

I am having a recordset error when I import my new crosstab query report over to my new database. The same exact code runs fine in the original database but will not work now that I imported all the objects to the new database. Here is the code, I commented where the error is. I use a form to input a beginning and ending date then create a queryset that opens a new report based on that recordset of the query. I am getting a record set = to nothing and a datatype mismatch 13

Option Compare Database   'Use database order for string comparisons.
Option Explicit

'  Constant for maximum number of columns EmployeeSales query would
'  create plus 1 for a Totals column.
Const conTotalColumns = 11

'  Variables for Database object and Recordset.
Dim dbsReport As Database
Dim rstReport As Recordset

'  Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub Report_Open(Cancel As Integer)

    '  Create underlying recordset for report using criteria entered in
    '  EmployeeSalesDialogBox form.
    
    Dim intX As Integer
    Dim qdf As QueryDef
    Dim frm As Form
            
    '   Don't open report if EmployeeSalesDialogBox form isn't loaded.
    If Not (IsLoaded("EmployeeSalesDialogBox1")) Then
        Cancel = True
        MsgBox "To preview or print this report, you must open " _
        & "EmployeeSalesDialogBox1 in Form view.", vbExclamation, _
        "Must Open Dialog Box"
        Exit Sub
    End If

    '  Set database variable to current database.
    Set dbsReport = CurrentDb
    Set frm = Forms!EmployeeSalesDialogBox1
    
    
    
    '  Open QueryDef object.
   '   Set qdf = dbsReport.QueryDefs("EmployeeSales")
    
      Set qdf = dbsReport.QueryDefs(Me.RecordSource)
    
    
    ' Set parameters for query based on values entered
    ' in EmployeeSalesDialogBox form.
    qdf.Parameters("Forms!EmployeeSalesDialogBox1!BeginningDate") _
        = frm!BeginningDate
    qdf.Parameters("Forms!EmployeeSalesDialogBox1!EndingDate") _
        = frm!EndingDate

    '  Open Recordset object.
   [COLOR="Red"] Set rstReport = qdf.OpenRecordset()[/COLOR] 'Here is where the error is
    
    '  Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count

 

[isladogs]

You say it always worked before though I'm surprised.
Define the recordset for your report BEFORE you open it.
In other words do it from the form used to open it.

From the code portion we can see, I'm sure you could dramatically simplify the code.
Lots of Set ... statements here.
Are they needed?
Could you get the SQL for the recordset from a SQL string instead?

 

[nick243243]

You say it always worked before though I'm surprised.
Define the recordset for your report BEFORE you open it.
In other words do it from the form used to open it.

From the code portion we can see, I'm sure you could dramatically simplify the code.
Lots of Set ... statements here.
Are they needed?
Could you get the SQL for the recordset from a SQL string instead?

Thank you for your reply,

I have made what seems to be the correct record set in the report. I have ran the record set by my own dates and was given records in datasheet view. I could not get the record set to translate to the report. Some of this is over my head so bare with me. I have to use the form to put in the dates for this to run correctly. Every time I enter a date it says the record set is = to nothing and a datatype mismatch. I do the same thing in my other database and it brings the report up with no issues. The set statements are for the entire module. I will try to add more to help you understand my problem.

 

[JHB]

Put in a Debug.Print qdf.SQL just before the Set rstReport = qdf.OpenRecordset() then put the SQL string from the immediate window into a new query, then it is easier to see what the problem is.
Else show the SQL string from the immediate window here.
The way you do it is (a little) backwards to normal!

    Debug.Print qdf.SQL
    '  Open Recordset object.
    [COLOR=Red]Set rstReport = qdf.OpenRecordset()[/COLOR] 'Here is where the error is

 

[nick243243]

Put in a Debug.Print qdf.SQL just before the Set rstReport = qdf.OpenRecordset() then put the SQL string from the immediate window into a new query, then it is easier to see what the problem is.
Else show the SQL string from the immediate window here.
The way you do it is (a little) backwards to normal!

    Debug.Print qdf.SQL
    '  Open Recordset object.
    [COLOR=Red]Set rstReport = qdf.OpenRecordset()[/COLOR] 'Here is where the error is

I inserted the new code and nothing printed. Thank you for your help.

 

[JHB]

I think you forgot to show the immediate window.