473,324 Members | 1,646 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,324 software developers and data experts.

How do I solve this run-time error 3021?

Hi all,

I have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.

Each time I run my code I receive "run-time error 3021: No current record."

It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit" of my code.

I'm suspecting that my code is running too fast such that it didn't manage to read that the table has already been populated in the timestamp column due to the rstInsert.AddNew.

How do I solve this?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click()
  2.     Dim dbs As DAO.Database
  3.     Dim rstTimestamp As DAO.Recordset
  4.     Dim rstAcknowledgement As DAO.Recordset
  5.     Dim rstAgent As DAO.Recordset
  6.     Dim rstDetails As DAO.Recordset
  7.     Dim rstInsert As DAO.Recordset
  8.  
  9.     Set dbs = CurrentDb
  10.     Set rstTimestamp = dbs.OpenRecordset("GMT+8", dbOpenDynaset)
  11.     Set rstAcknowledgement = dbs.OpenRecordset("Acknowledgement", dbOpenDynaset)
  12.     Set rstAgent = dbs.OpenRecordset("AgentName", dbOpenDynaset)
  13.     Set rstDetails = dbs.OpenRecordset("Table2", dbOpenDynaset)
  14.     Set rstInsert = dbs.OpenRecordset("Final", dbOpenDynaset)
  15.  
  16.     If Not rstTimestamp.EOF Then
  17.         Do
  18.             rstInsert.AddNew
  19.             rstInsert![Timestamp] = rstTimestamp![Timestamp (GMT+8)]
  20.             rstInsert.Update
  21.             rstTimestamp.MoveNext
  22.         Loop Until rstTimestamp.EOF
  23.     End If
  24.     RefreshDatabaseWindow
  25.     If Not rstAcknowledgement.EOF Then
  26.         Do
  27.             rstInsert.Edit
  28.             rstInsert![SNOCAcknowledged] = rstAcknowledgement![Field2]
  29.             rstInsert.Update
  30.             rstAcknowledgement.MoveNext
  31.         Loop Until rstAcknowledgement.EOF
  32.     End If
  33.     RefreshDatabaseWindow
  34.     If Not rstAgent.EOF Then
  35.         Do
  36.             rstInsert.Edit
  37.             rstInsert![AgentName] = rstAgent![Field2]
  38.             rstInsert.Update
  39.             rstAgent.MoveNext
  40.         Loop Until rstAgent.EOF
  41.     End If
  42.     RefreshDatabaseWindow
  43.     If Not rstDetails.EOF Then
  44.         Do
  45.             rstInsert.Edit
  46.             rstInsert![Details] = rstDetails![Field5]
  47.             rstInsert.Update
  48.             rstDetails.MoveNext
  49.         Loop Until rstDetails.EOF
  50.     End If
  51.  
  52. End Sub
  53.  
Ps, I have attached a picture showing my final table's column names.
Attached Images
File Type: jpg Headers.jpg (9.3 KB, 171 views)
Nov 25 '14 #1
1 2391
twinnyfo
3,653 Expert Mod 2GB
Shawn,

I'm really confused about how you are trying to do this. It appears you have four recordsets based on four different tables and you are trying to combine everything into one table?

What happens if the four tables have different numbers of records?

If your are always ABSOLUTELY CERTAIN that they will always have identical numbers of records, then you could probably combine what you are doing into one loop:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command9_Click()
  2.     Dim dbs As DAO.Database
  3.     Dim rstTimestamp As DAO.Recordset
  4.     Dim rstAcknowledgement As DAO.Recordset
  5.     Dim rstAgent As DAO.Recordset
  6.     Dim rstDetails As DAO.Recordset
  7.     Dim rstInsert As DAO.Recordset
  8.  
  9.     Set dbs = CurrentDb
  10.     Set rstTimestamp = dbs.OpenRecordset("GMT+8", dbOpenDynaset)
  11.     Set rstAcknowledgement = dbs.OpenRecordset("Acknowledgement", dbOpenDynaset)
  12.     Set rstAgent = dbs.OpenRecordset("AgentName", dbOpenDynaset)
  13.     Set rstDetails = dbs.OpenRecordset("Table2", dbOpenDynaset)
  14.     Set rstInsert = dbs.OpenRecordset("Final", dbOpenDynaset)
  15.  
  16.     If Not rstTimestamp.EOF Then
  17.         Do
  18.             rstInsert.AddNew
  19.             rstInsert![Timestamp] = rstTimestamp![Timestamp (GMT+8)]
  20.             rstInsert![SNOCAcknowledged] = rstAcknowledgement![Field2]
  21.             rstInsert![AgentName] = rstAgent![Field2]
  22.             rstInsert![Details] = rstDetails![Field5]
  23.             rstInsert.Update
  24.             rstTimestamp.MoveNext
  25.             rstAcknowledgement.MoveNext
  26.             rstAgent.MoveNext
  27.             rstDetails.MoveNext
  28.         Loop Until rstTimestamp.EOF
  29.     End If
  30.     RefreshDatabaseWindow
  31. End Sub
The other option is to make sure to go to the top of the Insert Recordset before you start looping through the records:

Expand|Select|Wrap|Line Numbers
  1. rstInsert.MoveFirst
And move that recordset along with MoveNext command.

Either way, this is not a good design.

When you import your data from your text files, you need to make sure you get it all into one table--not four. Otherwise, you are asking to have mis-matched records.

If you are able to create four tables from your data import, you should be able to create one table from the data with matching records.

Hope this hepps.
Nov 25 '14 #2

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

Similar topics

9
by: Robert Wing | last post by:
I support an MS Access application in which errors are trapped using the On Error statement. Just recently, the users of this system have experienced run-time error number 3021 on a random basis. ...
2
by: Polly | last post by:
I'm trying to write the results of a query, a name, ID number, and date out to a notepad .txt file to print on a "legacy" printer. I get the output from the first 2 "write" lines over the...
0
by: Jamey | last post by:
I perused old posts for an answer to this for at least an hour, and I've found a work-around, but no definitive answer. Synopsis of the problem: On NotInList or ctl.Requery commands where a...
7
by: ruvi | last post by:
I am getting runtime error 3021 - Either EOF or BOF is true or the current record has been deleted..... I have 2 combo boxes in a form- One for the client and the other for the project. When the...
2
by: katrinkerber | last post by:
Hello, I need help to solve a runtime error that keeps reocurring every time I try to convert an Excel file into an Acess File. I have looked through many forums trying to find help, but I have...
1
by: darrel | last post by:
Hi there, can someone tell me what is wrong with my code am getting a run time error 3021: Here my code: If rs.State = adStateOpen Then rs.Close rs.Open "Select * from where ID like...
9
by: jmarcrum | last post by:
i need some help i have a table with 5 city divisions 1 = D1 2 = D2 3 = D3 4 = D4 and
0
by: bssandeshbs | last post by:
I am developing a Address Book Database Project using Visual Basic 6... When i click the delete button the data does'nt get deleted in the FrontEnd ..But it gets deleted in Database when we see in...
1
by: Ferwayne Yalung | last post by:
The code works when I logged in as a admin, teacher or student.But when i try an unregistered username, runtime error 3021 (either BOF or EOF is true, or the current record has been deleted....
1
by: karthik mca | last post by:
rs3.Move (Index) rs3!a_Votes = Val(Text2(Index).Text) + 1 rs3.Update here index is working... dont bother about that... but this is not working.. can any one edit this code???? it is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.