I have a form where I put my own Back and Forward buttons on the form.
I used the codes: - DoCmd.GoToRecord , , acNext
-
DoCmd.GoToRecord , , acPrevious
But when I get to the last record or the first record, I get an error.
Is there a way to detect if it is a t the last record? Or some other way to fix this.
I tired using the on the on error.... step but it messes with my other code for the button.
5 13141
Try putting your own error message in ... -
Private Sub cmdNextRecord_Click()
-
On Error GoTo Err_cmdNextRecord_Click
-
-
DoCmd.GoToRecord , , acNext ' move to next record
-
-
Exit_cmdNextRecord_Click:
-
Exit Sub
-
Err_cmdNextRecord_Click:
-
MsgBox "This is the last record", , "Last Record"
-
Resume Exit_cmdNextRecord_Click
-
End Sub
-
-
Private Sub cmdPreviousRecord_Click()
-
On Error GoTo Err_cmdPreviousRecord_Click
-
-
DoCmd.GoToRecord , , acPrevious ' move to previous record
-
-
Exit_cmdPreviousRecord_Click:
-
Exit Sub
-
Err_cmdPreviousRecord_Click:
-
MsgBox "There is no record prior to this", , "First Record"
-
Resume Exit_cmdPreviousRecord_Click
-
End Sub
-
Mary
Another approach I sometimes use is to "wrap" back around. If you're on the Last Record and hit Next it wraps around to the First Record. If you're on the First Record and hit Previous it wraps back around to the Last Record. - Private Sub cmdNextRecord_Click()
-
On Error GoTo Err_cmdNextRecord_Click
-
DoCmd.GoToRecord , , acNext
-
Exit_cmdNextRecord_Click:
-
Exit Sub
-
-
Err_cmdNextRecord_Click:
-
DoCmd.GoToRecord , , acFirst
-
End Sub
-
-
-
Private Sub cmdPreviousRecord_Click()
-
On Error GoTo Err_cmdPreviousRecord_Click
-
DoCmd.GoToRecord , , acPrevious
-
Exit_cmdPreviousRecord_Click:
-
Exit Sub
-
-
Err_cmdPreviousRecord_Click:
-
DoCmd.GoToRecord , , acLast
-
End Sub
I have a form where I put my own Back and Forward buttons on the form.
I used the codes: - DoCmd.GoToRecord , , acNext
-
DoCmd.GoToRecord , , acPrevious
But when I get to the last record or the first record, I get an error.
Is there a way to detect if it is a t the last record? Or some other way to fix this.
I tired using the on the on error.... step but it messes with my other code for the button.
The professional way to accomplish what you are requesting is by the creation of "Smart Navigation" Buttons which change their "Enabled State" depending on the Current Record Pointer. This system involves the creation of 5 Command Buttons, code in the Current Event() of the Form, and the RecordsetClone property to test potential moves. The User will never see an Error because of an inadvertant move, because it will never happen. A little bit of effort, and the close following of these instructions, will give your Form a truly professional look: - Create a Command Button whose Caption = First, and whose Name is cmdFirst. Place this code in the Click() Event.
- Private Sub cmdFirst_Click()
-
DoCmd.GoToRecord , , acFirst
-
End Sub
- Create a Command Button whose Caption = Previous, and whose Name is cmdPrevious. Place this code in the Click() Event.
- Private Sub cmdPrevious_Click()
-
DoCmd.GoToRecord , , acPrevious
-
End Sub
- Create a Command Button whose Caption = Next, and whose Name is cmdNext. Place this code in the Click() Event.
- Private Sub cmdNext_Click()
-
DoCmd.GoToRecord , , acNext
-
End Sub
- Create a Command Button whose Caption = Last, and whose Name is cmdLast. Place this code in the Click() Event.
- Private Sub cmdLast_Click()
-
DoCmd.GoToRecord , , acLast
-
End Sub
- Create a Command Button whose Caption = Add Record, and whose Name is cmdAddNew. Place this code in the Click() Event.
- Private Sub cmdAddNew_Click()
-
DoCmd.GoToRecord , , acNewRec
-
End Sub
- Place this code in the Current() Event of the Form, it is well documented so that you can get an idea as to what is goiong on. I even added code to modify the Form's Caption depending on which Record is current. This was basically to show how the AbsolutePosition and RecordCount properties of a Recordset work. If you have any questions at all, please feel free to ask. I realize that this may be a little confusing and I'm not really sure of your skill level.
- Private Sub Form_Current()
-
Dim recClone As Recordset, intNewRecord As Integer, Msg As String
-
-
'Make a duplicate copy of the Form's Recordset Object using RecordsetClone().
-
'It will create a seperate copy of the Recordset so that you can navigate or
-
'Manipulate a Form's Records independently of the Form itself. Maneuvers will
-
'not be reflected in the Form's Recordset.
-
Set recClone = Me.RecordsetClone()
-
'necessary for initial Record Count for Form Caption
-
recClone.MoveLast
-
recClone.MoveFirst
-
-
'If this is a New Record then disable the <Next> and <New> Buttons and enable
-
'the others. Must Exit the Sub-Routine at this point!!!!!!!!!!
-
'intNewRecord = IsNull(Me![Name]) 'Name is a required Field OR
-
intNewRecord = Me.NewRecord
-
-
If intNewRecord Then 'Zero Records - disable all Buttons
-
cmdFirst.Enabled = True
-
cmdNext.Enabled = False
-
cmdPrevious.Enabled = True
-
cmdFirst.Enabled = True
-
cmdAddNew.Enabled = False 'Not using in this Form
-
Exit Sub
-
End If
-
-
'If we reach this Point, we are not at a New record, so enable the <New Record> Button
-
cmdAddNew.Enabled = True
-
-
'If there are no Records, disable all Buttons except <New Record>
-
If recClone.RecordCount = 0 Then
-
cmdFirst.Enabled = False
-
cmdNext.Enabled = False
-
cmdPrevious.Enabled = False
-
cmdFirst.Enabled = False
-
Else 'Must synchronize the Current Pointer in both Recordsets. Obviously Records exist
-
recClone.Bookmark = Me.Bookmark 'Resync after each Record Navigation
-
Msg = "Injuries for Years 1993 to 2000 (Record " & Str$(recClone.AbsolutePosition + 1) & " of "
-
Msg = Msg & recClone.RecordCount & ")"
-
Me.Caption = Msg
-
-
'There are Records, let's see if we're on the First Record. If we are, disable the
-
'<First> and <Previous> Buttons
-
recClone.MovePrevious
-
cmdFirst.Enabled = Not (recClone.BOF)
-
cmdPrevious.Enabled = Not (recClone.BOF)
-
recClone.MoveNext
-
-
'See if we're on the <Last Record> and if we are, disable the <Last>
-
'and <Next> Buttons
-
recClone.MoveNext
-
cmdLast.Enabled = Not (recClone.EOF)
-
cmdNext.Enabled = Not (recClone.EOF)
-
recClone.MovePrevious
-
End If
-
-
'Don't forget to Close the Recordset
-
recClone.Close
-
End Sub
The only way that I could get this to work was by changing
Dim recClone As Recordset
to
Dim recClone as DAO.Recordset
The only way that I could get this to work was by changing
Dim recClone As Recordset
to
Dim recClone as DAO.Recordset
That is because you probably have References to both the DAO and ADO Type Libraries. Thanks for bring this to light, since you should always make Explicit References to the Libraries as in: - Dim MyRS As DAO.Recordset
-
Dim MyRS As ADODB.Recordset
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Josh Strickland |
last post by:
I am attempting to create an Access database which uses forms to enter
data. The issue I am having is returning the query results from the
Stored Procedure back in to the Access Form.
...
|
by: Fred F. |
last post by:
Here is my problem. From an ACCESS form, I run a C++ process reaching
the same database in read/write mode via the ODBC driver. An error
occurs: " The database has been placed by an unknown user in...
|
by: bjbounce2002 |
last post by:
Hello,
I am using forms with command buttons to close form or run action
queries. The error messages such as "Null value in required field" or
"duplicate value in primary key" are suppressed....
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am...
|
by: Dököll |
last post by:
Hello again!
I loaded some web pages in an access form. For now, adding Next and Previous buttons work to get me to the next or previous web sites.
Searching for a code that deals with paging...
|
by: nassersh |
last post by:
I am using a bound control to embed word documents within an access form.
I have created two buttons on the form One to embed aand display and one to close.
I embed one doc and then save the doc...
|
by: John |
last post by:
I am looking for VBA code that will work with Access 2003 to enable
dragging and dropping a file/folder name from Windows XP Explorer into
an Access form's text box. This is a common functionality...
|
by: kpresidente |
last post by:
Hello all,
I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |