how do i find the last record for a form

[Eric6]

hello,
i have navigation buttons on the bottom of my forms and
i disable them to prevent the user from going out of bounds
(that way, the user wont see an error message)

i have it working
but i was wonderring if there is a simpler way to find
the last record that a form uses...

right now, i'm using this code (this is a sample of my "Next Record" button)

'find current and lastrec numbers
CurrentRec = CurrentRecord
DoCmd.GoToRecord , , acLast
LastRec = CurrentRecord
'return to original rec
DoCmd.GoToRecord , , acGoTo, CurrentRec
'disable buttons
If CurrentRecord <> 1 Then
Me![Previous].Enabled = True
End If
If CurrentRecord >= LastRec Then
Me![Next].Enabled = False
End If

my &quot;problem&quot; is that, by moving to the last record (to find its value) and then moving back to the original record causes the form to flicker quite badly (because it displays the form moving back and forth through the records at a very high speed)

i was wonderring if there was a simpler way to find out what the last record is
or if there is a way to stop access from displaying the records while i find out which one is the last one

any input is greatly appreciated
thank you

Eric

 

[Craig0201]

Eric,

You could take a clone of the current recordset and check the EOF and BOF properties.

Set the bookmark on the clone to the bookmark on your form's recordset. You then the properties. If BOF = True then you're on the first record, if EOF = True then you're on the last.

Help?

Craig

 

[halx]

Hello,

Could someone post the code for what Craig0201 wrote in the previous post?

Thanks,

Alex

 

[MarrowGenx]

'Creates recordset and database variables
Dim rs as dao.recordset
Dim dbs as database

'Sets the dbs variable equal to the current database
set dbs = currentdb
'Sets the rs variable equal to the table [YOURTABLENAME]
set rs = dbs.openrecordset(&quot;Select * from [YOURTABLENAME]&quot

'Moves to the last record in the recordset
rs.movelast
'Sets the current record in the form equal to the last record in the recordset
me.bookmark = rs.bookmark

 

[KyleS]

Or if you're using bound forms (the fields are directly linked to the table/query you could always use Me.RecordsetClone

So your code would look something like this:


If Me.NewRecord = False Then
Me.RecordsetClone.Bookmark = Me.Bookmark
txtRecNum = Me.RecordsetClone.AbsolutePosition + 1
If txtRecNum = 1 Then
cmdPRecord.Enabled = False
Else
cmdPRecord.Enabled = True
End If
If txtRecNum = Me.RecordsetClone.RecordCount Then
cmdNRecord.Enabled = False
Else
cmdNRecord.Enabled = True
End If
lblRecNum.Caption = &quot; Of &quot; & Me.RecordsetClone.RecordCount & &quot; Records&quot;
Else
txtRecNum = &quot;New Record&quot;
End If


Where txtRecNum is a text box that displays the position of the current record and lblRecNum is where I put the total records on the form (so I can reproduce the &quot;Record 1 of 75&quot; effect that you get with the built-in navigation buttons without giving up my control over navigation.

This is the code I use to keep track of my record location when I'm using bound forms. Kyle

+ =
+ =