Disallow navigation to next record when on last

[maw230]

I've spent far too long searching for the answer to this. On my form's onCurrent event I want to check if I am on the last record and if so, disallow navigating to the next "record" which is blank. I'm not sure why Access will let this happen to begin with when selecting Next Record. Isn't that what New (blank) record is for?

 

[CJ_London]

I would set the forms allow additions property to false

 

[maw230]

I would set the forms allow additions property to false

I'm sure that would be work just fine. What code can I use to determine if I am on the last record?

I tired this yesterday, but could not get it to work: http://www.access-programmers.co.uk/forums/showthread.php?t=104478

My VBA is mediocre at best.

Edit: This is the code I'm using

Public Sub Next_Record()
On Error GoTo Err_Next_Record
With Recordset
  If .AbsolutePosition = .RecordCount - 1 Then
'you are on the last record
        DoCmd.GoToRecord , , acFirst
  Else
'you are on some other record
        DoCmd.GoToRecord , , acNext
  End If
End With
Exit_Next_Record:
    Exit Sub
Err_Next_Record:
    MsgBox Err.Description
    Resume Exit_Next_Record
End Sub

I call it from the form's OnCurrent event and get the error: "Object required"

Edit 2: It was an error with how I was calling it I suppose. I moved the code to a private procedure in the OnCurrent event itself and it seems to be working so far.

 

[pr2-eugin]

What is Recordset?

 

[maw230]

What is Recordset?

"You use Recordset objects to manipulate data in a database at the record level. When you use DAO objects, you manipulate data almost entirely using Recordset objects. All Recordset objects are constructed using records (rows) and fields (columns)."

I would assume using Recordset allows the VBA to know I'm referring to the Form's records. But I have been wrong a few times before.

 

[maw230]

The code is "working", but I need to modify it so that when I'm on the last record I can't navigate forward, but I can add records and I can navigate backward.

Edit: I just ended up using my own buttons. Problem solved. Thanks!

 

[pr2-eugin]

"You use Recordset objects to manipulate data in a database at the record level. When you use DAO objects, you manipulate data almost entirely using Recordset objects. All Recordset objects are constructed using records (rows) and fields (columns)."

I think I am pretty sure what Recordsets mean in Access, since I have been using it for a while.

What I meant was I could not see a declaration of the variable "recordset" (a very bad variable name BTW). If you looked closely to the thread you have used they use a declaration Dim rst As DAO.Recordset. Then they would set that object, that is what you should have had in your code..

Glad you have it sorted though ! Good luck !

 

[maw230]

I think I am pretty sure what Recordsets mean in Access, since I have been using it for a while.

What I meant was I could not see a declaration of the variable "recordset" (a very bad variable name BTW). If you looked closely to the thread you have used they use a declaration Dim rst As DAO.Recordset. Then they would set that object, that is what you should have had in your code..

Glad you have it sorted though ! Good luck !

I cannot get it to work when I declare the recordset variable. I am unable to add the DAO 3.6 library as a Reference due to a naming error, if that makes a difference. Code:

Private Sub Form_Current()
Dim rst As dao.Recordset
'On Error GoTo Err_Next_Record
With rst
  If .AbsolutePosition = .RecordCount - 1 Then
'you are on the last record
        Me.cmd_next_main.Enabled = False
  Else
'you are on some other record
        Me.cmd_next_main.Enabled = True
  End If
 
If .AbsolutePosition = 0 Then
'you are on the first record
        Me.cmd_prev_main.Enabled = False
  Else
'you are on some other record
        Me.cmd_prev_main.Enabled = True
  End If
 
End With
Exit_Next_Record:
    Exit Sub
'
'Err_Next_Record:
'    MsgBox Err.Description
'    Resume Exit_Next_Record
End Sub

Error is 'object variable or with block variable not set'

 

[pr2-eugin]

You need to set the rst object..

Private Sub Form_Current()
On Error GoTo Err_Next_Record
    Dim rst As DAO.Recordset
    [COLOR=Red][B]Set rst = Me.RecordsetClone[/B][/COLOR]
    With rst
        If .AbsolutePosition = .RecordCount - 1 Then
            [COLOR=Green]'you are on the last record[/COLOR]
            Me.cmd_next_main.Enabled = False
        Else
            [COLOR=Green]'you are on some other record[/COLOR]
            Me.cmd_next_main.Enabled = True
        End If
      
        If .AbsolutePosition = 0 Then
            [COLOR=Green]'you are on the first record[/COLOR]
            Me.cmd_prev_main.Enabled = False
        Else
            [COLOR=Green]'you are on some other record[/COLOR]
            Me.cmd_prev_main.Enabled = True
        End If
    End With
    
Exit_Next_Record:
    Exit Sub
Err_Next_Record:
    MsgBox Err.Description
    Resume Exit_Next_Record
End Sub

 

[maw230]

Setting it to RecordsetClone produces undesirable results. I don't know what other options I have in that regard.

Edit: From what I understand RecordsetClone is a copy of the underlying record source for the form? So, if the results in the record source somehow don't match the Form's AbsolutePosition for the record...could that cause the problems?

 

[pr2-eugin]

What exactly do you class as undesirable? Try Me.Recordset ?!?

 

[maw230]

What exactly do you class as undesirable? Try Me.Recordset ?!?

See my edit. Undesirable means it doesn't work. My previous button is always disabled and my next button is not disabled when it should be. I tried Recordset with the same results.

Edit: I'm sure it's clear by now that I don't understand the fundamentals of the problem, so I hope it doesn't seem like I'm trying to. I only know that it worked when I didn't declare a Recordset variable and now it doesn't. I do appreciate your help here!