Go to next record code activating for no reason?

Question

MS Access 2016

I have the following code in the current event of a form:

Dim linecount As Integer
Dim linenum As Integer

Private Sub Form_Current()

linenum = (DCount("ID", "tbl ShippingHistory", "[sales order] = forms![frm Shipping Information]!combo3") - 1)

If linecount < linenum Then
    linecount = linecount + 1
    DoCmd.GoToRecord Record:=acNext
End If

End Sub

Well there's a lot more actually, but this is the part that's giving me trouble. If I comment out the whole rest of the sub, this part still causes an issue.

So when the form opens, basically it counts the number of lines on the order and stores it in linenum (then subtracts one because I don't want to include the record I start on)

Then each time we go through the current event it adds 1 to linecount and goes to the next record.

When linecount equals linenum we should be on the last record and we don't want to go to the next record anymore.

All of this seems to work fine until we hit the last record at which point an error pops: Run-time error 2105: you can't go to the specified record. When I hit the debug button Access indicates that the error is with DoCmd.GoToRecord Record:=acNext

So I put a break at the beginning of the If statement and stepped through it and here's what happens:

1. It does as I expect it to, it counts the lines, it adds 1 each time we go to a new record and activate the current event and when linecount finally equals linenum, it skips the rest of the if statement and goes to end sub But the event doesn't end there...

2. Then... when I hit F8 to take the next step it jumps back to the End If skipping everything else entirely, then the next F8 takes it to the end sub again and then it pops the error. saying i can't go to the record...

Now I'm confused because it doesn't look like the go to next record is activated again so why is it trying to go to the next record? More importantly, why doesn't it end the sub and stop once it finally hits end sub but instead goes back to the end if...?

EDIT: just extra info, I did try sticking an

else
     Exit Sub

to the if statement, but it didn't make a difference.

Answer 1

Good morning all,

I came across my solution after some suggestions to test my issue from @Javier.

So what I ended up doing, rather than trying to prevent the current event from firing the extra time which seemed like a hassle, I just changed the event. I took the code I showed earlier:

Dim linecount As Integer
Dim linenum As Integer

Private Sub Form_Current()

linenum = (DCount("ID", "tbl ShippingHistory", "[sales order] = forms![frm Shipping Information]!combo3") - 1)

If linecount < linenum Then
    linecount = linecount + 1
    DoCmd.GoToRecord Record:=acNext
End If

End Sub

and put it in a button click event.

So now the event only triggered when I clicked the button on each record.

After verifying that worked correctly, I added a line calling the click event of the button if we weren't on the last record (linecount < linenum). When that also worked (pressing the button on the first record went through all the records performing as I wanted). I added the else docmd.close so it would close the form when we were finished with the last record

Dim linecount As Integer
Dim linenum As Integer

Private Sub btn_DoNextRecord_Click()

linenum = (DCount("ID", "tbl ShippingHistory", "[sales order] = forms![frm Shipping Information]!combo3") - 1)

If linecount < linenum Then
    linecount = linecount + 1
    DoCmd.GoToRecord Record:=acNext
    **Call btn_DoNextRecord_Click**
Else
    DoCmd.Close
End If

End Sub

And then finally, to automate the whole thing, I added Call btn_DoNextRecord_Click to the form load event so that opening the form would press the button the first time, which ran through all the records and then closed the form. Everything seems to be running correctly now.

Thanks again to @Javier for putting me on the right track and showing me how to verify the issue!