Disable nav button if first or last record 2

[HenryAnthony]

Hi all,

I have two commands buttons. One goes to the next record, the other goes to the previous record. On the first record, I would like to disable the "previous" button. On the last record, I would like to disable the "next" button.

Any suggestions will be greatly appreciated.

Best regards,

Henr¥

 

[jebry]

Hi Henry!

I think this will work, but I haven't tested it:

Dim rst As DAO.Recordset

DoCmd.RunCommand acCmdRecordsGoToNext
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark
If rst.EOF (or BOF in the previous record button) = True Then
Call SomeControl.SetFocus
YourNextCommandButton.Enabled = False
End If

Set rst = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu

 

[HenryAnthony]

Greetings Jeff!

I appreciate your response. No need for you to test. That's what I'M here for. I'll let you know how it works out.

Best regards,

Henr¥

 

[rarich01]

Hey Henry,

I had a similar problem myself. There are several ways of doing it, but here's how I suggest you handle it. You will need to use VBA to accomplish this, so I'm hoping you're familiar with this.

1. Go to the Form's Code section (View->Code). Under 'Option Compare Database' create two global variables. You would do this by typing, for example,

Public lngFirstRecordID AS Long
Public lngLastRecordID AS Long

The first variable will hold the PrimaryKey of the first record in your recordset and the second variable will hold the PrimaryKey for the Last record in your recordset. Ofcourse, if your primary keys are not of data type 'Long', then modify them accordingly.

2. Go to the form's 'On Open' event, click on the Ellipsis (...) button on the right, and choose code builder. Create a recordset object that will house the recordset you are interested in. Say, for example, your recordset is a table called tblEmployees. Then, the following code will set this recordset in a variable:

Dim rcdTable AS Recordset
Set rcdTable = CurrentDB.OpenRecordset("tblEmployees&quot

Once you've created this variable, what you want to do is save both the first and last records' PrimaryKeys into the global variables you created in step 1. If the field for the PrimaryKey was EmployeeID, then the following code will accomplish this.

With rcdTable
'Moves to first record
.MoveFirst

'Save Primary Key of First Record
lngFirstRecordID = .Fields!EmployeeID

'Move to Last Record
.MoveLast

'Save Primary Key of Last Record
lngLastRecordID = .Fields!EmployeeID

'Close Recordset
.Close
End With

This code will run when the Form is first opened. In effect, what you will have done is Force the form to remember the first and last record's PrimaryKeys while the form is opened.

3. The final step is to use your global variables to compare the current record with the first and last records. Here's the jist of what you have to do. Use the buttons' 'After Update' events. When you first open the form, the 'Back' button should be disabled, because, ideally, you should be at the first record. Now, as soon as you hit the 'Next' button, what will happen is this. The 'After Update' event will run. In it, you will first Enable the 'Back' button. Next, you will check to see if the current record's PrimaryKey equals the value of lngLastRecordID. If it does, move the focus to the 'Back' button, and then Disable the 'Next' button. If it doesn't, then don't do anything.

Similarly, for the 'Back' button's 'After Update' event, you will do the same thing. When the 'Back' button's 'After Update' event is launched, you will first Enable the 'Next' button, and then check to see if the current record's PrimaryKey equals the value of lngFirstRecordID. If it does, move the focus to the 'Next' button and then Disable the 'Back' button. If it doesn't, don't do anything at all.


Hope this helps.

rarich01

 

[JimHorton]

Just another easier way to accomplish the same end:
Just put an error trap in the button event and if the error is (forgot the #) "The recordset has reached the end..." or "No current record", you put up a message.
No fancy code, but the user gets the same idea that he's at the end.
--Jim

 

[BoxHead]

rarich,

I'm trying this out. I put the if... then statement on the form's current (no update on buttons) event, but it's disabling the Previous button on the 3rd record and the Next button on the 5th record.

There are 7 records in my table, the order of the records in the form is the same as the order of the records in the table.

I tried throwing in a .MoveLast before the .MoveFirst because I saw in help that that will populate the recordset.

Any ideas?

TIA

John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Go figure...

AS per my previous post, the order of my records in form view is the same as the order of the records in the form's underlying table, HOWEVER, when I output the table to Excel, the order is changed.

My MoveFirst and MoveLast are picking up on the order that is output to Excel rather than the order in the form or table.

I'll use Jim's solution 'til I figure it out or someone can explain it.

FYI

John

Use what you have,
Learn what you can,
Create what you need.

 

[HenryAnthony]

Hi all,

Thanks for all the great ideas. It will take me a day or so to get to this, priorities you know. I posted this in the forms forum (say that fast 3 times ) and got an interesting response from Astrid. I

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/702/qid/152613

<<Astrid (handle &quot;Sawatzky&quot wrote>>
if you were using adodb.recordset and did not change the order or the filter of the recordset by code you could
check after moving to previous
nextbutton.enabled =true
if rec.absoluteposition = 1 then
prebutton.enabled = false
end if

after moving forward
prevbutton.enabled =true
if rec.abcoluteposition = rec.recordcount then
nextbutton.enabled =false
end if

HTH, if not give me more details about your navigating

regards astrid