How do I solve this run-time error 3021?

[shawnnnnnnn]

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?

Private Sub Command9_Click()
    Dim dbs As DAO.Database
    Dim rstTimestamp As DAO.Recordset
    Dim rstAcknowledgement As DAO.Recordset
    Dim rstAgent As DAO.Recordset
    Dim rstDetails As DAO.Recordset
    Dim rstInsert As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rstTimestamp = dbs.OpenRecordset("GMT+8", dbOpenDynaset)
    Set rstAcknowledgement = dbs.OpenRecordset("Acknowledgement", dbOpenDynaset)
    Set rstAgent = dbs.OpenRecordset("AgentName", dbOpenDynaset)
    Set rstDetails = dbs.OpenRecordset("Table2", dbOpenDynaset)
    Set rstInsert = dbs.OpenRecordset("Final", dbOpenDynaset)
 
    If Not rstTimestamp.EOF Then
        Do
            rstInsert.AddNew
            rstInsert![Timestamp] = rstTimestamp![Timestamp (GMT+8)]
            rstInsert.Update
            rstTimestamp.MoveNext
        Loop Until rstTimestamp.EOF
    End If
    RefreshDatabaseWindow
    If Not rstAcknowledgement.EOF Then
        Do
            rstInsert.Edit
            rstInsert![SNOCAcknowledged] = rstAcknowledgement![Field2]
            rstInsert.Update
            rstAcknowledgement.MoveNext
        Loop Until rstAcknowledgement.EOF
    End If
    RefreshDatabaseWindow
    If Not rstAgent.EOF Then
        Do
            rstInsert.Edit
            rstInsert![AgentName] = rstAgent![Field2]
            rstInsert.Update
            rstAgent.MoveNext
        Loop Until rstAgent.EOF
    End If
    RefreshDatabaseWindow
    If Not rstDetails.EOF Then
        Do
            rstInsert.Edit
            rstInsert![Details] = rstDetails![Field5]
            rstInsert.Update
            rstDetails.MoveNext
        Loop Until rstDetails.EOF
    End If
 
End Sub
 

Ps, I have attached a picture showing my final table's column names.

[twinnyfo]

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:

Private Sub Command9_Click()
    Dim dbs As DAO.Database
    Dim rstTimestamp As DAO.Recordset
    Dim rstAcknowledgement As DAO.Recordset
    Dim rstAgent As DAO.Recordset
    Dim rstDetails As DAO.Recordset
    Dim rstInsert As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rstTimestamp = dbs.OpenRecordset("GMT+8", dbOpenDynaset)
    Set rstAcknowledgement = dbs.OpenRecordset("Acknowledgement", dbOpenDynaset)
    Set rstAgent = dbs.OpenRecordset("AgentName", dbOpenDynaset)
    Set rstDetails = dbs.OpenRecordset("Table2", dbOpenDynaset)
    Set rstInsert = dbs.OpenRecordset("Final", dbOpenDynaset)
 
    If Not rstTimestamp.EOF Then
        Do
            rstInsert.AddNew
            rstInsert![Timestamp] = rstTimestamp![Timestamp (GMT+8)]
            rstInsert![SNOCAcknowledged] = rstAcknowledgement![Field2]
            rstInsert![AgentName] = rstAgent![Field2]
            rstInsert![Details] = rstDetails![Field5]
            rstInsert.Update
            rstTimestamp.MoveNext
            rstAcknowledgement.MoveNext
            rstAgent.MoveNext
            rstDetails.MoveNext
        Loop Until rstTimestamp.EOF
    End If
    RefreshDatabaseWindow
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:

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.