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
To close the connection:
To retrieve the data:
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
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
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
To pass the Recordset to the Userform I have created two 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
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
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
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
We also need two routines that will allow us to scroll through the Recordset, both backward and forward:
Scroll Through Recordset
Finally, when the Userform is unloaded, we will set the Recordset to nothing:
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.