Form to show 20 records at a time (1 Viewer)

T. McConnell · Sep 2, 2019

Hello everyone,
I am looking at the best way to open a form built from a table. In my case a customers table. What I would like to do is on a button it opens a customers form displaying the first 20 or so customers, then at the bottom have a Next 20 button to display the next 20 customers. I know a way to display just 20 records at time in a query, but looking to be able to expand and allow the client to click on a button to scroll through the customers table through a form. I know I could just setup the record navigation to scroll through, but want to have a simple form look to show the first list then give the option to go through the whole table basically. Any suggestions would be greatly appreciated. If this is posted elsewhere let me know.
Thanks all!

Micron · Sep 2, 2019

Maybe this isn't what you want, but if the form was sized to show 20 records, wouldn't a simple click on the scroll bar take you down one page (the next 20)? Or is there the possibility of users with different monitor resolutions?

What you want should be doable with code, but maybe there are better/simpler ways.

T. McConnell · Sep 2, 2019

Micron,
Different screen resolutions are there, but shouldn't be an issue doing that. I was hoping to do it a cleaner way maybe. Thanks for the info though.

theDBguy · Sep 2, 2019

Hi. Probably not exactly what you want either but try something like:

Code:

DoCmd.GoToRecord , , acNext, 20

T. McConnell · Sep 2, 2019

Does the form need to be a continuous one in order for this to work as well. When I do so, if I click on the button to it goes to the end of the list and then gives error can't go to that record. If I display all records it shows more than the amount I want to see at once. So basically if I only want to show 20 records, and I have 50 I can set the query to show 20, but when clicking on the button it just advances to the the last record and then gives an error if going any further.

Micron · Sep 2, 2019

I can set the query to show 20, but when clicking on the button it just advances to the the last record and then gives an error if going any further.

That was one issue I expected would have to be dealt with if going for a code solution, but I thought the click on the scroll bar (not on the button, but on the slider part) might make sense.

I suppose one code approach would be to get a record count and use the mod function to return the remainder, which would then be the count of the last batch of records.

Micron · Sep 2, 2019

Not seeing how any code solution is "cleaner" that simply clicking on a scroll bar but maybe try

Code:

Dim recPos As Long

On Error GoTo errHandler
recPos = Me.Recordset.RecordCount - Me.CurrentRecord
If recPos > 0 And recPos < 20 Then
  DoCmd.GoToRecord , , acNext, recPos
Else
  DoCmd.GoToRecord , , acNext, 20
End If

exitHere:
Exit Sub

errHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume exitHere
End If

arnelgp · Sep 2, 2019

was thinking of using a function to group your records on a query.
autonumber field is required for you table.

Code:

Option Compare Database
Option Explicit

Public Function fncList(ByVal p_id As Long) As Integer
    '
    ' arnelgp
    ' 02-sep-2019
    '
    ' p_id      autonumber value or -99.
    '           if -99 is passed, it will reset the collection object.
    '
    Const MAX_ROWS      As Integer = 20
    Const NO_ERROR      As Integer = 0
    Static col_values   As VBA.Collection
    Static int_counter  As Integer
    Dim int_ret         As Integer
    If p_id = -99 Then
        Set col_values = New VBA.Collection
        int_counter = 0
        Exit Function
    End If
    If col_values Is Nothing Then
        Set col_values = New VBA.Collection
        int_counter = 0
    End If
    On Error Resume Next
    int_ret = col_values(p_id & "")
    If Err.Number <> NO_ERROR Then
        Err.Clear
        On Error GoTo 0
        int_counter = int_counter + 1
        col_values.Add (int_counter \ (MAX_ROWS + 1)) + 1, p_id & ""
        int_ret = (int_counter \ MAX_ROWS) + 1
    End If
    fncList = int_ret
End Function

Public Function fncResetList()
Call fncList(-99)
End Function

create a query that will call the function:

Code:

select *, fncList([ID]) As [Group] from yourTable;

on your form's button (btn_PageDown, btn_PageUp) add some code:

Code:

Option Database
Option Explicit

Private intGroup As Integer

Private Sub btn_PageDown_Click()
    intGroup = intGroup + 1
    If intGroup > DMax("[Group]", "yourQuery") Then
        'do nothing
        intGroup = intGroup - 1
    Else
        Me.RecordSource = "select * from yourQuery Where [group]=" & intGroup & ";"
    End If
End Sub

Private Sub btn_PageUp_Click()
    intGroup = intGroup - 1
    If intGroup < DMax("[Group]", "yourQuery") Then
        'do nothing
        intGroup = intGroup + 1
    Else
        Me.RecordSource = "select * from yourQuery Where [group]=" & intGroup & ";"
    End If
End Sub

you must reset the collection on the Form's Load and Close events:

Code:

Private Sub Form_Load()
Call fncList(-99)
End Sub

Private Sub Form_Close()
Call fncList(-99)
End Sub

Form to show 20 records at a time (1 Viewer)

T. McConnell

Registered User.

Micron

AWF VIP

T. McConnell

Registered User.

theDBguy

I’m here to help

T. McConnell

Registered User.

Micron

AWF VIP

Micron

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread