Display a Random Customer Record using VBA and Form Filter

Suppose you are responsible for managing an Access Database containing customer records.  You have been asked to introduce a contact management feature whereby the person operating the database can extract a random customer record.  This blog post walks you through the VBA code needed to generate a random number and use it to filter out a customer record, all at the click of a form button.

In order to demonstrate this we will be using the following data:

Table of Fictitious Customer Records

We will display the random customer record on the following form:

The VBA code starts when the user clicks the "Get Random" button to display a random customer record on the form. Lets begin by creating the control button.

Creating the Control Button

1. Open the form in DESIGN VIEW

2. Click the BUTTON icon in the CONTROLS group on the FORM DESIGN ribbon.

3. Click the location on the form grid where we will be positioning the button.  Resize the control button by clicking on one of the buttons' corner or edges and dragging to enlarge or contract as appropriate.

4. Click the PROPERTY SHEET icon from the TOOLS group of the FORM DESIGN ribbon if it is not already open.

5. Ensure the control button is selected. Click the button to select if it is not. Now the property    sheet is displaying the properties specific to our button.

6. Change the button CAPTION property on the FORMAT tab to "Get Random".

7. Change the button NAME property on the OTHER tab to "btnRnd"

Check how the button looks by changing  to FORM VIEW from the VIEWS group of the FORM DESIGN ribbon. If its OK, we are now ready to add the VBA code which runs when the command button is clicked.

Adding the VBA Code

1. Open the form in DESIGN VIEW, and reopen the PROPERTY SHEET if it isn't already.

2. Select the control button by clicking on it.

3. Select the EVENT TAB on the PROPERTY SHEET, and click inside the grid cell adjacent to where it says ON CLICK.

4. Click the ellipse button ("...") at the end of the cell to open the CHOOSE BUILDER dialog, Select CODE BUILDER from the drop down list and click OK to open the VBA editor.

 

You should now see an empty sub procedure called btnRnd_Click(). This is the control button's event handler.  This means the code we enter here will run when the user clicks the command button at runtime.

 

The VBA to Generate a Random Number

In order to retrieve a random customer record we need to generate a random number which corresponds to one of the customers in the database table. We can do this using an inbuilt VBA function called Rnd.  This generates a random decimal number between 0 and 1. For example, it  may return the value 0.5924582.  For this to be of any use to us, we need change the decimal number to an integer and multiply it by the number of customer records in the table.  So, for example, our table contains 10 customer records so we need to multiply the value returned by rnd by 10 and use another function called int to convert it from decimal to integer. We do this as follows:

    Int(rnd * 10)

This returns a value between 0 and 9.   We now need to add 1 to the value to get a number between 1 and 10:

    Int(rnd * 10)+1

Just one more thing to note: computers are unable to return a true random number on their own.  They return a seed number based on a pre-existing sequence of numbers. However, when we initialize the random number generator with the Randomize statement, the seed number returned is based on the system timer, which effectively makes the returned value a true random number.

So to put all this together we are going to create our own custom function to return a random integer number based on the number of records in our database table. The number returned will correspond to a particular record in the table. We shall call our function getRandom and will pass the number of customer records as the parameter. The code for the function is as follows:

Private Function getRandom(intRecordCount As Integer) As Integer  

    Randomize

    getRandom = Int((intRecordCount * Rnd) + 1)  

End Function

For more information on how custom functions work, please check out my post on Writing Custom Functions for Access VBA.

The next step is to write the VBA code to connect the random number returned by our getRandom function with it's corresponding customer record in the database table.  We shall do this by looping through a DAO Recordset containing records from the customer table.

Looping through the DAO Recordset

Before we can create the code to filter a customer record based on our random number, we need to find a way of mapping any potential number returned by the getRandom function  (in our case this is between 1 and 10) against the records in the database table. Whilst it is tempting to use the returned random number directly against the value stored in the customer_id field in a form filter WHERE clause - ie "customer_id = " & getRandom(intRecordCount) - there is a problem with this.  That is to say, although the customer id's in our table go up in an unbroken sequence from 1 to 10, it is possible that one of these records will be deleted in the future.  If and when this happens, no customer record would be found using the above WHERE clause if the random number corresponded to the deleted record. Moreover, the record count would not include the deleted record so the random number returned would be within a range which did NOT include the last record in the table.

What we must do, therefore, is create a DAO Recordset containing all the records in our customer table and use the value returned by getRandom to step through each customer record in the set until this value has been reached - eg if getRandom returns the value 8, we simply loop though the recordset to the 8th record contained therein.  Once we get to this record we can look up the actual value in the customer_id field, and then use this as the basis of our form filter WHERE clause.

If you are unfamiliar with how DAO recordsets work, you may want to check out my post on Accessing Data with VBA Code - Introducing the DAO Recordset; otherwise, lets jump straight in, and run through the code we will be using.

The first step is to create a new instance of a DAO recordset object and set its value to that of our  tblCustomer table.

Dim rstCustomers As DAO.Recordset

Set rstCustomers = Application.CurrentDb.OpenRecordset("tblCustomer", dbOpenSnapshot)

You may have noticed the second parameter we have used when opening the recordset is dbOpenSnapshot.  This means our recordset will be a static snapshot of the customer table. Unlike the versatile dbOpenDynaset type, records cannot be updated using dbOpenSnapshot, but in cases where editing is not required, the snapshot type is the more efficient option. 

In the next step we are going to count the number of records in the rstCustomers recordset and store the value in an integer variable called intCount.  To count the number of records in rstCustomers we will read the RecordCount property using the following syntax:  rstCustomer.RecordCount.  However, before doing so, it is first necessary to visit all the records in the recordset so they can all be counted. The best way to do this is to use the recordset MoveLast method which moves the recordset cursor though each of the records before pointing to the last record in the set. The syntax for this is rstCustomers.MoveLast. All this is done in three lines of code as follows:

Dim intCount As Integer

rstCustomers.MoveLast

intCount = rstCustomers.RecordCount

Now that we know how many records are in the customer table, we can use our getRandom function, passing intCount as the parameter, to obtain a random number.  We then step through the records in the recordset until we reach this number.  Once this record has been reached, we can read the value contained in the customer_id field.  

To do all this we shall begin by defining an integer variable called intCustomerId.  This will be used to store the customer id once we have arrived at the random customer record.  Before we set up the loop, we need to move the recordset cursor back to the first record using the MoveFirst  method.  This is because the cursor is still at the last record from when did the record count in the section of code above.  We will use a For...Next loop to step through the recordset, starting at the first record and ending at the record which corresponds to the value returned by our getRandom function. This is done with the following line of code: For i = 1 To getRandom(intCount).  The first statement within the loop sets the value of the intCustomerId variable to the value contained in the customer_id field of the record where the recordset cursor is located. We reference the field value using the recordset name ("rstCustomers") followed by an exclamation mark ("!") and then the field name ("Customer_Id") as follows: intCustomerId = rstCustomers!Customer_Id. Once this value is stored in our intCustomer variable we can move the recordset cursor to the next record using the MoveNext method: rstCustomers.MoveNext. Finally the loop counter is incremented by 1 with Next i.  If the end value of the loop counter had been reached before the Next statement had executed, the loop counter will still increment by 1, but the program flow will exit the loop and move to the statement following Next i.  

Dim intCustomerId As Integer

rstCustomers.MoveFirst

For i = 1 To getRandom(intCount)

           intCustomerId = rstCustomers!Customer_Id
           rstCustomers.MoveNext

Next i    

 

So we have stepped through the recordset and arrived at our random record storing the value of it's customer_id field in the intCustomerId variable.  Now the program flow has exited the loop we are going to use the intCustomerId value to display the record on our form.  This will be done by means of a form filter containing a WHERE clause based on the stored customer_id. The syntax for the WHERE clause is "Customer_id = " & intCustomerId and the syntax to create form filter is DoCmd.ApplyFilter filtername, whereclause, controlname.  Although the ApplyFilter method has three parameters, we are just going to use the second - the WHERE clause.  As such we write the full statement as follows:

DoCmd.ApplyFilter , "Customer_id = " & intCustomerId

(NB Although the first parameter is left blank we still need the separating coma just before the WHERE clause string.  The last parameter is also left blank, but a separating coma after the WHERE clause is not needed.)

Lastly we need to close the rstCustomer recordset and set its value to NOTHING.  This clears the memory that it had been taking up and allows us to reopen the recordset again the next time the user clicks the getRandom button. We place this code within a IF conditional statement which checks the recordset is still open before it executes thereby avoiding any error from occurring if it is not.

If Not rstCustomers Is Nothing Then

    rstCustomers.Close: Set rstCustomers = Nothing

End If

All we need to do now is look at how all the code we have run through above is organised in the code module for the frmCustomer form.

As you can see from the screenshot above, our code is divided into two Sub Procedures and one Function, btnRnd_Click(), RandomCustomerRecord() and getRandom() respectively.  When the user clicks the Get Random button on the Customer Form, the code in the btnRnd_Click() sub executes first. This is the event handling sub that Access created automatically when we set up the control button at the start of the tutorial.  There is just one statement here calling the second sub procedure which is RandomCustomerRecord().  This contains the main body of the code for retrieving the random customer record from the database and displaying it on the form.  The reason for giving this it's own sub rather than placing it in the code handler, is that we can potentially reuse this code by calling it from different locations in the program as required.  The only task this code does not do is generate the random number representing a specific customer record for selection.  This code is located in the getRandom() Function which is called from the FOR statement of the FOR...NEXT loop. The number returned from the function determines how many records are stepped through in the rstCustomers recordset before getting to the record which is to be displayed right at the end of the process.

Once RandomCustomerRecord() has the random number and retrieved the customer record from the recordset, the form filter is applied using the value of cutomer id.  The form will then display the record with the same customer_id as that stored in the sub's intCustomerId variable.

And there we have it: a random customer record at the click of a button!