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! 

 

Using VBA to Filter Report Results

Earlier this week I was asked how to filter the results of a report using a criteria selected from three Combo Boxes on a search form.  You can see Marwa's question posted on my Access Tutorial Facebook Page (Tuesday 10th January 2012).  The solution I suggested involved creating a Parameter Query with three separate criteria referencing the values contained in the Combo Boxes on the Search Form.  You can see the full response I gave in my comment below her question.

By co-incidence I have also been working on a similar task in my work as an Access Developer.  One of the projects I am currently working on is a Journal Database which uses descriptive tags to categorise journal entries.   So, for example, if users made an entry about their progress learning Microsoft Access, they might use tags such as  "Database Development", "Reports", and "Filters" to categorise their entry.

I wanted to create an quick way for users to search their journal by tag name, so that all records categorised by a given tag may be extracted and displayed in a report.  To do this I created a really basic search form that consisted of just one unbound Combo Box.

The ROW SOURCE for the Combo Box is a table containing all of the tag names used in the database.  The idea is that the user selects a tag from the drop down list, thereby triggering a block of VBA code in order to open the report filtered by the selected tag name.  The screen shot below shows the report filtered by the tag name "ADO.Net":

The underlying table structure for this report is based on a query with
three tables from a many to many relationship.  There is a table for the Entries, and a
separate table for Tags.  There is also a junction table to store each instance of
a tag used in any particular Entry.  As such a journal entry can be related to
many tags records, and any tag record can be related to many Entries.

So how does this work?  When the user selects a tag name from the drop down list, Access fires the Combo Box AFTER UPDATE event.  This in turn executes the followingVBA code that I wrote for this event:

Private Sub comTag_AfterUpdate()

    Dim varSQLWhere As String
    varSQLWhere = "tagId = '" & Me!comTag & "'"
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenReport "rptTagSearch", acViewReport, , varSQLWhere

End Sub
NB for sake of clarity, I have removed all code related to error handling. 

The code begins by defining a string type variable called varSQLWhere - this is going to contain the code for an SQL WHERE clause (minus the WHERE keyword).  Next I set the value of this variable to "tagId = '" & Me!comTag & "'".  TagId is the name of the primary key of the table containing all of the tag names.  In fact, tblTags only contains this one field.  Me!comTag is a reference to the name of the Combo box used on the search form.  Note the manner in which it has been concatenated into the string variable. Due to fact that the value contained in the combo box is itself a string, I have had to build two single quotation makes into the varSQLWhere string.  So for example, if the user had selected the tag name 'Visual Studio' in the Combo Box, the contents of the string variable would be "tagId = 'Visual Studio'" .

The next line of code closes the search form so it does not get in the way when the form opens.

The penultimate line of code is the DoCmd.OpenReport method.  This not only opens our report, but also passes our varSQLWhere variable as one of the method's parameters (the WhereCondition). When this is passed, only records matching our WhereCondition are displayed in the report results.  As such, our report effectively filters our report results by the tag name selected by the user in the search form combo box.

It's not too difficult to apply this type of solution to the application that Marwa is building. The only real difference is that her Database search form consists of three combo boxes.  This means that she would also need to use a  Command Button ONCLICK event to start the search rather than relying on one of the Combo Box AfterUpdate Events.  Then she would need to construct the varSQLWhere variable from all three combo box values.  This would look something like:

varSQLWhere = "fld1 = '" & Me!Combo1 & "' AND fld2 = '" & Me!Combo2 & "' AND fld3 = '" & Me!Combo3 & "'"

So its slightly more complicated, but the same principles apply.

Filtering Report Results Using the Filter Property

In this tip we are going to filter the results of a Report to display records with order amounts above a given value.  In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button.  The user enters an amount value in the text box, and clicks the command button.  The command button then runs a small amount of VBA code to open the report.  As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders).  In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).  

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post).  I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user.  To add this code:

1. Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).  
2. When the PROPERTY SHEET opens, click the EVENT TAB.  
3. Then select the ON CLICK event by clicking into that particular cell of the grid.  
4. A three dot  symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.  
5. Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor.  The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

---

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

    DoCmd.OpenReport "rptOrdersFiltered", acViewReport

    DoCmd.Close acForm, Me.Name

Leave:

    Exit Sub

    

MyError:

    MsgBox "Error " &  Err.Number &  ": " &  Error$

    Resume Leave

End Sub

---

The two important lines in this section of code begin with DoCmd (meaning do command).  The first of these is the command to open the form in Report View.  The second closes the Dialog form once the report has opened.  NB it is very important the report opens before  the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.  

The Report

The quickest way to create the Report is to base it on our table.

1. Select tblOrders in the Navigation Pane.  It will highlight in orange.
2. Then select  the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP). 

This creates a basic report which we can then then modify in DESIGN VIEW as required. 

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

1. Click the Square Box in the top right hand corner of the Report Design Grid.  This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
2. Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
3. Click the DATA TAB of the pane.
4. Make sure the SELECTION TYPE is set to REPORT.  This should be the case if you carried out step 1 successfully.  Otherwise just change the selection to Form in the drop down list.
5. Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] .  This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
6. Then set the FILTER ON LOAD PROPERTY to YES.  This ensures the filter is applied when the forms loads.
7. Save the Report as rptOrdersFiltered.
8. Close the Report

Figure 5: The Form's Filter Properties.  Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run.  Just click the frmParameter form in the Navigation Pane to begin.  The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter: 

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.