• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Use Next and Previous Controls

In the project I am working on I have disabled the Record Controls on all form as they always look untidy.  In two of the forms it does make sense for the user to 'scroll through' records one at a time.  Like in the Rooms form when looking at rooms that a B&B Operator offers, and also when looking at Payments made for a Booking.  Thus I do need the Next/Previous/Add functionality but prefer to have my own style '<<', '>>' & Add buttons.

I would like to trap the Next button keypress that moves beyond the number of records in the recordset and goes to a blank (new) record.  I want to make users realize that they are Adding a record by forcing them to use the Add button rather than allow them to press Next too many times and thus arrive at a blank record by mistake.

Is there anything that I could add to Next button code that would just keep looking at the last matching record i.e. stay where it is on the last record on the recordset (with filter) rather than throwing a new record.

BTW Don't worry if I don't reply straight away as I am a part-time developer and my day job calls me now.
0
MikeDTE
Asked:
MikeDTE
  • 5
  • 3
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Here's the code I generally use to build my own Move buttons:

Function MoveWhere (MoveAction As String) As Boolean

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

Select Case MoveAction
  Case "Next"
    If Not rst.EOF Then rst.MoveNext
  Case "Previous"
    If Not rst.BOF Then rst.MovePrevious
  Case "First"
    rst.MoveFirst
  Case "Last"
    rst.MoveFirst
End Select

Me.Bookmark = rst.Bookmark
End Function

Open in new window

 
You'd call it like this:

MoveWhere "First"

MoveWhere "Next"
0
 
Dale FyeCommented:
In addition to a MoveWhere subroutine, I also use a subroutine (NavButtons) that I call in the current event (and Form Dirty, cmd_Cancel, and cmd_Save click events) of those few forms where I display navigation buttons.

This enables/disables all my navigation buttons based on the current status of the form.
 
Private Sub NavButtons()

    Dim rs As DAO.Recordset
    Dim FirstRec As Boolean, LastRec As Boolean, NewRec As Boolean
    Dim Dirty As Boolean

    On Error GoTo ProcError
    
    Set rs = Me.RecordsetClone
    FirstRec = rs.AbsolutePosition = 0
    LastRec = rs.AbsolutePosition = rs.RecordCount - 1
    Set rs = Nothing

    NewRec = Me.NewRecord
    Dirty = Me.Dirty

    Me.cmd_First.Enabled = (Not FirstRec) And (Not NewRec) And (Not Dirty)
    Me.cmd_Prev.Enabled = Me.cmd_First.Enabled
    Me.cmd_Next.Enabled = (Not LastRec) And (Not NewRec) And (Not Dirty)
    Me.cmd_Last.Enabled = Me.cmd_Next.Enabled
    Me.cmd_Add.Enabled = (Not NewRec) And (Not Dirty)
    Me.cmd_Cancel.Enabled = NewRec Or Dirty
    Me.cmd_Save.Enabled = NewRec Or Dirty
    'Me.cmd_Close.Enabled = Not Dirty

ProcExit:
    Exit Sub
ProcError:
    If Err.Number = 2164 Then
        Me.txtDummy.SetFocus
    Else
        Debug.Print Err.Number & vbCrLf & Err.Description
    End If
    
End Sub

Open in new window

0
 
MikeDTEAuthor Commented:
Hi LSM Consulting

I'm confused here!

Are you testing the move on a recordset clone and then passing back a bolean vaue which is set false if, the the case of a 'next', the move is beyond the last record?

Surely you have to attempt a moveNext to get an EOF condition?

If so where is the code that moves the record pointer and where is the MoveWhere value set?

Also should Case Last be rst.MoveLast and should you include Set rst = Nothing before ending the function.

Or am I missing the point?  

Regards

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
MikeDTEAuthor Commented:
Hi fyed

I saw elements of your code that might achieve what I want to do - see code in my NextButton proc. below

The problem is that I cannot achieve a condition where AbsolutePosition > 0  - as a consequence LastRec always = False

When you open a clone a recordset does the recordpointer move in sequence with the original recordset or is it always reset to row 0.  If so I could open the clone recordset in Form_Load.

Your comments would help - thanks
Private Sub NextButton_Click()
On Error GoTo Err_NextButton_Click

    Dim rs As DAO.Recordset
    Dim LastRec As Boolean

    Set rs = Me.RecordsetClone
    LastRec = rs.AbsolutePosition = rs.RecordCount - 1
    
    Debug.Print rs.AbsolutePosition
    
    Set rs = Nothing

    If Not LastRec Then DoCmd.GoToRecord , , acNext

Exit_NextButton_Click:
    Exit Sub

Err_NextButton_Click:
    'MsgBox Err.Description '- disabled because not necessary and slows down usage
    Resume Exit_NextButton_Click
    
End Sub

Open in new window

0
 
MikeDTEAuthor Commented:
OK I've worked it out

See code below.  

This works a treat working with the form's own recordset rather than a clone.  Also requires no code in the Prev button proc but it is advisable to disable the error MsgBox do not repork over-clicks on the Prev button
Private Sub NextButton_Click()
On Error GoTo Err_NextButton_Click
    
    Dim LastRec As Boolean

    LastRec = Form.Recordset.AbsolutePosition = Form.Recordset.RecordCount - 1
    
    If Not LastRec Then DoCmd.GoToRecord , , acNext

Exit_NextButton_Click:
    Exit Sub

Err_NextButton_Click:
    MsgBox Err.Description
    Resume Exit_NextButton_Click
    
End Sub

Open in new window

0
 
MikeDTEAuthor Commented:
The solution did not solve the issue but made me aware of the way to resolve it.  The AbsolutePosition property and the comaprison to the RecordCount gave me a reference point to exactly where the record pointer stood and therefore a way of finding out if it sat on the last record.  Then if on the last record all that was required was to not allow a further MoveNext.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
"Are you testing the move on a recordset clone and then passing back a bolean vaue which is set false if, the the case of a 'next', the move is beyond the last record?

Surely you have to attempt a moveNext to get an EOF condition?"

You test the EOF FIRST, and then MoveNext. Same with MovePrevious. I've been bitten by the AbsolutePosition method before and stopped using it long ago, and adopted the RecordsetClone methods.

0
 
MikeDTEAuthor Commented:
Hi LSMConsulting

Thanks for the follow-up.  What I have written is working well and I tried destructive testing i.e. clicking furiously on the same Next and Prev buttons but I cannot make it fall over.  Typically there are a maximum of 3 records in the recordset - a deposit payment, a staged payment and a final payment.   The AbsolutePosition method is working for me so I will continue to use it ... but I do note your comments.
0
 
Dale FyeCommented:
Scott,

"I've been bitten by the AbsolutePosition method before"

Can you explain?  Was that when working with the forms recordset, or the recordset clone.  I cannot recall ever having a problem with that technique, and would appreciate your insight.

Dale
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In earlier versions of Access (the 2000 format) I had instances where AbsolutePosition will return an incorrect or invalid value with larger recordsets (i.e. it will tell you you're at 4 when you're really at 5). I don't recall the specifics now (it was a long time ago) but I remember doing away with the code (which was similar to yours) and moving instead to using the Bookmark method (which is the preferred method, according to MSFT) to determine where I was in the recordset. I did continue to use the AbsolutePosition to fill my "record position" indicator (i.e. the "3 of 22 Records" box) but I stopped using it to determine my acutal position in the form's recordset.

Also, in some cases AbsolutePosition just won't work. For example, if base your form on an ADO Recordset you could easily find that AbsolutePosition always returns a -1, since (in many cases) and ADO Recordset doesn't report the RecordCount (which is, apparently, used when figuring the Absolute Position).

Finally, please note this (from here: http://msdn.microsoft.com/en-us/library/bb221121%28v=office.12%29.aspx):

"You shouldn't use this property as a surrogate record number. Bookmarks are still the recommended way of retaining and returning to a given position and are the only way to position the current record across all types of Recordset objects. In particular, the position of a record changes when one or more records preceding it are deleted. There is also no assurance that a record will have the same absolute position if the Recordset object is re-created again because the order of individual records within a Recordset object isn't guaranteed unless it's created with an SQL statement by using an ORDER BY clause."

(Note that I added the emphasis to the above statement)

MikeDTE: Glad that it's working for you. My intent was to include a comment for others who might see this.


0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now