How to Get the Filtered Recordset from an Access Form in VBA

Learn how to retrieve a filtered recordset from an Access form using VBA with clear steps and sample code to ensure you only work with visible rows.
---
This video is based on the question https://stackoverflow.com/q/67057484/ asked by the user 'Gener4tor' ( https://stackoverflow.com/u/9930052/ ) and on the answer https://stackoverflow.com/a/67057541/ provided by the user 'Gustav' ( https://stackoverflow.com/u/3527297/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to get the filtered Recordset from an Access Form in vba?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Retrieve Filtered Recordset in Access VBA

Microsoft Access provides a robust platform for managing and manipulating data. However, when it comes to handling forms and recordsets, beginners might run into roadblocks. One common problem users face is how to retrieve a filtered recordset from an Access form when only specific data is displayed. This guide will guide you through the process and help you solve the issue of obtaining a filtered recordset in VBA.

The Problem

Let's set the stage. You have a form that displays a subset of records—let's say 58 rows out of 14,000 total records due to an applied filter. You want to use these filtered records in a function to perform some operations with them. The issue arises when you try to call your function with the following code:

[[See Video to Reveal this Text or Code Snippet]]

or

[[See Video to Reveal this Text or Code Snippet]]

In both cases, the rowcount returns 14,000 instead of the 58 that are currently shown. This is where the challenge lies.

The Solution

To get the filtered records, you need to open a recordset for the currently filtered data properly. Here’s how to do just that:

Step 1: Clone the Recordset

Start by creating a clone of the current recordset associated with your form:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Apply the Filter

Next, apply the filter that is currently active on your form:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Open a Recordset

Now, where the key change comes in—open the recordset based on the filtered data:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Call Your Function

Finally, you can now call your function with the filtered recordset:

[[See Video to Reveal this Text or Code Snippet]]

Complete Code Example

Putting it all together, here’s the complete implementation:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following these steps, you can successfully retrieve a filtered recordset from your Access form using VBA. This allows your code to operate precisely on the visible records instead of the entire dataset, making your applications more efficient and responsive.

Make sure to implement this solution in your Access applications to streamline your data processing tasks. Remember, working with filters effectively can save you a lot of time and increase the accuracy of your reports and functions!

Видео How to Get the Filtered Recordset from an Access Form in VBA канала vlogize