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

Error: "Either BOF or EOF is True, or the current record has been deleted."

100+
P: 153
Hey ya'll...I can't seem to figure out why I'm getting this error message, but it all started when I added the new line of code with the recSet5.AddNew --- when I ran the first line, the logic worked and the fields were populated properly, when I added all the rest, it wouldn't run and even when I tried to go back, it still won't run now...I am getting the following error message:

"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

Any help would be much appreciated! Here is the code (Everything after the whole recSet5.AddNew part should work properly and has been tested):

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Private Sub cmdClaimsReleased_Click()
  4.     On Error GoTo Err_cmdClaimsReleased_Click
  5.  
  6. DoCmd.SetWarnings False
  7.  
  8.  DoCmd.OpenQuery ("ClearTblClaimSearch")
  9.  DoCmd.OpenQuery ("ClearTblTimeDifference")
  10.  DoCmd.OpenQuery ("ClearTblConvertedDates")
  11.  DoCmd.OpenQuery ("ClearTblOutputData")
  12.  DoCmd.OpenQuery ("ClearTblQuantities")
  13.  
  14.  
  15.     'Opening ADODB connections and record sets
  16.     Dim con1 As ADODB.Connection
  17.     Dim con2 As ADODB.Connection
  18.     Dim con3 As ADODB.Connection
  19.     Dim con4 As ADODB.Connection
  20.     Dim con5 As ADODB.Connection
  21.  
  22.     Dim recSet1 As ADODB.Recordset
  23.     Dim recSet2 As ADODB.Recordset
  24.     Dim recSet3 As ADODB.Recordset
  25.     Dim recSet4 As ADODB.Recordset
  26.     Dim recSet5 As ADODB.Recordset
  27.  
  28.     Set con1 = CurrentProject.Connection
  29.     Set con2 = CurrentProject.Connection
  30.     Set con3 = CurrentProject.Connection
  31.     Set con4 = CurrentProject.Connection
  32.     Set con5 = CurrentProject.Connection
  33.  
  34.     Set recSet1 = New ADODB.Recordset
  35.     Set recSet2 = New ADODB.Recordset
  36.     Set recSet3 = New ADODB.Recordset
  37.     Set recSet4 = New ADODB.Recordset
  38.     Set recSet5 = New ADODB.Recordset
  39.  
  40.     'setting records and connections to actual tables
  41.     recSet1.Open "tblClaimSearch", con1, adOpenKeyset, adLockOptimistic
  42.     recSet2.Open "tblOutputData", con2, adOpenKeyset, adLockOptimistic
  43.     recSet3.Open "tblTimeDifference", con3, adOpenKeyset, adLockOptimistic
  44.     recSet4.Open "tblQuantities", con4, adOpenKeyset, adLockOptimistic
  45.     recSet5.Open "tblConvertedDates", con5, adOpenKeyset, adLockOptimistic
  46.  
  47.  
  48.     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblClaimSearch", "C:\Documents and Settings\jpollard\Desktop\ClaimSearchResult", True
  49.  
  50.     recSet1.MoveFirst
  51.  
  52.     Do Until recSet1.EOF
  53.  
  54.         recSet5.AddNew
  55.             If Not IsNull(recSet1.Fields("LOAD_DATE")) Then
  56.                 recSet5.Fields("LOAD_DATE2") = CDate(Format(recSet1.Fields("LOAD_DATE"), "00\/00\/0000"))
  57.             End If
  58.             If Not IsNull(recSet1.Fields("DATE_SENT")) Then
  59.                 recSet5.Fields("DATE_SENT2") = CDate(Format(recSet1.Fields("DATE_SENT"), "00\/00\/0000"))
  60.             End If
  61.             If Not IsNull(recSet1.Fields("CLAIM_START")) Then
  62.                 recSet5.Fields("CLAIM_START2") = CDate(Format(recSet1.Fields("CLAIM_START"), "00\/00\/0000"))
  63.             End If
  64.             If Not IsNull(recSet1.Fields("CLAIM_END")) Then
  65.                 recSet5.Fields("CLAIM_END2") = CDate(Format(recSet1.Fields("CLAIM_END"), "00\/00\/0000"))
  66.             End If
  67.             If Not IsNull(recSet1.Fields("OUT_DATE")) Then
  68.                 recSet5.Fields("OUT_DATE2") = recSet1.Fields("OUT_DATE")
  69.             End If
  70.         recSet5.Update
  71.  
  72.     recSet1.MoveNext
  73.  
  74.     Loop
  75.  
  76.  
  77.  
  78.  
  79.     Dim TimeDifference As Integer
  80.     Dim X As Integer
  81.     Dim Y As Integer
  82.     Dim Z As Integer
  83.     Dim Q As Integer
  84.     Dim N As Integer
  85.     Dim DailyPercentage As Integer
  86.  
  87.     Dim IsOutlookOpen As Boolean
  88.  
  89.     Q = 0
  90.     X = 0
  91.     N = 0
  92.  
  93.     recSet5.MoveFirst
  94.  
  95.     Do Until recSet5.EOF
  96.  
  97.       If IsNull(recSet5.Fields("OUT_DATE")) = True Then
  98.  
  99.         N = N + 1
  100.  
  101.       Else
  102.  
  103.             TimeDifference = DateDiff("d", recSet5.Fields("LOAD_DATE2"), recSet5.Fields("OUT_DATE2"))
  104.  
  105.             recSet3.AddNew
  106.                 recSet3.Fields("TimeDifference") = TimeDifference
  107.             recSet3.Update
  108.  
  109.       End If
  110.  
  111.       If TimeDifference > X Then
  112.         X = TimeDifference
  113.       End If
  114.  
  115.       Q = Q + 1
  116.  
  117.     recSet5.MoveNext
  118.  
  119.     Loop
  120.  
  121.     Y = 0
  122.     Z = 0
  123.  
  124.     Do Until Y > X
  125.  
  126.         recSet3.MoveFirst
  127.  
  128.         Do Until recSet3.EOF
  129.  
  130.             If Y = recSet3.Fields("TimeDifference") Then
  131.                 Z = Z + 1
  132.             End If
  133.  
  134.         recSet3.MoveNext
  135.  
  136.         Loop
  137.  
  138.     DailyPercentage = ((Z / Q) * 100)
  139.  
  140.     recSet2.AddNew
  141.     recSet2.Fields("Days") = Y
  142.     recSet2.Fields("Claims") = Z
  143.     recSet2.Fields("PercentageOfTotal") = DailyPercentage
  144.     recSet2.Update
  145.  
  146.  
  147.     Y = Y + 1
  148.     Z = 0
  149.  
  150.     Loop
  151.  
  152.     recSet4.AddNew
  153.         recSet4.Fields("Q") = Q
  154.         recSet4.Fields("N") = N
  155.     recSet4.Update
  156.  
  157.                         Dim objOutlookRecip As Outlook.Recipient
  158.                         Dim outObj As Outlook.Application
  159.                         Set outObj = CreateObject("outlook.application")
  160.                         Dim olNs As Outlook.NameSpace
  161.                         Set olNs = outObj.GetNamespace("MAPI")
  162.                         olNs.Logon
  163.                         IsOutlookOpen = True
  164.                         Dim outMail As Outlook.MailItem
  165.                         Set outMail = outObj.CreateItem(olMailItem)
  166.                         outMail.To = "jordan.pollard@multiplan.com"
  167.                         outMail.Subject = "Percentage of Claims Released"
  168.                             recSet2.MoveFirst
  169.                             Do Until recSet2.EOF
  170.                                 outMail.Body = _
  171.                                     outMail.Body & " Days: " & recSet2.Fields("Days") & " Claims: " & recSet2.Fields("Claims") & " Percentage Of Total: " & recSet2.Fields("PercentageOfTotal")
  172.                             recSet2.MoveNext
  173.                             Loop
  174.                             recSet4.MoveFirst
  175.                             Do Until recSet4.EOF
  176.                                 outMail.Body = _
  177.                                         outMail.Body & "There were/was " & recSet4.Fields("N") & " claim(s) that have not yet gone outbound." & vbNewLine _
  178.                                         & "There was a total of " & recSet4.Fields("Q") & " claim(s) in the load."
  179.                             recSet4.MoveNext
  180.                             Loop
  181.                             outMail.Send
  182.                     Set outMail = Nothing
  183.                     Set outObj = Nothing
  184.  
  185.  
  186.  
  187.     'Closing connections and clearing record sets
  188.     recSet1.Close
  189.     recSet2.Close
  190.     recSet3.Close
  191.     recSet4.Close
  192.     recSet5.Close
  193.     con1.Close
  194.     con2.Close
  195.     con3.Close
  196.     con4.Close
  197.     con5.Close
  198.     Set con1 = Nothing
  199.     Set con2 = Nothing
  200.     Set con3 = Nothing
  201.     Set con4 = Nothing
  202.     Set con5 = Nothing
  203.     Set recSet1 = Nothing
  204.     Set recSet2 = Nothing
  205.     Set recSet3 = Nothing
  206.     Set recSet4 = Nothing
  207.     Set recSet5 = Nothing
  208.  
  209.  DoCmd.OpenQuery ("ClearTblClaimSearch")
  210.  DoCmd.OpenQuery ("ClearTblTimeDifference")
  211.  DoCmd.OpenQuery ("ClearTblOutputData")
  212.  DoCmd.OpenQuery ("ClearTblQuantities")
  213.  DoCmd.OpenQuery ("ClearTblConvertedDates")
  214.  
  215.   DoCmd.SetWarnings True
  216.  
  217.  
  218.  
  219.  
  220. Exit_cmdClaimsReleased_Click:
  221.     Exit Sub
  222.  
  223. Err_cmdClaimsReleased_Click:
  224.     MsgBox Err.Description
  225.     Resume Exit_cmdClaimsReleased_Click
  226. End Sub
  227.  
Thanks!
Kosmös
Oct 10 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 153
Just an update...it's stopping at the line recSet1.MoveFirst...I have no clue why
Oct 11 '07 #2

100+
P: 153
Alright never mind...I figured it out...and this is really important


IF YOU ARE LIKE ME AND LEARNED ACCESS THROUGH FORUMS...YOU MAY NOT KNOW THIS SO YOU MAY WANT TO READ THIS

You have to import the data into the table before you open up the connection because of how things are called into memory.

In the above code...by opening up RecSet1 before I imported the data, it saw a blank table

I KNOW it's not logical because you're supposed to be opening up a CONNECTION!!!!...which should mean that you can add data and pull it from the table after you've opened up the connection...or at least that makes sense...think about it...if I have a tube connected to another room and I then place money through the tube in the other room....why shouldn't I be able to go back and get it

anyways...I digress

damn you access you wasted like a whole day over some stupid syntax!!!

Hope this helps someone else tho :)
Oct 11 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
The problem is not with the connection but opening the recordset. The recordset will grab what is currently in the table at the time it is set. If you change your code to open the recordset after the import it should work fine.
Oct 13 '07 #4

Post your reply

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