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"
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
) and got an interesting response from Astrid. I