Continuous Form Paging? (1 Viewer)

[AccessBug27]

I have created a continuous Form, ie multiple records on the same form.

But I want to limit the amount of records displayed on the form from the large table.

Maybe have page forward/backward command buttons on the header or footer using VBA code?

 

[The_Doc_Man]

There IS a passive approach, perhaps.

Control the number of displayed records by limiting the size of the display area. Then just use the standard navigation buttons to visit the next or previous records.

 

[AccessBug27]

I have created a continuous form, without scroll bars which limits the amount of records displayed. And using navigation buttons can move forward or back etc.

I want to create 2 custom command buttons, page forward and page back. I need to find the record number of the topmost displayed record so I can use it as a reference to page forward and back.

How can I find out the record number?

 

[missinglinq]

As Doc said, you need to figure out how many Records you want to show, at a time, then dimension your Form accordingly. Then you can use code like this:

Private Sub Form_Load()
 
 Dim i As Integer
 
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

If Me.CurrentRecord = RecordsetClone.RecordCount Then Exit Sub
   
   For i = 1 To 7
       
    DoCmd.GoToRecord , , acNext
    
    If Me.CurrentRecord = RecordsetClone.RecordCount Then Exit Sub
   
   Next i
   
End Sub


Private Sub PageForward_Click()
   
Dim i As Integer
   
   If Me.CurrentRecord = RecordsetClone.RecordCount Then Exit Sub
   
   For i = 1 To 8
       
    DoCmd.GoToRecord , , acNext
    
    If Me.CurrentRecord = RecordsetClone.RecordCount Then Exit Sub
   
   Next i
          
End Sub

Private Sub PageBackward_Click()
  
Dim i As Integer
   
   If Me.CurrentRecord = 1 Then Exit Sub
   
   For i = 1 To 8
    
    DoCmd.GoToRecord , , acPrevious
    
    If Me.CurrentRecord = 1 Then Exit Sub
    
   Next i

End Sub

The above code is set to show 8 Records at a time, but you can adjust that by simply replacing the 8 in each of these statements, with your desired number of Records:


For i = 1 To 8

Also you'll need to replace the 7 in

For i = 1 To 7

in the first bit of code, with your desired number of Records minus 1.

Linq ;0)>

 

[static]

...or

If the main field is text (if there is a main field) you could filter records on the first letter of that field.
Rather than a previous next button, filter records by starting letter a-z.

Or if it's display only (no editing) use a (dao/ado) recordset to filter an ado recordset by id and bind that to the form.

 

[missinglinq]

And how would filtering records on the first letter of that field always display a fixed number of records at a time...which is what the OP wants?

Linq ;0)>

 

[static]

Where did he say he wanted a fixed number?

 

[AccessBug27]

Actually it was a simple solution !!

I discovered that hitting the PageUP/PageDown keys on the keyboard worked. I just simply wrote code to simulate the key presses

Private Sub PageDown_Click()

SendKeys "{pgdn}"

End Sub

Private Sub PageUp_Click()

SendKeys "{pgup}"

End Sub

 

[isladogs]

Using SendKeys can cause problems in different versions of Access and is therefore best avoided

Better to use expressions like:
DoCmd.GoToPage(PageNumber, Right, Down)

As you are staying on the same page, PageNumber=1
Right & Down are in twips so you will need to multiply the height of your form by the number of twips in an inch or cm
1 inch = 1440 twips, 1cm=567 twips

For this purpose, ignore the 'Right' argument

So something like

Private Sub cmdPgDown_Click()
   DoCmd.GoToPage 1, , 10000
End Sub

Private Sub cmdPgUp_Click()
   DoCmd.GoToPage 1, , -10000
End Sub

 

[AccessBug27]

OK, I see your point, although the application is to be used for our own purposes, not being distributed where different versions of access could be used.

I probably will change it for consistency though

Thank you

 

[isladogs]

Ok but at some point you may update to a new version yourself