Attempting to run code with a RecordSet but get Runtime Error 3021

[OldCityCat]

To begin let me just say that I'm aware that this piece of code is not very efficient, It's my first attempt at using Recordse


And I'm sure there is a better way of getting this task done, and I'm completely open to any and all suggestions.
But first please tell me why I can step though this code line by line without getting the Runtime error 3021 but
if I try running it via F5 I get an immediate error and debug takes me to bolded If statement.

Sub CustLookup()

Dim rs As DAO.Recordset

Dim rs2 As DAO.Recordset2

Set rs = CurrentDb.OpenRecordset("Select * From DailySalesOrds " & " Order By [Customer]")

Set rs2 = CurrentDb.OpenRecordset("Select * from InFlowUniqueCustLookup " & " Order by [CustNameKey]")

If rs.EOF Then

MsgBox "No Records"

Exit Sub

Else

  rs.MoveFirst

  rs2.MoveFirst

End If


Do

If rs.Fields("CustId") = rs2.Fields("CustId") Then   ' Error Line

GoTo NxtIf

       Else

           GoTo NxtRs2

NxtIf:

  If rs.Fields("Customer") = rs2.Fields("CustNameKey") Then

       Else

           GoTo NxtRs2

  End If

       If rs.Fields("Zip") = rs2.Fields("UniqueKey") Then

         End If

rs.Edit

rs.Fields("Customer") = rs2.Fields("InFlowCustName")

rs.Update

rs2.MoveFirst

NxtRS:

   rs.MoveNext

NxtRs2:

   rs2.MoveNext

End If

Loop Until rs.EOF

rs.MoveNext

'Close and Cleanup

rs.Close

rs2.Close

Set rs = Nothing

Set rs2 = Nothing

Set db = Nothing

End Sub

Thank you in advance for any and all comments
OldCityCat

[RayMilhon]

From what I could tell from what you posted this is what you're trying to do.

Sub CustLookup()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * From DailySalesOrds Inner join INFlowuniquecustlookup on DailySalesords.custid = inflowuniquecustlookup.custid" & _
     " Order By [Customer]")
Do until rs.eof
      rs.Edit
      rs.Fields("Customer") = rs.Fields("InFlowCustName")
      rs.Update
      rs.MoveNext
Loop 
'Close and Cleanup
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

However, looking at this tells me your Data is not normalized Since Customer Name is in both tables. You need to post the data structure of your database so we can help you with the data design it will make Querying and updating your data much easier.

[Micron]

Agree that some explanation of the data layout might help although I wouldn't necessarily say it's not normalized. You could be trying to combine 2 tables but I didn't try to figure out exactly what you're up to because there are issues with the code. F'rinstance, I don't believe there's any such thing as a Recordset2. The message you get at high speed means there's no current record, which indicates to me that an attempt of an operation might be occurring before a needed record move, unless you're stepping through it.

Better to tell us what the task is in order to know if there's a better way.

[pbaldy]

I don't believe there's any such thing as a Recordset2.

Yes, there is.

[Micron]

Just not in any db I've ever created

[ssanfu]

From HELP:

Access Developer Reference
Recordset2 Object

A Recordset2 object represents the records in a base table or the records that result from running a query.
Remarks

A Recordset2 object is contains all of the same properties and methods as the Recordset object. The Recordset2 object contains a new property, ParentRecordset, that support multi-valued field types.

[OldCityCat]

Thanks to all for your prompt responses.

I should explain the project a little more, The subroutine I posted is one piece of a Data Import > Data cleanup > Data export project, So I no need to store data long term.
I'm working with two table as you might have noticed, The DailySalesOrds table is the imported data, and InFlowUniqueCustLookup is a static lookup table.
Because the DailySalesOrds data have the same customer name with a slight variation such as a "-" or "/.
I need to update DailySalesOrds with a standardize customer name from the lookup table before exporting.

I have three qualifiers to get the correct customer from the lookup table;
DailySalesOrds!CustID = InFlowUniqueCustLookup!CustID
DailySalesOrds!Customer = InFlowUniqueCustLookup!CustNameKey
DailySalesOrds!Zip = InFlowUniqueCustLookup!UniqueKey

If all match then DailySalesOrds!Customer is updated with InFlowUniqueCustLookup!InFlowCustName,
Then Move to next Sales Order Record, and this is where I have minimal experience, so started with a Recordset.
The CustLookup sub I posted did work if run line by line, but would get the run-time error 3021 if executed via F5. Still would like to know what is causing this error.

I know there's a more efficient way of looping thru the 371 records in the lookup table for every record in the sales order table.
I thought about loading the Lookup table into a array but was unsure how to query it.

I hope this helps, If not please let me know and I will try to explain myself better.

OldCityCat

[Micron]

I think you could make this simpler by using queries instead of record sets. Just run one (or more if necessary) updates against the table without checking that it is needed. If the values are already correct, the update won't change anything. If they're not, they will. If you really want to know what causes the error, I think you'll have to post a compacted/zipped copy of your db for analysis as this sort of thing can be hard to find. That assumes you'd want to know even if a simple update solves the problem.

[ssanfu]

Still would like to know what is causing this error.

The Run time error 3021 is the error number for "No current record", probably from the last "rs.MoveNext" line in your code.


Here is my take on the code:

Sub CustLookup()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSQL As String

    Set db = CurrentDb

    sSQL = "SELECT CustID, Customer, Zip"
    sSQL = sSQL & " FROM DailySalesOrds"
    sSQL = sSQL & " ORDER BY [Customer];"
    '    Debug.Print sSQL
    Set rs = db.OpenRecordset(sSQL)

    If rs.EOF Then
        MsgBox "No Records"
    Else
        rs.MoveLast
        rs.MoveFirst
        Do
            sSQL = "SELECT CustID, CustNameKey, UniqueKey"
            sSQL = sSQL & " FROM InFlowUniqueCustLookup"
            sSQL = sSQL & " WHERE CustID = '" & rs!CustID & "';"
            '    Debug.Print sSQL
            Set rs2 = db.OpenRecordset(sSQL)

            If Not rs2.BOF And Not rs2.EOF Then
                rs2.MoveLast
                rs2.MoveFirst
                'we already know that rs!CustID = rs2!CustID, so check the other two fields
                If (rs.Fields("Customer") = rs2.Fields("CustNameKey")) And _
                   (rs.Fields("Zip") = rs2.Fields("UniqueKey")) Then

                    rs.Edit
                    rs.Fields("Customer") = rs2.Fields("InFlowCustName")
                    rs.Update

                    rs2.Close
                    rs.MoveNext
                End If
            End If
        Loop Until rs.EOF
    End If

    'Close and Cleanup
    On Error Resume Next
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub

[OldCityCat]

Steve,
First of all thanks for your take on my code, appears to be exactly what I need.
And here comes the "But", Let me just say I know you have no way of testing without my data.
That said , As I step thru the code all is good until;

If (rs.Fields("Customer") =rs2.Fields("CustNameKey")) And (rs.Fields("Zip") = rs2.Fields("UniqueKey")) Then

As I cycle thru the records rs2 is not stepping to the next record when there's not a match.
I've been experimenting with rs2.MoveNext in different locations but can't seem to get it right.


OldCityCat

[ssanfu]

I'm not sure I understand and won't without the tables and some data.

I'm still ........... but try this: (added lines are blue)

Option Compare Database
Option Explicit

Sub CustLookup()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSQL As String

    Set db = CurrentDb

    sSQL = "SELECT CustID, Customer, Zip"
    sSQL = sSQL & " FROM DailySalesOrds"
    sSQL = sSQL & " ORDER BY [Customer];"
    '    Debug.Print sSQL
    Set rs = db.OpenRecordset(sSQL)

    If rs.EOF Then
        MsgBox "No Records"
    Else
        rs.MoveLast
        rs.MoveFirst
        Do
            sSQL = "SELECT CustID, CustNameKey, UniqueKey"
            sSQL = sSQL & " FROM InFlowUniqueCustLookup"
            sSQL = sSQL & " WHERE CustID = '" & rs!CustID & "';"
            '    Debug.Print sSQL
            Set rs2 = db.OpenRecordset(sSQL)

            If Not rs2.BOF And Not rs2.EOF Then
                rs2.MoveLast
                rs2.MoveFirst
                'we already know that rs!CustID = rs2!CustID, so check the other two fields
                Do While Not rs2.EOF
                    If (rs.Fields("Customer") = rs2.Fields("CustNameKey")) And _
                       (rs.Fields("Zip") = rs2.Fields("UniqueKey")) Then

                        rs.Edit
                        rs.Fields("Customer") = rs2.Fields("InFlowCustName")
                        rs.Update

                        rs2.MoveNext
                    Loop
                    rs2.Close
                End If
            End If
            rs.MoveNext   '<<-- moved to here
        Loop Until rs.EOF
    End If

    'Close and Cleanup
    On Error Resume Next
    rs.Close
    rs2.Close
    Set rs = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub

What are the field types for:
Customer
CustNameKey
Zip
UniqueKey

Are they all text type fields?


I think adding those fields to the SQL for rs2 might be easier.
Something like:

Option Compare Database
Option Explicit

Sub CustLookup()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sSQL As String

    Set db = CurrentDb

    sSQL = "SELECT CustID, Customer, Zip"
    sSQL = sSQL & " FROM DailySalesOrds"
    sSQL = sSQL & " ORDER BY [Customer];"
    '    Debug.Print sSQL
    Set rs = db.OpenRecordset(sSQL)

    If rs.EOF Then
        MsgBox "No Records"
    Else
        rs.MoveLast
        rs.MoveFirst
        Do
            sSQL = "SELECT CustID, CustNameKey, UniqueKey"
            sSQL = sSQL & " FROM InFlowUniqueCustLookup"
            sSQL = sSQL & " WHERE CustID = '" & rs!CustID & "'"
            sSQL = sSQL & " AND [CustNameKey] = '" & rs.Fields("Customer") & "'"
            sSQL = sSQL & " AND [UniqueKey] = '" & rs.Fields("Zip") & "';"
            '    Debug.Print sSQL
            Set rs2 = db.OpenRecordset(sSQL)

            If Not rs2.BOF And Not rs2.EOF Then
                rs2.MoveLast
                rs2.MoveFirst

                Do While Not rs2.EOF
                    rs.Edit
                    rs.Fields("Customer") = rs2.Fields("InFlowCustName")
                    rs.Update

                    rs2.MoveNext
                Loop
                rs2.Close
            End If
        End If
        rs.MoveNext
    Loop Until rs.EOF
End If

'Close and Cleanup
On Error Resume Next
rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub