Run Time error 91 Object Variable or With Block variable not set

[atrium]

I keep on getting the above error. The rs.MoveNext is highlighted when the error occurs. I'm extracting a selection of related records from a much larger file onto DdTransTemp and then going through that DdTransTemp file and processing certain transactions that full into a From and To date range.

All of the Dims are OK

Using Access 2016

I hope someone can help

     Dim IntDaysCount As Integer
      Dim lUserID As Long
      Dim db As DAO.Database
      Dim rs As DAO.Recordset
      DBEngine.SetOption dbMaxLocksPerFile, 1000000
      On Error GoTo ErrTrap1
      TaskCompletedFld = 0
      IntDaysCount = 0
      Me.QryFile = strQryFile
      lUserID = Me.UserIdFld.Value
      Me.DaysOfIntFld = 0
      PeriodInterestFld = 0
      RecCounter = 1
      PayPeriodDateFld = Me.FirstDateFld
      Set db = CurrentDb
      Set rs = db.OpenRecordset("DdTransTemp", dbOpenTable)
      If Not (rs.EOF And rs.BOF) Then       '----- Is there any transactions in this period
         rs.MoveFirst ' Unnecessary in this case, but still a good habit
         Do While Not rs.EOF
            Me.DdSchedTransIdFld = rs![DdSchedTransId]
            Me.TransCodeFld = rs![TransCode]
            Me.DdSchedIdFld = rs![DdSchedId]
            Me.MatterIdFld = rs![MatterId]
            Me.MatterTitleFld = rs![MatterTitle]
            Me.MatterNoFld = rs![MatterNo]
            Me.DueDateFld = rs![DueDate]
            Me.DueAmountFld = rs![DueAmount]
            Me.DDAmountFld = rs![DDAmount]
            Me.DaysOfIntFld = rs![DaysOfInt]
            Me.TransStatusFld = rs![Status]
            Me.ABADateFld = rs![ABADate]
            Me.TransDateFld = rs![TransDate]
            Me.RefNoFld = rs![RefNo]
            'MsgBox "Current Record number = " & DdSchedTransIdFld & " TransCode  = " & TransCodeFld & "  DDSchedIdFld  = " & DdSchedIdFld & "  Amount = " & Me.DueAmountFld
            ' ----- Process this transaction ------------------------
            ' ----------------------------------I need to apply the Interest for each day of the period
            If Me.DueDateFld >= Me.PayPeriodDateFld And Me.DueDateFld <= Me.LastDateFld Then
               Select Case Me.TransCodeFld
                  Case 13, 50, 20      ' ----- 13 = Direct debit, 50 = Direct Credit,  20 = Manual Payment
                     RunningDebtTotal = Round((RunningDebtTotal - Me.DueAmountFld), 2)                    ' Update the total debt at that time
                     '----DueDateFld updated here
                     'If Me.TransCodeFld = 13 Then
                     '   Me.IntDueDateFld = Me.DueDateFld                 ' Me.DueDateFld
                     ' End If
                  Case 30, 35, 40, 60      '------ 30 = Dis Hon, 35 = Penalty Fee, 40 = Interest, 60 = Transaction Fee
                     RunningDebtTotal = RunningDebtTotal + Me.DueAmountFld                     ' Update the total debt at that time
               End Select
            End If
            ' ------- Calculate the Interest daily and accumulate it for the Pay Period, then write the Int transaction
            Me.PayPeriodDateFld = Me.PayPeriodDateFld + 1
            Me.PeriodInterestFld = Round(Me.PeriodInterestFld + (RunningDebtTotal * (((Me.InterestRateFld / 100)) / 365) * 1), 2)
            RunningDebtTotal = Round(RunningDebtTotal + (RunningDebtTotal * (((Me.InterestRateFld / 100)) / 365) * 1), 2)
            IntDaysCount = IntDaysCount + 1
JumpToHere1:
            RecCounter = RecCounter + 1
            rs.MoveNext
         Loop

 

[June7]

Have you step debugged? Do you get this error every time?

When would JumpToHere1: come into play? Perhaps:

If Not rs Is Nothing Then rs.MoveNext

You have variables that are not declared in this procedure - are they globals?

 

[Minty]

Possibly related this line
rs.EOF And rs.BOF
Should be an Or

 

[arnelgp]

if both .EOF and .BOF are True, then there is no record in the recordset, the logic is correct.

 

[sonic8]

The error happens when the problematic location in code is reached by jumping to the JumpToHere1 mark from a location outside the quoted code area.

 

[arnelgp]

if we can see the Whole code, coz what on post#1, there is no code that is Branching to that Label.

 

[Pat Hartman]

You are missing the End If for this If

If Not (rs.EOF And rs.BOF) Then '----- Is there any transactions in this period

 

[The_Doc_Man]

The best solution is as noted by June7 - set a breakpoint in this code and single-step it to assure that the RS variable actually becomes instantiated. If it is not, the Debug.Print of RS (uninstantiated) would be "Nothing" and you would thus know that the .OpenRecordset method failed.

Your complaint "The error happens when the problematic location in code is reached by jumping to the JumpToHere1 mark from a location outside the quoted code area." leads to Arnel's comments. You clearly didn't get there from anything you showed us yet in order for your comment to even make any sense, there HAS to be exactly such a GoTo somewhere. Otherwise, how would you ever get that error? If the GoTo comes from a spot where you completely bypassed your .OpenRecordset then that would lead to the error.

Your incorrect closure of the If Not ... statement that Pat described is another error that eventually needs to be corrected. Because of the way VBA code is implemented, that error might not show up because an "End Sub" would terminate everything.

However, the fact that it STARTED to run (since Error 91 is a RUN-time error) means that what you provided compiled legally. (Because of the unmatched IF/End IF and another problem I'll cover next, not correctly. Just legally.)

What bothers me even more is that you are declaring a routine without a routine header. VBA code in Access must be either a Function or a Sub (i.e. a declared entry point) in a form, report, declared class, or general module context, and you have not shown us that your code is any of those. Only the code inside of Access itself can be a MAIN routine without a Function or Sub declaration.

You just banged away into code without preparing a context, yet you are clearly dealing with some kind of form or report because of your use of the Me.xxx construct. That shouldn't compile at all unless you are in a class module, and a module can ONLY execute code in a proper call context. You don't show us that. Therefore, we KNOW you haven't shown us the whole story.

 

[Isaac]

I believe Sonic's answer is the only possible cause. The recordset is successfully set, proved by earlier code running.
If you get that error, it's because something moved to that line at a point in time when the rs was NOT set, thus, the visual sequence you are showing us becomes less relevant to the issue

 

[Gasman]

I would have said that the recordset was set?, else it would fall over at the movefirst?

 

[Isaac]

I would have said that the recordset was set?, else it would fall over at the movefirst?

Right ... unless a line of execution running at a point in time when it was not set jumped to the line label that ends with a colon

 

[Pat Hartman]

Run Time error 91 Object Variable or With Block variable not set​

So none of you think the error might not be the missing End IF??????????????? That's the error I get when I don't close an If.

 

[cheekybuddha]

So none of you think the error might not be the missing End IF??????????????? That's the error I get when I don't close an If.

No it's not!

You get:

Compile error:

Block If without End If

 

[The_Doc_Man]

Pat, it is possible, but the part that bothers me is that it is a run-Time error, but a missing END IF is normally a compile-time error. The idea that it occurs after a GoTo to that pesky label makes sense because the run-time environment tracks things like that. However, for reasons that I stated earlier, we CANNOT POSSIBLY be seeing all that there is. Among other things, there IS no GoTo that could bypass the loop setup code.

When I look at the code in post #1, the FOR/LOOP construct is entirely within a single block - the still-open IF/END IF block implied by the line you called out. The beginning and end of the loop are at the same block level. While I agree that the open END IF is a serious error, because of the aforementioned reasons in this and my previous post, I cannot categorically agree with you ... yet. But in my previous post, I absolutely supported your call-out as something that would HAVE to be corrected. Except that since we cannot see beyond a certain point, we don't know that it HASN'T been closed and the post just had sloppy indentation.

 

[MajP]

Also the end if is not missing, they clearly just did not paste the end of the code. As @cheekybuddha pointed out you never could get error 91 which is a runtime error if the code did not compile do to a missing end if (compile time error).