What’s a developer sitting in front of his keyboard going to do with a few free hours? That’s right, have some fun and try and replicate Web Page pagination in his Access form.
In the demo, I have it displaying 10 records per page (as shown in the images above), but you can change that by simply editing the VBA line
Private Const lRecPerPage As Long = 10
and changing the 10 to whatever number of records you wish to display at a time.
There are no ActiveX controls, no APIs, … so this will work in both 32 and 64-bit versions of Access.
This is a good approach in the sense that it avoids fetching the entire table and instead limits the records being pushed and pulled.
Also, note the the subform is currently sized to display 10 records with no scrollbars displayed, so if you try using a lRecPerPage value greater than 10 you will need to either resize the subform or enable the vertical scrollbar.
I hope a few of you out there will find it useful.
Disclaimer/Notes:
If you do not have Microsoft Access, simply download and install the freely available runtime version (this permits running MS Access databases, but not modifying their design):Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime
In no event will Devhut.net or CARDA Consultants Inc. be liable to the client/end-user or any third party for any damages, including any lost profits, lost savings or other incidental, consequential or special damages arising out of the operation of or inability to operate the software which CARDA Consultants Inc. has provided, even if CARDA Consultants Inc. has been advised of the possibility of such damages.
Download a Demo Database
Feel free to download a 100% unlocked demo copy by using the link provided below:
Download “Access – Pagination (x32 accdb)” pagination.zip – Downloaded 7304 times – 56.90 KBVersion History
| Version | Date | Changes |
|---|---|---|
| V1.000 | 2022-03-28 | Initial Public Release |
| V1.001 | 2022-03-29 | UI & Cosmetic Improvements
|
| V1.002 | 2022-03-29 | Code Simplification (what was I thinking originally!?) |
Other References on the Subject
The data in the demo file was generated from:
Merci de partager, très intéressant comme approche, j’aime bien l’idée du “Top” dans le SQL
La constante lRecPerPage ne fonctionne pas pour plus de 10 records pourtant la query retourne bien le bon nombre d’enregistrements.
La constante marche bien. Le problème est que j’ai dimensionné le sous-formulaire pour afficher que 10 enregistrements et j’ai désactivé les barres de défilement. Donc, il faut que tu redimensionne le sous-formulaire ou que tu active la barre de défilement verticale.
Tu as bien raison, ça fonctionne Nickel !
Merci encore, ça va m’être utile pour mes applications mobile.
Bonjour,
J’utilise régulièrement msacces et c’aurait été tres intéressant d’avoir une version android de ms access afin de créer des bases de données et des applications mobile.
Merci
Effectivement, il aurait été bien que Microsoft pousse un peu plus Accces pour une version mobile, mais malheureusement cela va rester dans nos rêves…
Personnellement j’utilise certaines de mes applications (logiciel d’inspection de bâtiments) avec des tablettes Surface Microsoft avec le runtime et j’ai adapté mes applications pour les mettre plein écran et adapter au tactile et j’utilise aussi la caméra intégrer. Bref une façon contournée du vrai mobile, mais très fonctionnelle.
Thanks for sharing this interesting ideas Dan. It’s hard to beat Access in UI enhancement or customization, I wish in the future a web development tool like Access will be available. I’m also hoping fo a robust compiler from accdb to accde like foxpro or rbase. MS accdb to accde compilation is so weak that some of the vba codes were still readable. As of this time Access is not a contender if you want a secure front-end applications.
Sadly, I doubt you’ll see any of that. MS has clearly shown a lack of investment in Access in the past 5-10 years and what they have focused on, IMHO, has been in many instances a waste and/or poorly done. Add to that the fact that Access is now more unstable and buggy than it ever has been in its history. Microsoft has effectively taken what was the Alpha RAD tool in the 80s-90s-2000s and reduced it to mediocrity.
Furthermore, Access has never been secure! When you can extract then BE password(s) as plain text, your tool is insecure. When there is no way to stop GetObject from working against your database, your tool is insecure. When you can pay to have your compiled database uncompiled, your tool is insecure. See a trend! Making things worse is that these points have been flagged numerous times over the years and still Microsoft has done nothing to address them. All we hear about is security, yet they leave gaping security holes open in their business critical data application!
Hello Dan, im very impressed by your idea of implementing Pagination on an Access Form
however i want to replicate the same idea but with only 4 cmdButton (FirstPage, PreviousPage, NextPage, LastPage), and i cant make it to work properly, a bit of help will be apreciated
Not 100% sure if this is what you’re after, this is untested, but take a look at: https://1drv.ms/u/s!AjYnefG2siYSkUXISx9X28EnfAca?e=ldD6t5. Hope it helps.
that’s the perfect solution for my needs, thanks mate
My pleasure.
Hello Daniel,
thank you for sharing your concepts and demos.
I’ve made some adjustments to the form’s VBA module to deduplicate the code, fix the centering of the page buttons and enable reusability of the form for varying source tables/queries or ORDER BY columns.
I’ll happily send you the code to use for a V1.003 if you get in touch.
Kind regards