Test for last record on form (1 Viewer)

[Curry]

Hi All,

I am trying to get a Command Button to become enabled only when a form reaches its last record...I have played around with the following however do not know what to use in place of (lastrecord).

Private Sub Form_Current()
If (lastrecord) Then
Forms![frmActionUpdate]![NEXT].Enabled = yes
Else: Forms![frmActionUpdate]![NEXT].Enabled = no
End If
endsub

Thanks for any help..

 

[jon_sg]

You need to use recordset clone object of the form. Are you using ADO or DAO

I have never really used DAO so I am unsure of the syntax.

If you are using ADO in mdb file there is a slight problem which I have not managed to figure out, the recordset.clone method does not seem to work.

There is a way round this by opening a new readonly recordset object based on your form, (you must use the exact same filtering and order as you used when opening your form). It may be best to base you form on a query from the table and the specify the query instead of the strSql variable in the rst.Open statement (remember to enclose the query name in"" if doing this)

code:

Dim rst As New ADODB.recordset
Dim cnn As New ADODB.connection
Dim strSql As String

set cnn = CurrentProject.Connection
strSql="SELECT [Primary Key field] FROM your Table;" 'Remember to include any filters / ordering here - if your form used them

rst.Open strSql, cnn, adOpenStatic, adLockReadOnly, adCmdTable

If rst.recordcount = 0 Then
' If you want the button enabled when no records in form enter code here
rst.Close
set rst = nothing
Exit sub
End if

rst.Find "Primary Key field = Me.[Pk field from form]"

rst.MoveNext

If rst.EOF = true Then
'Enter your code to enable the command button
Else
'Code goes here to disable button if required
End If
rst.close
set rst = nothing

You may need to add some conditional code elsewhere to handle adding and deleting records

Regards

Jon

 

[pono1]

Adding to Jon's comments, if you're not filtering your data (Jon noted the importance of paying attention to this), something similar to the following would do it for you.

If Me.CurrentRecord = DCount("NoDupFieldName", Me.RecordSource) Then
    MsgBox "It's the last record."
End If

If you are filtering, it won't do it for you.

Regards,
Tim

 

[Pat Hartman]

RecordsetClone will work. The problem is that the recordset of a form is DAO so the clone recordset needs to also be defined as a DAO.Recordset.