By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,387 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,387 IT Pros & Developers. It's quick & easy.

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

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

Mary

Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_tblNJDOC()
  2.  
  3.  
  4. On Error GoTo Err_Hndlr
  5.  
  6. Dim dbs As DAO.Database
  7. Dim rstTemp As DAO.Recordset
  8. Dim strSQL As String
  9.  
  10. 'set variable values
  11. Set dbs = CurrentDb
  12.  
  13. strSQL = "SELECT NJDOC.ProductName, " & _
  14.                 "NJDOC.NDC_1, " & _
  15.                 "NJDOC.GPI, " & _
  16.                 "Count(NJDOC.Quantity) AS QuantitySummed, " & _
  17.                 "Sum(NJDOC.[Amount Billed]) AS AmountBilledSummed, " & _
  18.                 "Sum(NJDOC.AAC) AS SumOfAAC, Sum(NJDOC.BillFee) AS BillFeeSummed, " & _
  19.                 "Sum(NJDOC.[Cost Billed]) AS CostBilledSummed " & _
  20.         "FROM NJDOC " & _
  21.         "GROUP BY NJDOC.ProductName, " & _
  22.                 "NJDOC.NDC_1, NJDOC.GPI " & _
  23.         "ORDER BY NJDOC.ProductName;"
  24.  
  25.  
  26.  
  27. 'Delete temporary table
  28. ' DoCmd.RunSQL "DROP TABLE tblNJDOC;"
  29.  
  30. 'Create temporary table
  31. CurrentDb.Execute ("CREATE TABLE tblNJDOC(ProductName VARCHAR(125), " & _
  32.                 "NDC_1 VARCHAR(75), " & _
  33.                 "GPI integer, " & _
  34.                 "QuantitySummed currency, " & _
  35.                 "AmountBilledSummed currency, " & _
  36.                 "BillFeeSummed currency, " & _
  37.                 "CostBilledSummed currency)")
  38.  
  39. 'Bind rstTemp to the temporary table
  40. Set rstTemp = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
  41. Set rstSummary = CurrentDb.OpenRecordset("tblNJDOC")
  42.  
  43.  
  44.  '*********temp fields for writing records
  45. Dim strProductName_TEMP As String
  46. Dim strNDC_1_TEMP As String
  47. Dim strGPI_TEMP As Integer
  48. Dim strQuantitySummed_TEMP As Integer
  49. Dim strAmountBilledSummed_TEMP As Integer
  50. Dim strBillFeeSummed_TEMP As Integer
  51. Dim strCostBilledSummed_TEMP As Integer
  52.  
  53. Dim strNDC_1_Stack As String
  54.  
  55.  
  56. strFirstRec = "Yes"
  57.  
  58.  
  59. 'Move the data into the temporary table
  60.  
  61. rstTemp.MoveFirst
  62.  
  63. Do While rstTemp.EOF = False
  64.     ' ***************************************************
  65.     ' first record flag
  66.     ' ***************************************************
  67.     If strFirstRec = "Yes" Then
  68.         strFirstRec = "No"
  69.         strProductName_TEMP = rstTemp!ProductName
  70.         strNDC_1_TEMP = rstTemp!NDC_1
  71.         strGPI_TEMP = rstTemp!GPI
  72.         strQuantitySummed_TEMP = rstTemp!QuantitySummed
  73.         strAmountBilledSummed_TEMP = rstTemp!AmountBilledSummed
  74.         strBillFeeSummed_TEMP = rstTemp!BillFeeSummed
  75.         strCostBilledSummed_TEMP = rstTemp!CostBilledSummed
  76.      End If
  77.  
  78.     If rstTemp!ProductName = strstrProductName_TEMP Then
  79.         If rstTemp!NDC_1 = strNDC_1_TEMP Then
  80.             strNDC_1_Stack = strNDC_1_TEMP
  81.         Else
  82.             strNDC_1_Stack = strNDC_1_Stack + "/ " + rstTemp!NDC_1
  83.             rstTemp!NDC_1 = strNDC_1_TEMP
  84.         End If
  85.     Else
  86.       ' Write temp records to table
  87.         rstTemp.AddNew
  88.             rstSummary!ProductName = strProductName_TEMP
  89.             rstSummary!NDC_1 = strNDC_1_Stack
  90.             rstSummary!GPI = strGPI_TEMP
  91.             rstSummary!QuantitySummed = strQuantitySummed_TEMP
  92.             rstSummary!AmountBilledSummed = strAmountBilledSummed_TEMP
  93.             rstSummary!BillFeeSummed = strBillFeeSummed_TEMP
  94.             rstSummary!CostBilledSummed = strCostBilledSummed_TEMP
  95.         rstTemp.Update
  96.  
  97.         ' Move rst (record set) into the temp fields
  98.  
  99.         strProductName_TEMP = rstTemp!ProductName
  100.         strNDC_1_TEMP = rstTemp!NDC_1
  101.         strGPI_TEMP = rstTemp!GPI
  102.         strQuantitySummed_TEMP = rstTemp!QuantitySummed
  103.         strAmountBilledSummed_TEMP = rstTemp!AmountBilledSummed
  104.         strBillFeeSummed_TEMP = rstTemp!BillFeeSummed
  105.         strCostBilledSummed_TEMP = rstTemp!CostBilledSummed
  106.         ' move to next record set (rst)
  107.  
  108.         rstTemp.MoveNext
  109. Loop
  110.  
  111. ' write last record
  112.         rstSummary.AddNew
  113.             rstSummary!ProductName = strProductName_TEMP
  114.             rstSummary!NDC_1 = strNDC_1_Stack
  115.             rstSummary!GPI = strGPI_TEMP
  116.             rstSummary!QuantitySummed = strQuantitySummed_TEMP
  117.             rstSummary!AmountBilledSummed = strAmountBilledSummed_TEMP
  118.             rstSummary!BillFeeSummed = strBillFeeSummed_TEMP
  119.             rstSummary!CostBilledSummed = strCostBilledSummed_TEMP
  120.         rstSummary.Update
  121.  
  122. rstTemp.Close
  123. rstSummary.Close
  124.  
  125.  
  126. Create_tblNJDOC_Exit:
  127.   Exit Sub
  128.  
  129.  
  130. Err_Hndlr:
  131.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tblNJDOC()"
  132.  
  133. End Sub
  134.  
Oct 12 '10 #1

✓ answered by Mariostg

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

Share this Question
Share on Google+
3 Replies


100+
P: 332
You are missing an End If. Just after line 108.
Oct 12 '10 #2

100+
P: 133
Yes, thanks i found it after. it should have been line 107.

thanks FRESH EYES.
Oct 12 '10 #3

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 12 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.