473,378 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

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

153 100+
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
3 15986
Kosmos
153 100+
Just an update...it's stopping at the line recSet1.MoveFirst...I have no clue why
Oct 11 '07 #2
Kosmos
153 100+
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
14,534 Expert Mod 8TB
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

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

Similar topics

7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
4
by: barbara_dave | last post by:
Hi all, We have a Access database used by multiple user. For some reason the database get a "Error" record. When doing search or record pointer point to this record, we get run time error...
3
by: PAUL | last post by:
Hello, I have 2 datasets I am trying to update. The parent table seems to update fine but when I go update the chiled table I get an error message that says I need a related record in the parent...
2
by: MLH | last post by:
Suppose that code running on FormB is moving the focus around on FormA to various textbox controls on FormA - Which form is the current form during this process? Is it FormA, which has the...
9
by: Jamey Bon | last post by:
As a newbie to C#, I am not sure what I can do about this. I would like to do something like an Enumeration to use "constants" like Yes to indicate true and No for false. But since there seems to...
6
by: JHNielson | last post by:
This is a very simple question.... I have a form that looks up Records for an unbound drop-down list. It has worked just fine up until last night. Now the button on the form to delete a record...
1
by: ncsthbell | last post by:
I have an Access 2000 database (running on Windows XP) that is used as a model and each user grabs a copy of this and puts onto their own drive. I have had 2 folks have issues with it lately. When...
19
by: mask | last post by:
Hi all, I have a problem of urgent nature . I am using Oracle and VB6 for an application . i am using ADODB coding In oracle i have created a table and trying to connect thru vb form. the vb code...
25
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record,...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.