Livio/ May 7, 2018/ Excel, Excel VBA/ 0 comments

Building a searchable Userform using a Recordset

In my previous post How to create a disconnected recordset I showed how to create a disconnected Recordset with ADO. Now we will use this disconnected recordset to create a searchable userform. The data in the recordset will be displayed inside the userform and then we will build a search functionality for the user to easily find the record she is looking for. The data will sit in an Access database. It is a simple table with four fields: PersonID, FirstName, LastName, DateBorn.

Link to the Access file: Download Database

Link to the Excel file: Download Excel File

Connecting to the database and retrieving the data

The routines to connect to the access database and retrieve the data, as shown in my previous post, are:

To connect to the Access database:

Connect to Access Database

Connect to Access Database

To close the connection:

To retrieve the data:

Retrieving Access Data with VBA

Retrieving Access Data with VBA

Adding the Userform

I built a Userform called ‘UFormData’ and added all the controls. The bottom section displays the data whereas the top section is used to search the data and retrieve only those records matching the search. The top part has two frames with two option buttons each and below is a Textbox in which the user will type her search. The two command buttons Prev and Next are used to move to the Next and Previous records, if something is typed inside the search Textbox then these buttons will move to the next / previous matching record.

The Userform looks like this:

Searchable userform

Searchable Userform

Starting the Userform

The routine to load the Userform is displayed below. The Userform is shown if the RETRIEVE_PEOPLE function was successful and if the Recordset is not empty:

Searchable Userform

Searchable Userform

Userform code:

Two variables declared at module level: objRS is the Recordset whose data is displayed within the Userform, and dblBookMark used to store the position of the current record

Searchable Userform

Searchable Userform

To pass the Recordset to the Userform I have created two properties:

VBA Class Properties

VBA Class Properties

The Userform is being loaded with the following routine called ShowME, which will call another routine to fill the labels with the information of the current record in the Recordset:

Searchable Userform

Searchable Userform

When the user starts typing inside the search Textbox, the change event of the Textbox is fired which will reset the Recordset filter and then it will check whether the value of the Textbox is a null string or not. If it is a null string then it will clear the Textbox color, the label that displays the number of matches found and will pass to the Recordset the bookmark of the current displayed record. If it is not a null string then two routines are called:

Textbox Change Event

Textbox Change Event

The routine FilterRecordset will apply the filter to the Recordset based upon the Option buttons currently selected. The other routine AfterFilterRecordset  will check whether the search returned any match. If TRUE then it will call the FillLabels routine and set the background color of the Textbox to green, otherwise it will not fill the labels and it will set the background color of the Textbox to red, finally it will display in a label placed at the bottom the number of matches found.

Filter Recordset

Filter Recordset

We need to handle the event of when one of the option buttons change value from TRUE to FALSE or vice versa and call the above routines if the Textbox does not contain a null string:

Option Buttons Change Event

Option Buttons Change Event

We also need two routines that will allow us to scroll through the Recordset, both backward and forward:

Scroll Through Recordset

Scroll Through Recordset

Finally, when the Userform is unloaded, we will set the Recordset to nothing:

Terminate Event

Terminate Event

This was an easy example to show you how to use a Disconnected Recordset to populate and then filter your searchable Userform. It can also be used to populate and filter Listboxes or Comboboxes or to build a more advanced search with more conditions. 

Leave a Comment

Your email address will not be published. Required fields are marked *

*
*