You may be at the end of a recordset | Error 2105 (1 Viewer)

J

jjake

Registered User.
Local time
Today, 07:29
Joined
Oct 8, 2015
Messages
291
Hi,

I have numerous forms with previous and next record buttons. Whenever a user scrolls to the last record it pops up with a default message box "You can't go to the specified record, You may be at the end of a record set" Which is fine, but when i click OK it pops up with a Macro single step error 2105

frmOrder : cmdNextPO : OnClick : Embedded Macro

How can i change this so that i will pop up with a box that states "Last Record Available" and when the user clicks OK it will go back to the last record available instead of displaying the macro error?

I can replace this macro with VBA if necessary.

The same rule would apply to previous records when the user goes to the beginning and cant go past the first record.

Thanks.
 
S

sneuberg

AWF VIP
Local time
Today, 05:29
Joined
Oct 17, 2014
Messages
3,506
I believe there are various way to do this. I've posted some vba code below that you might consider. In this code the next button is named NextBTN" and the previous button is named "PreviousBTN". Change these to your buttons' names. This code doesn't have message boxes. I put in comments where they would go. Personal I find them irritating and would leave them out. I think the fact the button is disable is notification enough

Code for the next button click event
Code: 
If Recordset.AbsolutePosition = Recordset.RecordCount - 1 Then
    'Put msgbox here
    Me.NextBTN.Enabled = False
Else
    Me.PreviousBTN.Enabled = True
    DoCmd.GoToRecord , , acNext
End If


Code for the previous button event
Code: 
If Recordset.AbsolutePosition = 0 Then
   'Put msgbox here
    Me.PreviousBTN.Enabled = False
Else
    Me.NextBTN.Enabled = True
    DoCmd.GoToRecord , , acPrevious
End If
 
J

jjake

Registered User.
Local time
Today, 07:29
Joined
Oct 8, 2015
Messages
291
I am getting the following Error. 'Runtime Error 2105' Can't go to the specified record

When I click the previous button it greys out but doesn't go to a previous record

Private Sub cmdNextPO_Click()
If Recordset.AbsolutePosition = Recordset.RecordCount - 1 Then
'Put msgbox here
Me.cmdNextPO.Enabled = False
Else
Me.cmdPreviousPO.Enabled = True
DoCmd.GoToRecord , , acNext
End If
End Sub

Private Sub cmdPreviousPO_Click()
If Recordset.AbsolutePosition = 0 Then
'Put msgbox here
Me.cmdPreviousPO.Enabled = False
Else
Me.cmdNextPO.Enabled = True
DoCmd.GoToRecord , , acPrevious
End If
End Sub
 
S

sneuberg

AWF VIP
Local time
Today, 05:29
Joined
Oct 17, 2014
Messages
3,506
Disregard this. See my last post.

If you are on the first record it is supposed to grey out as there is go previous record to go to. It should be enabled again when you click on the next button. If you are on some other record, let's say record 2 and it greys out, then I guess this code won't work on your form although I'm mystified as to why it doesn't work right.
 
Last edited:
S

sneuberg

AWF VIP
Local time
Today, 05:29
Joined
Oct 17, 2014
Messages
3,506
Disregard this. See my last post.

Oh by the way if you have First Record and Last Record buttons the code of of First Record button will need

Me.cmdNextPO.Enabled = True

and the code of the Last Record button will need

Me.cmdPreviousPO.Enabled = True
 
Last edited:
S

sneuberg

AWF VIP
Local time
Today, 05:29
Joined
Oct 17, 2014
Messages
3,506
I'm sorry but the code I recommended doesn't work right when you delete records. I found this on line a long time ago and kept it because I thought it was a concise way of doing this, but I never really tested it thoroughly myself. Now I see from https://msdn.microsoft.com/en-us/library/bb221121(v=office.12).aspx that the Recordset.AbsolutePosition becomes undefined when you delete a record.

Maybe the other forum members can give you something that actually works. :eek:
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 14:29
Joined
Nov 3, 2010
Messages
6,142
I have up/down , but should be the same as next/previous, play with it, call it from Current event handler:

Code: 
Public Sub SetMoveButtons(MyForm As Form)

    With MyForm
    
        If Not .NewRecord Then
            .RecordsetClone.MoveLast    'this to get proper record count
            .cmdMoveDown.Enabled = Not .CurrentRecord >= .RecordsetClone.RecordCount
            .cmdMoveUp.Enabled = Not .CurrentRecord = 1 And Not .CurrentRecord > .RecordsetClone.RecordCount
        Else
            .cmdMoveDown.Enabled = False
            .cmdMoveUp.Enabled = False
        End If
        
    End With
    
End Sub
 
J

jjake

Registered User.
Local time
Today, 07:29
Joined
Oct 8, 2015
Messages
291
I get runtime error 424 - Object Required when opening the form

Code: 
Private Sub Form_Current()
     With frmOrder
    
        [COLOR=red]If Not .NewRecord Then
[/COLOR]           .RecordsetClone.MoveLast    'this to get proper record count
            .cmdNextPO.Enabled = Not .CurrentRecord >= .RecordsetClone.RecordCount
            .cmdPreviousPO.Enabled = Not .CurrentRecord = 1 And Not .CurrentRecord > .RecordsetClone.RecordCount
        Else
            .cmdNextPO.Enabled = False
            .cmdPreviousPO.Enabled = False
        End If
        
    End With
    
End Sub
 
S

smig

Registered User.
Local time
Today, 15:29
Joined
Nov 25, 2009
Messages
2,209
You must let vba know what frmOrder is.
 
S

sneuberg

AWF VIP
Local time
Today, 05:29
Joined
Oct 17, 2014
Messages
3,506
Any reason why Me, instead of the form name, can't be used here? I've been testing a version of what jjake posted using Me and it seems to work fine. If you used Mr Spikes subroutine instead of putting the code directly in the Current event, wouldn't you call it with SetMoveButtons Me
 
Last edited:
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:29
Joined
Sep 12, 2006
Messages
15,638
can you not just simply use the form error event to intercept and ignore the 2105 error?
 
J

jjake

Registered User.
Local time
Today, 07:29
Joined
Oct 8, 2015
Messages
291
sneuberg said:
Any reason why Me, instead of the form name, can't be used here? I've been testing a version of what jjake posted using Me and it seems to work fine. If you used Mr Spikes subroutine instead of putting the code directly in the Current event, wouldn't you call it with SetMoveButtons Me
Click to expand...

Ok so I did what sneuberg suggested and just substituted the form name with me and it all worked fine. I still had to set the macros on the buttons to scroll previous and next.


Code: 
Private Sub Form_Current()
     With [COLOR=lime]Me[/COLOR]
    
        If Not .NewRecord Then
           .RecordsetClone.MoveLast    'this to get proper record count
            .cmdNextPO.Enabled = Not .CurrentRecord >= .RecordsetClone.RecordCount
            .cmdPreviousPO.Enabled = Not .CurrentRecord = 1 And Not .CurrentRecord > .RecordsetClone.RecordCount
        Else
            .cmdNextPO.Enabled = False
            .cmdPreviousPO.Enabled = False
        End If
        
    End With
    
End Sub
 
You must log in or register to reply here.

Similar threads

H
Is it possible to edit the contents of the Unhide Columns box in the datasheet part of a split form?
Replies
14
Views
662
isladogs
isladogs
Kayleigh
Error re SQL server statement Foreign key conflict
Replies
4
Views
151
Pat Hartman
P
C
Combobox throwing error the first time you use it, but not later times
Replies
9
Views
421
LarryE
LarryE
M
Opening forms with fields pre-filled
Replies
16
Views
927
mikenyby
M
J
macro or function preventing saving date to a field.
Replies
9
Views
242
John Sh
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom