Error: Either BOF or EOF is True, or the current record...

[doma23]

Hi, I have 3 tables and 1 master table:
tblInfo1, tblInfo2, tblInfo3, tblMaster.
All tables have the same fields.
Two of them are [code] and [date]. This is unique combination.

I need to open and compare in VBA all three tables with Master table. If the data already exist in tblMaster, meaning [code] and [date] are the same, I just need to overwrite the record in tblMaster. If the data are new, I need to add a new record.

Thank you.

----------
P.S. I was trying something on my own, I've opened connection to 2 databases and I've tried to compare the records, but I don't know the syntax.
My logic was that it should be something like this:

rs.Open "tblInfo1"
rs.Open "tblMaster"

Do While "tblInfo1" EOF
rs.MoveFirst
If rs![code] = rs(tblMaster)![code] and rs![date] = rs(tblMaster)![date] Then
rs(tblMaster)![data1] = rs![data1]
rs(tblMaster)![data2] = rs![data2]
rs.Update
Else
rs.AddNew
rs(tblMaster)![data1] = rs![data1]
rs(tblMaster)![data2] = rs![data2]
rs.Update
End If
Loop

This is just an idea of the code logic.

[doma23]

This down is the code I come up with.
However, now I'm receiving next error message:
"Either BOF or EOF is True, or the current record has been deleted."
When I go to debug it highlights the line 33:
"rsMaster![sheet_name] = rsInfo1![sheet_name]"

Private Sub Comando2_Click()
 
Dim cn As ADODB.Connection
 
Dim rsMaster As New ADODB.Recordset
Dim rsInfo1 As New ADODB.Recordset
 
 
Dim sCriteria As String
 
Set cn = CurrentProject.Connection
Set rsMaster = New ADODB.Recordset
Set rsInfo1 = New ADODB.Recordset
 
rsMaster.Open "tblMaster", cn, adOpenDynamic, adLockOptimistic
rsInfo1.Open "tblInfo1", cn, adOpenDynamic, adLockOptimistic
 
rsMaster.MoveFirst
 
rsInfo1.MoveFirst
 
If Not rsInfo1.EOF Then
 
    Do While Not rsInfo1.EOF
        'check if it exist in the rsMaster
        'if exist then rs.filter
 
        If DCount("*", "[tblMaster]", _
            "(([series_code]='" & rsInfo1![series_code] & "') AND (" & "[date]=#" & Format(rsInfo1![Date], "mm dd yyyy") & "#))") > 0 Then
 
            sCriteria = "(([series_code]='" & rsInfo1![series_code] & "') AND (" & "[date]=#" & Format(rsInfo1![Date], "mm dd yyyy") & "#))"
            rsMaster.Filter = sCriteria
            rsMaster![sheet_name] = rsInfo1![sheet_name]
            rsMaster![Value] = rsInfo1![Value]
            rsMaster.Update
        Else
            rsMaster.AddNew
            rsMaster![series_code] = rsInfo1![series_code]
            rsMaster![sheet_name] = rsInfo1![sheet_name]
            rsMaster![Date] = rsInfo1![Date]
            rsMaster![Value] = rsInfo1![Value]
            rsMaster.Update
        End If
 
    rsInfo1.MoveNext
    Loop
 
Else
    MsgBox ("No record found!")
End If
 
 
rsMaster.Close
rsInfo1.Close
cn.Close
Set rsMaster = Nothing
Set cn = Nothing
 
MsgBox "Data have been saved!", vbOKOnly
 
End Sub

[Mariostg]

You must test for both EOF and BOF. So on line 24 have:
Do While Not rsInfo1.EOF And Not rsInfo1.BOF

And you can get rid of the IF clause on line 22.

[doma23]

Unfortunately, that doesn't solve the problem. :(
Still showing run-time error 3021:
"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

Actually in the beginning I have one dummy record in tblMaster, and after I click on the button for the first time it works good, and it adds new records. It copies all the records from tblInfo. But then, when I click again the second time it brings up this message.

The problem is for sure in line 33, in the "rsMaster![sheet_name] = ".
This code is the cause for this message.

[ADezii]

Just subscribing for now, will return later.

[Mariostg]

Yes more that likely. In that case you filter may have something to do with it. Can you run your filter with the appropriate criteria by itself and see what is left in the rsMaster table. There might be no record left...

[ADezii]

I should have a solution shortly using a slightly different approach, namely: an ADODB Recordset in combination with Dynamic SQL Statement Execution, thereby eliminating the Recordset Filter.

[ADezii]

Simply pass to the Function the Name of the Table that you wish to compare to tblMaster, and the Code should do the rest. I'll post the Code as well as the sample Calls, but I strongly suggest that you download the Attachment that I have made available to you, in order to get a much clearer picture of the Logic involved. All Fields are TEXT except [Date], [series_code] and [Date] comprise a 'Unique' Index, and the same Fields are consistent in Name and Data Types across all Tables. Modify the Values in tblInfo1, then see what happens to tblMaster. Have fun, and should you have any questions, feel free to ask.

1. Function Definition:
   

   Public Function fCompareData(strTableName As String)
   Dim rstCompare As ADODB.Recordset
   Dim strADD As String
   Dim strUpd As String
    
   Set rstCompare = New ADODB.Recordset
    
   DoCmd.Hourglass True
    
   'Kept distinct for the sake of clarity
   With rstCompare
     .Source = strTableName
     .ActiveConnection = CurrentProject.Connection
     .CursorType = adOpenDynamic
     .LockType = adLockOptimistic
       .Open
   End With
    
   'Let's test every Record in strTableName, and see if the Unique Combination of [Date] and [series_code]
   'exists in tblMaster. If a Match exists, overwrite [sheet_name] and [Value] in the Master
   'Table with the Values in the Recordset. Should no Match exist, then Add the New Record to tblMaster.
   With rstCompare
     Do While Not .EOF
       If DCount("*", "tblMaster", "[series_code] = '" & ![series_code] & "' AND [Date] = #" & ![Date] & "#") > 0 Then
         strUpd = "UPDATE tblMaster Set [sheet_name] = '" & ![sheet_name] & "', [Value] = '" & ![Value] & "' WHERE " & _
                  "[Date] = #" & ![Date] & "# AND [series_code] = '" & ![series_code] & "';"
                     CurrentDb.Execute strUpd, dbFailOnError
                       Debug.Print "UPDATE ==> " & ![Date] & " | " & ![series_code]
       Else        '[series_code] <==> [Date] combination doesn't exist, so Add a New Record
         strADD = "INSERT INTO tblMaster ([sheet_name], [Value], [Date], [series_code]) VALUES ('" & _
                  ![sheet_name] & "', '" & ![Value] & "', #" & ![Date] & "#, '" & ![series_code] & "')"
                     CurrentDb.Execute strADD, dbFailOnError
                       Debug.Print "ADD ==> " & ![Date] & " | " & ![series_code]
       End If
         .MoveNext
     Loop
   End With
    
   DoCmd.Hourglass False
    
   rstCompare.Close
   Set rstCompare = Nothing
   End Function
    

2. Sample Call(s) to the Function:
   

   Private Sub cmdTest_Click()
   On Error GoTo Err_cmdTest_Click
    
   Call fCompareData("tblInfo1")
               'OR
   Call fCompareData("tblInfo2")
               'OR
   Call fCompareData("tblInfo3")
    
   Exit_cmdTest_Click:
     Exit Sub
    
   Err_cmdTest_Click:
     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
       DoCmd.Hourglass False
         Resume Exit_cmdTest_Click
   End Sub

3. Download the Attachment below.