473,848 Members | 1,559 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

153 New Member
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 16044
Kosmos
153 New Member
Just an update...it's stopping at the line recSet1.MoveFir st...I have no clue why
Oct 11 '07 #2
Kosmos
153 New Member
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 Recognized Expert Moderator MVP
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
6674
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 the whole db changed over in 1 hour. Since the upgrade I have been getting the following error message sporadically.
4
6977
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 "Invalid Argument". I try to delete that record, but it does not allow me to do that, always get "Invalid Argument" message. but if I delete a good record, it works. How can I delete that "Error" record in table? Please give me some suggestions!
3
2424
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 table. However I put some code in to display the key field of each parent table record (parent dataset) and the value I am trying to put into the child table is there. ParentTable ChildTable ID------------------------<...
2
12157
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 focus. Or is it FormB, whose code is doing the work?
9
11200
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 be no underlying 0 or non- zero for boolean values in C#, I am not sure how to handle this. Any advice would be appreciated. Thanks, JB
6
1778
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 doesn't work. i get a "compile error: method or data member not found". The code for the drop-down is this: Private Sub REC_ID_COMBO_AfterUpdate()
1
2264
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 they try to open it, the application does not fully open. What you see is a blank grey form with the dark blue title bar at the top with the appliation title. The 'main' form that is set on the startup properties does not come up. Everything is...
19
3910
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 is giving an error "either eof or bof is true or the current record has been deleted " When i try to use some other tables already there in oracle , with the same code ,its working fine. Also i have tried to use ADODC in place of ADODB coding...
25
20600
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, and try to move to another record and get an Access error "Record is too large". The record is only half filled, with many empty fields. If I remove the added data or delete some older data, then it saves ok and works fine again. Whenever I'm...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10997
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5731
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5917
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4542
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4134
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.