Do While Syntax Error - Loop without Do - Can't See it?

[dowlingm815]

I am receiving a Do While Syntax Error - Loop without Do - Can't See it. I would appreciate any fresh eyes?

Mary

Private Sub Create_tblNJDOC()
 
 
On Error GoTo Err_Hndlr
 
Dim dbs As DAO.Database
Dim rstTemp As DAO.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 integer, " & _
                "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 Integer
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"
 
 
'Move the data into the temporary table
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
    ' ***************************************************
    ' first record flag
    ' ***************************************************
    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 = strstrProductName_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)
 
        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()"
 
End Sub
 

[Mariostg]

You are missing an End If. Just after line 108.

[dowlingm815]

Yes, thanks i found it after. it should have been line 107.

thanks FRESH EYES.

[NeoPa]

I'm pleased to see you got your problem solved, but next time, please include the line number the error occurred on. Especially if posting 133 lines of code.