updating table in VBA

[Timtropolis]

Greetings,

I'm currently using .addnew and .edit methods to update a table in VBA.
I have a flag setup so that if a new id comes in , then I use the .Addnew method to add a record to my table.

Some of the records are update records, which need to have data pulled from certain fields, then updated into the record that was added. So I set up a routine that uses the .Edit to accomplish that.

However, I'm receiving "No current record" error when the code hits rst.edit.
I'm not sure why this is occuring and was wondering if someone could point me in the right direction.

I've included the code below to help.

    Do While Not rst.EOF
        DayName = Format(rst!EarnedDate, "dddd")

        If rst!EarnedDate <= rst3!StartDate And X = 0 Then
            rst2.AddNew
            rst2!SSN = rst!SSN
            rst2!startofweekdate = rst3!StartDate - 7
            rst2!weeknumber = rst3!week
            
            Set db1 = CurrentDb()
            Set tdf = db1.TableDefs("DSNYWorkingTable")
            intNumFields = tdf.Fields.Count - 1
            ReDim avarFields(intNumFields)
            For Each fld In tdf.Fields
                If DayName = fld.name Then
                    Select Case DayName
                        Case Is = "sunday"
                            rst2!SUNDAY = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        'rst2(" & dayname & ") = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "monday"
                            rst2!monday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "tuesday"
                            rst2!tuesday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "wednesday"
                            rst2!Wednesday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "thursday"
                            rst2!thursday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "friday"
                            rst2!friday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "saturday"
                            rst2!saturday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                    End Select
                    Exit For
                End If
            Next fld
            rst2.Update
            
            rst.MoveNext
            rst3.MoveFirst
            X = 1
            
        ElseIf rst!EarnedDate <= rst3!StartDate And X = 1 Then
            rst2.Edit [B][COLOR="Red"]<--- Problem occuring here[/COLOR][/B]
            Set db1 = CurrentDb()
            Set tdf = db1.TableDefs("DSNYWorkingTable")
            intNumFields = tdf.Fields.Count - 1
            ReDim avarFields(intNumFields)
            For Each fld In tdf.Fields
                If DayName = fld.name Then
                    Select Case DayName
                        Case Is = "sunday"
                            rst2!SUNDAY = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        'rst2(" & dayname & ") = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "monday"
                            rst2!monday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "tuesday"
                            rst2!tuesday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "wednesday"
                            rst2!Wednesday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "thursday"
                            rst2!thursday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "friday"
                            rst2!friday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                        Case Is = "saturday"
                            rst2!saturday = rst!SSN & " - " & rst!EarnedDate & " - " & rst!LeaveMins
                    End Select
                    Exit For
                End If
            Next fld
        
            rst2.Update
            rst.MoveNext
            rst3.MoveFirst
            If rst!EarnedDate > Dateholder Then
                Dateholder = rst!EarnedDate
                X = 0
            End If
        Else
            rst3.MoveNext
        End If

TIA,
Tim

 

[RuralGuy]

You may need to use the debugger to watch your variables and see what is happening. Just a personal note: having 7 fields in your table named for the days of the week strikes me as a possible normalization problem. As for your error, maybe if you post all of the procedure it would be more obvious what is causing the issue. You may also want to consider posting your entire zipped up db with any sensitive data removed.

 

[pbaldy]

The right direction is "up".

We would need to see the code above this to see how rst2 was set. From the error, it is not on a specific record.

 

[RuralGuy]

Hey Paul!

Enjoying the snow? The wife is in Vegas right now.

Allan

 

[pbaldy]

Hey Allan!

Man, it's nippy in Reno right now, and I heard from my Vegas coworkers that they got snow in Vegas, which is pretty unusual (we get snow here all the time). Your wife is probably laughing at all the idiots down there that don't know how to drive in the snow.

 

[Timtropolis]

LOL @ snow... were gettin our first taste tomorrow. Can ya say "day off"?

Anyways guys, much thanks for your replies... I know the code is convoluted but this is what I inherited and just trying to make it work, which I can say, I have! I basically just got rid of the .edit portion and combined it in the .addnew process so that got rid of the problem.

Thanks again and enjoy the white stuff. lol
Tim

 

[RuralGuy]

Thanks for posting back with your success Tim. Merry Christmas

Paul: For some reason I thought you were in Vegas. Sorry. And you are right about my wife. She said no body has scrapers (like she does). They don't even sell them there. I said she should have rented hers out and made some money.

 

[pbaldy]

No problem, Allan. I only have Nevada in my profile, so it's not like you should have known. I talked to a Vegas coworker today and she confirmed how incompetent the Vegas drivers were in the snow. I hope your wife is having fun! We've got a great view of the mountains here, and they're completely obscured. Looks like another round is getting ready to hit.

And sadly, around here snow <> day off.