Recordset data not updating after MoveNext

**Glenley** · 12-09-2022, 08:29 AM

I am implementing a Transaction Table in order to start keeping better financial records at our office. We've already had over 3000 transactions since implementing our database a little over a year ago so I'm attempting to append the price data into our Transaction Table before we start recording current transactions. I'm using 3 recordsets to get the TestID from the TestT.TestID and the AmountOwed from the TestTypeT.TestTypePrice and put them into TransactionT.TestID and TransactionT.AmountOwed. Appending the TestID is working fine but the TestTypePrice is not updating as the rsTest recordset moves to the next record. It keeps returning the price from the first record so I keep getting $45.00 on every record. What am I doing wrong here? The line with "Status" is just a textbox I use that works like a msgbox on my CodeTest Form

Code:

    Dim rsTest As Recordset, rsTrans As Recordset, rsType As Recordset
    
    Set rsTest = CurrentDb.OpenRecordset("SELECT * FROM TestT", dbOpenSnapshot)
    Set rsTrans = CurrentDb.OpenRecordset("Select * FROM TransactionT", dbOpenDynaset)
    Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot)


    On Error GoTo ERR_MSG
    StatusBox = ""
    Do Until rsTest.EOF
        rsTrans.AddNew
        rsTrans!TestID = rsTest!TestID
        rsTrans!AmountOwed = rsType!TestTypePrice
        Status  rsTrans!TestID & " " & rsTrans!AmountOwed
        rsTrans.Update
        rsTest.MoveNext
    Loop
    
KillSub:


    rsType.Close
    rsTrans.Close
    rsTest.Close
    Set rsType = Nothing
    Set rsTrans = Nothing
    Set rsTest = Nothing
    Exit Sub
    
ERR_MSG:
    Status "Error #" & Err.Number & " " & Err.Description
    GoTo KillSub

**Welshgasman** · 12-09-2022, 08:33 AM

You need a / before CODE in the last tag.?
Hopefully that will then show your indentation.

Regardless, if you walk through your code line by line, you should be able to spot the issue?

Issue is, you are not moving from the first record for TestType?

**Glenley** · 12-09-2022, 09:28 AM

I think I fixed the code section of the post. I tried rsType.MoveNext after rsTest.MoveNext and it returned a Error#3021 no current record

**Welshgasman** · 12-09-2022, 09:42 AM

Originally Posted by Glenley

I think I fixed the code section of the post. I tried rsType.MoveNext after rsTest.MoveNext and it returned a Error#3021 no current record

So how many rsType records do you expect for each rsTest.TestType?, you have to account for EOF on a record movement forward and BOF on any backward movement.

**Gicu** · 12-09-2022, 10:01 AM

You need to move the third Set line (for the rsType) inside the Do Loop (before the AddNew) so it can refresh the value based on the new record.

Cheers,

**Glenley** · 12-09-2022, 11:36 AM

Sorry for the delayed response, It's a busy day.

WelshGasMan: I'm returning only the TestTypePrice from rsType but I'm using the rsType!TestID for the WHERE Condition. I'm using this data to APPEND about 3200 records into rsTrans, which is my Transaction Table

Gicu: So if I understand you correctly I need to manually move the cursor within the rsType recordset? Something like this maybe:

Code:

    Set rsTest = CurrentDb.OpenRecordset("SELECT * FROM TestT", dbOpenSnapshot)
    Set rsTrans = CurrentDb.OpenRecordset("Select * FROM TransactionT", dbOpenDynaset)
    Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot)


    On Error GoTo ERR_MSG
    StatusBox = ""
    Do Until rsTest.EOF
        While Not rsType.EOF Or rsType.BOF
            rsTrans.AddNew
            rsTrans!TestID = rsTest!TestID
            rsTrans!AmountOwed = rsType!TestTypePrice
            Status rsTrans!TransactionID & " " & rsTrans!TestID & " " & rsTrans!AmountOwed
            rsTrans.Update
            rsTest.MoveNext
        Wend
    Loop

**Gicu** · 12-09-2022, 11:45 AM

Not sure where you declare and\or use the Status variable, but this is what I meant:

Code:

    Set rsTest = CurrentDb.OpenRecordset("SELECT * FROM TestT", dbOpenSnapshot)
    Set rsTrans = CurrentDb.OpenRecordset("Select * FROM TransactionT", dbOpenDynaset)
    'Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot) 'THIS WILL ALWAYS BE THE BASED ON THE FIRST rsTest record




    On Error GoTo ERR_MSG
    StatusBox = ""
    Do Until rsTest.EOF
        While Not rsType.EOF Or rsType.BOF
            Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot) 'now will change with the movenext
            rsTrans.AddNew
            rsTrans!TestID = rsTest!TestID
            rsTrans!AmountOwed = rsType!TestTypePrice
            Status rsTrans!TransactionID & " " & rsTrans!TestID & " " & rsTrans!AmountOwed
            rsTrans.Update
            rsTest.MoveNext
        Wend
    Loop

**Welshgasman** · 12-09-2022, 11:45 AM

Originally Posted by Glenley

Sorry for the delayed response, It's a busy day.

WelshGasMan: I'm returning only the TestTypePrice from rsType but I'm using the rsType!TestID for the WHERE Condition. I'm using this data to APPEND about 3200 records into rsTrans, which is my Transaction Table

Gicu: So if I understand you correctly I need to manually move the cursor within the rsType recordset? Something like this maybe:

Code:

    Set rsTest = CurrentDb.OpenRecordset("SELECT * FROM TestT", dbOpenSnapshot)
    Set rsTrans = CurrentDb.OpenRecordset("Select * FROM TransactionT", dbOpenDynaset)
    Set rsType = CurrentDb.OpenRecordset("SELECT * FROM TestTypeT WHERE TestTypeID=" & rsTest!TestTypeID, dbOpenSnapshot)


    On Error GoTo ERR_MSG
    StatusBox = ""
    Do Until rsTest.EOF
        While Not rsType.EOF Or rsType.BOF
            rsTrans.AddNew
            rsTrans!TestID = rsTest!TestID
            rsTrans!AmountOwed = rsType!TestTypePrice
            Status rsTrans!TransactionID & " " & rsTrans!TestID & " " & rsTrans!AmountOwed
            rsTrans.Update
            rsTest.MoveNext
        Wend
    Loop

You are NOT moving off the first record in rsType, so will ALWAYS get the value of the first record?
If you try to move off that record and there is no more records, you will get that error.

So you have to determine if you have more records to locate in the rsType recordset.?

How you know which is which is beyond me> What determines that the second record in RsTye is for the second record in rsTest, or any other record for that matter?

You might get better results if you can join them in some way with a query and use that?

**Micron** · 12-09-2022, 11:50 AM

Methinks not. I don't see a move for the inner recordset. I've never seen code that loops over one recordset while affecting values in an inner recordset. This all seems so complicated. Why can't you just use an append query? Are you not just appending whatever prices (or whatever values) from one form record? Or are you appending every price record?

EDIT
I think I mis-read the code. Still looks like a round about way of doing things though.

**Gicu** · 12-09-2022, 11:53 AM

The rsTrans doesn't need to move as it is only used to AddNew.

Cheers,

**Micron** · 12-09-2022, 11:55 AM

Originally Posted by Gicu

The rsTrans doesn't need to move as it is only used to AddNew.

Cheers,

Yeah, I edited because of that. I'm going to bow out. Posts crossing too much and that's on me.

**Glenley** · 12-09-2022, 11:58 AM

Gicu settting the rsType rs within the loop without using the While Loop worked. The line with Status works like an active message box on my form. If you create an oversized TextBox named StatusBox on your form and create a sub:

Code:

Public Sub Status(S As String)


    StatusBox = S & vbNewLine & StatusBox
    DoEvents


End Sub

It works just like Debug.Print. This is amazing for me when testing out code and need to visualize what's happening

**Glenley** · 12-09-2022, 12:00 PM

I learned this trick from Richard @ Access Learning Zone. That guy is awesome

**Gicu** · 12-09-2022, 12:07 PM

Happy to see you got it going!

Cheers,

**Glenley** · 12-09-2022, 12:11 PM

Micron, I don't like to use big action queries because our tables are on SharePoint and SP doesn't handle those very well and recordsets seem to be much faster. If it's just affecting a small amount of data I usually will use an action Query. I'm also just starting to understand recordsets (kind of a rookie) so I've been using them a lot in order to learn them better. I've only been using VBA for a little over a year.

Thanks everybody for the help!!!

Recordset data not updating after MoveNext

Thread Tools

Recordset data not updating after MoveNext

Similar Threads

Updating Recordset

Updating a RecordSet Clone

Trying to add data to table field but records do not change - problem with movenext?

Updating Recordset with VBA Code

Updating one Recordset using another Recordset

Tags for this Thread

Posting Permissions