Recordset Error, [13] Type Mismatch

[dowlingm815]

The code below is receiving a [13] mismatch type as soon as it hits the statement of

Set rstSummary = CurrentDb.OpenRecordset("tblNJDOC")

Again, in need of FRESH EYES...thank you in advance for guidance.

Mary

Private Sub Create_tblNJDOC()
 
'*****************************************************************************************
' This sub routine builds a temporary table to store the desired dates extract
'
'******************************************************************************************
 
 
On Error GoTo Err_Hndlr
 
Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset
Dim rstSummary As Recordset
Dim strSQL As String
 
'set variable values
Set dbs = CurrentDb
 
strSQL = "SELECT NJDOC.ProductName, " & _
                "NJDOC.NDC_1, " & _
                "NJDOC.GPI, " & _
                "Count(NJDOC.Quantity) AS QuantitySummed, " & _
                "Sum(NJDOC.[Amount Billed]) AS AmountBilledSummed, " & _
                "Sum(NJDOC.AAC) AS SumOfAAC, Sum(NJDOC.BillFee) AS BillFeeSummed, " & _
                "Sum(NJDOC.[Cost Billed]) AS CostBilledSummed " & _
        "FROM NJDOC " & _
        "GROUP BY NJDOC.ProductName, " & _
                "NJDOC.NDC_1, NJDOC.GPI " & _
        "ORDER BY NJDOC.ProductName;"
 
 
 
'Delete temporary table
  DoCmd.RunSQL "DROP TABLE tblNJDOC;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tblNJDOC(ProductName VARCHAR(125), " & _
                "NDC_1 VARCHAR(75), " & _
                "GPI varchar(20), " & _
                "QuantitySummed currency, " & _
                "AmountBilledSummed currency, " & _
                "BillFeeSummed currency, " & _
                "CostBilledSummed currency)")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstSummary = CurrentDb.OpenRecordset("tblNJDOC")
 
 
 '*********temp fields for writing records
Dim strProductName_TEMP As String
Dim strNDC_1_TEMP As String
Dim strGPI_TEMP As String
Dim strQuantitySummed_TEMP As Integer
Dim strAmountBilledSummed_TEMP As Integer
Dim strBillFeeSummed_TEMP As Integer
Dim strCostBilledSummed_TEMP As Integer
 
Dim strNDC_1_Stack As String
 
 
strFirstRec = "Yes"
strProductName_TEMP = " "
strNDC_1_TEMP = " "
strGPI_TEMP = " "
strQuantitySummed_TEMP = 0
strAmountBilledSummed_TEMP = 0
strBillFeeSummed_TEMP = 0
strCostBilledSummed_TEMP = 0
 
'Move the data into the temporary table
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
    If strFirstRec = "Yes" Then
        strFirstRec = "No"
        strProductName_TEMP = rstTemp!ProductName
        strNDC_1_TEMP = rstTemp!NDC_1
        strGPI_TEMP = rstTemp!GPI
        strQuantitySummed_TEMP = rstTemp!QuantitySummed
        strAmountBilledSummed_TEMP = rstTemp!AmountBilledSummed
        strBillFeeSummed_TEMP = rstTemp!BillFeeSummed
        strCostBilledSummed_TEMP = rstTemp!CostBilledSummed
     End If
 
    If rstTemp!ProductName = strProductName_TEMP Then
        If rstTemp!NDC_1 = strNDC_1_TEMP Then
            strNDC_1_Stack = strNDC_1_TEMP
        Else
            strNDC_1_Stack = strNDC_1_Stack + "/ " + rstTemp!NDC_1
            rstTemp!NDC_1 = strNDC_1_TEMP
        End If
    Else
      ' Write temp records to table
        rstTemp.AddNew
            rstSummary!ProductName = strProductName_TEMP
            rstSummary!NDC_1 = strNDC_1_Stack
            rstSummary!GPI = strGPI_TEMP
            rstSummary!QuantitySummed = strQuantitySummed_TEMP
            rstSummary!AmountBilledSummed = strAmountBilledSummed_TEMP
            rstSummary!BillFeeSummed = strBillFeeSummed_TEMP
            rstSummary!CostBilledSummed = strCostBilledSummed_TEMP
        rstTemp.Update
 
        ' Move rst (record set) into the temp fields
 
        strProductName_TEMP = rstTemp!ProductName
        strNDC_1_TEMP = rstTemp!NDC_1
        strGPI_TEMP = rstTemp!GPI
        strQuantitySummed_TEMP = rstTemp!QuantitySummed
        strAmountBilledSummed_TEMP = rstTemp!AmountBilledSummed
        strBillFeeSummed_TEMP = rstTemp!BillFeeSummed
        strCostBilledSummed_TEMP = rstTemp!CostBilledSummed
        ' move to next record set (rst)
    End If
 
        rstTemp.MoveNext
Loop
 
' write last record
rstSummary.AddNew
    rstSummary!ProductName = strProductName_TEMP
    rstSummary!NDC_1 = strNDC_1_Stack
    rstSummary!GPI = strGPI_TEMP
    rstSummary!QuantitySummed = strQuantitySummed_TEMP
    rstSummary!AmountBilledSummed = strAmountBilledSummed_TEMP
    rstSummary!BillFeeSummed = strBillFeeSummed_TEMP
    rstSummary!CostBilledSummed = strCostBilledSummed_TEMP
rstSummary.Update
 
rstTemp.Close
rstSummary.Close
 
 
Create_tblNJDOC_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tblNJDOC()"
 
 

[Mariostg]

dowlingm815, you really need to pay attention to your code, before an admin tells you...
You create table tblNJDOC yet your Select query refers to NJDOC.

Also why do you bother to Set dbs=currentDB if you don't use it (line 17, 37)

[dowlingm815]

NJDOC is the name of the table that needs to be re-formatted into a new table. therefore, there are two separate NJDOC tables, one, import, the second, create, via tblNJDOC.

in regard to dbs=currentDB, i believe i need due to the fact that an imported table is being called.

[MMcCarthy]

mariostg is correct, you set dbs = currentdb and then don't use it, you just use currentdb at line 17 and 37 instead of dbs. However, this would not cause your error.

I would add a DoEvents command after you create the table and before you try to put it in a recordset.

CurrentDb.Execute ("CREATE TABLE tblNJDOC(ProductName VARCHAR(125), " & _ 
                "NDC_1 VARCHAR(75), " & _ 
                "GPI varchar(20), " & _ 
                "QuantitySummed currency, " & _ 
                "AmountBilledSummed currency, " & _ 
                "BillFeeSummed currency, " & _ 
                "CostBilledSummed currency)") 
 
DoEvents
 
'Bind rstTemp to the temporary table 
Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) 
Set rstSummary = CurrentDb.OpenRecordset("tblNJDOC") 

This will insure that tblNJDOC is created before you try to use it.

Mary

[ADezii]

Why DELETE then RE-CREATE the Temporary Table for each Call of the Sub-Routine? Simple CREATE the Temp Table once, then at each Call to the Routine, DELETE all the Records, then perform your APPEND Operation to it.

[dowlingm815]

so by not setting dbs = currentDB, vba will assume it is the current database in use?