Microsoft Access is a relational database management system from Microsoft. Access is widely used by small businesses, within departments of large corporations, and hobby programmers to create ad hoc customized systems for handling the creation and manipulation of data. Its ease of use and powerful design tools give the non-professional programmer a lot of power for little effort.

Apple Online Store

Visual Basic for Applications (VBA) is a programming language. With the addition of the VBA Editor, it is possible to use Access for far more than just basic presentations. Access VBA can automate complex tasks and perform complicated series of actions. The intent of this page is to show some useful Access VBA  examples that I have picked up in the process of creating my own applications. Click on the bulleted items below to view Access Visual Basic codes and tricks.

Filter Query Results from a Form

You should be aware that you can limit the results of a database select query by using particular criteria. What if you are not too sure about what criteria applies to the particular field? Wouldn't it be better to be able to choose this from a list rather than having the standard Microsoft Access input box appear? You can create a simple Access form, containing a combo box, and base the query criteria on the value chosen in the form as detailed below.

To begin with we will create a table (tblStudentGrades) in Microsoft Access containing some values to search on. The table is shown below:


The next stage is to create the Access query (qryStudent) that we will later apply criteria to. The query design, shown below, prior to any criteria being applied:

Once we have created the query above, we then need to create the Microsoft Access database form (frmSearchStudent) that we are going to use to apply the criteria to the query. This form will contain the drop-down combo box with a list of values to choose from that will run the query. The form design is shown below:

The combo box will be linked to the table tblStudentGrades.

 Within the form's design view, make sure the Properties for the combo box are visible by clicking the Properties button.

Click to activate the combo box. Under the Format Tab within the Properties box, name the combo box "cboSearch". Next, click the Tab "Event" to activate its properties.

On the "On Change" Event, click the expression builder button...

 to open the "Choose Builder" dialog box.

Choose "Code Builder" and you will be taken to the Access Visual Basic Editor. Within this editor, copy the following code within the procedure:

DoCmd.OpenQuery "qryProducts_And_Locations"
Me.cboSearch = ""

Your Visual Basic code should look similar to this:

Private Sub cboSearch_Change()
 DoCmd.OpenQuery "qryStudent"
 Me.cboSearch = ""
End Sub

The above code runs the query and re-sets the search drop down list to a blank selection. What we now need to do is apply the criteria...

[Forms]![frmSearchStudent]![cboSearch]

...to the query design that will ensure that the filter is applied based upon the value chosen in the form and this criteria is shown below:

So now, when we choose a value from the drop down list on the form...

...the query will run and return only the item chosen from the list:

You can download this Microsoft Access 2003 Database "filter_combo_box" here.
 

Back to Top^

Generate a Unique ID

The following code will generate a unique ID for a new customer based on Last Name (field: LastName from table: Customers), and First Name (field: FirstName from table: Customers), entered within a form. The unique ID created will be exported to the text box CustomerID within the form and recorded in the table: Customers. If you try to enter an ID that is already in use, Access won't accept the new entry. Some limitations to this technique occurs when you have customers with the same name; however, this script can be used for other multipurpose ID tracking (products, clients, etc.)

Private Sub LastName_AfterUpdate()
'Create variables to hold first and last name
' and customer ID
Dim fName As String
Dim lName As String
Dim cID As String

'Assign the text in the LastName text box to
' the lName variable.
lName = Forms!customers!LastName.Text

'You must set the focus to a text box before
' you can read its contents.
Forms!customers!FirstName.SetFocus
fName = Forms!customers!FirstName.Text

'Combine portions of the last and first names
' to create the customer ID.
cID = UCase(Left(lName, 3) & Left(fName, 2))

'Don't store the ID unless it is 5 characters long.
' (This would indicate both names not filled it.)
If Len(cID) = 5 Then
Forms!customers!CustomerID.SetFocus

'Don't change the ID if it has already been
' entered...perhaps it was changed manually.
If Forms!customers!CustomerID.Text = "" Then
Forms!customers!CustomerID = cID
End If
End If

'Set the focus where it would have gone naturally.
Forms!customers!Address.SetFocus

End Sub

 

Back to Top^