Disable New Button unless on Last Record

[pademo57]

On my form I have a "New Record" button which works fine. However, the people I am working for ONLY want that button enabled IF they are on the last record.

Apparently their issue is, if they are on record # 123 and press the "New" button it throws them off if the "New" record is, for example: # 256 and jumps to that new record. !!! I'm not sure why?!?

Is there a clean way to write this code so the "New" button is disabled until they are on the Last record?

I'm scratching my head over this one and I did try to search in the forums but I haven't been able to find an answer to this one.
:banghead:

 

[David R]

Is this a custom button, or the Record Navigation bar at the bottom of a standard form? If the latter http://www.access-programmers.co.uk/forums/showpost.php?p=582348&postcount=3 may be useful...

But it sounds like you're talking about a custom button. Are the records in the form sorted oldest-to-newest? You might be able to check in Form_Current to see if FieldX (entered date, invoice #, whatever) is = Max(FieldX) and enable/disable from there? Remember to apply any potential filters to the Max() calculation, though.

Alternatively you may be able to compare Me.CurrentRecord to RecordCount, but I haven't tested that yet.

 

[pademo57]

Sorry, I should have been more specific. Yes, it is a custom button called "cmdbutAddRecord".

I have disabled all the MS Access record navigation buttons because it was confusing to my client.

 

[pademo57]

Argh! Hit the Submit button before answering your questions:

It is sorted based on the Students ID.

 

[Rx_]

That is a very useful suggestion for the rule: button enabled IF they are on the last record
My applications typically involved regulations. So, there may be multiple conditions and those conditions may be on alternate forms (tables).
A public function in a public module can have arguments passed into it and return a True / False condition. It is useful for code re-use and documentation.
It is also good if the rule changes. Go to one location and make the change.

If your single rule is something expected to use multiple times, maybe you wrap the last record check into a function and re-use it.

Example of a function that checks if the person using the application is a Developer - used hundreds of places. It can make a textbox with a current record ID visible for developers and invisible to users.

Function IsDeveloper() As Boolean
' A boolean that determines if someone is a developer
' For example: turn on / off menu bar, edit, or otherwise set properties
' At run time - some properties are turned off for average users
' On Citrix - this allows a developer to peek into areas to see what is going on
' The Case statement here uses a hard coded name. The actual looks up names / roles in a table
10        On Error Resume Next
20        Dim UserName As String
30        IsDeveloper = False
40        UserName = VBA.Environ("UserName")
50        Select Case UserName
              Case "Rx"  '  Give me the power to test anyway
60                IsDeveloper = True
70            Case Else
80                IsDeveloper = False
90        End Select
' Add - Any other rules - e.g. is Release "live" or "test"          
End Function

Private Sub Form_Load()
' Me.AllowAdditions = False
Me.AllowAdditions = IsDeveloper()
End Sub

 

[boblarson]

A few things to note:

1. The "record numbers" in the navigation button area of the form are ONLY good for the current recordset.

2. If a record is added/deleted, the record numbers will be reassigned. What was record number 123 may now be 118.

3. Tables do not store records in any particular order. Access will attempt to order them upon compact and repair, if there is a primary key, but you should not rely on it to be so. Think of it like a bucket of fish. You reach down and pull one out and the rest can slide all over the place and won't necessarily be in the same order. If you want ORDER to the data, you must use a QUERY with an ORDER BY clause and use that query as the form's record source.


If you have a primary key so that each record is identifiable, you can use this code:

Dim rst As DAO.Recordset
 
Set rst = Me.RecordsetClone
 
If rst.RecordCount > 0 Then
 
   rst.MoveLast
End If
 
   Me.NewRecordButton.Enabled = (Me!YourPKFieldHere = rst("YourPKFieldHere").Value)
 
rst.Close
Set rst = Nothing

 

[Rx_]

Have not tested -but a function like this one might be in order?

Public Function Is_Last_Record() as Boolean
On Error GoTo Err_Is_Last_Record
With Me.recordset
  If .AbsolutePosition = .RecordCount - 1 Then    
[COLOR=green]      '      the last record[/COLOR]
        Is_Last_Record = True
  Else                                              
[COLOR=green]      ' some previous record[/COLOR]
        Is_Last_Record = False
  End If
End With
Exit_Next_Record:
    Exit Function
 
Err_Is_Last_Record:
    MsgBox Err.Description
    Resume Exit_Is_Last_Record
End Function

took a while for me to get back Didn't see the updates- the sort changes things as Bob mentioned. His post should do.
I pass in an ID to a function with a sorted sql statement filtered with that ID then check the resulting filtered recordset for EOF.