Microsoft Access Tips

Analysing Data with Access Crosstab Queries

2025-08-25T14:10:00.000-07:00

In the previous blog post, we used a data set downloaded from the UK based HM Land Registry website to demonstrate how Access can be used in the context of Data Analysis. As you may remember, the data set contained details of 2023 house sales in the Salford area of Greater Manchester, and we created a series of Access Queries to count the number of sales which occurred in seven separate price bands. This gave us an overview of what had occurred in the Salford area housing market that year.

In today’s post we are going to process the same data set with an Access Crosstab Query to give us a breakdown of average house sale price by property type and postcode area. This will show us the differences in average house sale price according to where a property is located and the type of building it is (ie whether the property is detached, semi-detached, terraced, or a flat). As we shall see, our crosstab query will restructure the dataset in such a way that property type and postcode values from the original table become row and column headings in the resulting new table. Furthermore, the values produced in the new table are derived from an aggregated average of individual house sale amounts from the original.

Let’s consider what this means in practice. The original table is comprised of rows which represent individual house sales with each column containing a specific piece of information about the sale. Here is a sample of the 2,942 record dataset we downloaded and cleansed in the last blog post (see Using Access for Data Analysis).

Figure 1: Sample from original dataset of house sales in Salford during 2023.

As we can see each house sale record has its own unique id, sale amount (price_paid), date (deed_date), postcode and property type. As stated above, we are going to create a crosstab query which will restructure the table so that each row represents a property type and each column a postcode area. Each value in the table is the average sale amount (ie price_paid) for all properties for the property type and postcode area of its particular row and column respectively.

The table below shows how the crosstab query results will look:

	Postcode	Postcode	Postcode	Postcode	Postcode
Property type	Average £	Average £	Average £	Average £	Average £
Property type	Average £	Average £	Average £	Average £	Average £
Property type	Average £	Average £	Average £	Average £	Average £
Property type	Average £	Average £	Average £	Average £	Average £

Before we look at how to create the crosstab query itself, let us first clarify what the property type and postcode data actually means. Property type data can be one of four different letters – D, S, T or F. These are the first letters of the following property types – Detached, Semi-detached, Terraced or Flat. For those of you outside of the UK and unfamiliar with these property types: Detached means a stand-alone building containing a single home; Semi-detached means a single building comprised of two separate homes; Terraced means a row of multiple adjoining homes; and Flat means a large building divided into multiple apartments. Detached homes tend to be the most expensive property type, followed by Semi-detached, Terraced and Flats respectively. However, location has a direct bearing on the property’s sale price, so a semi-detached property in a desirable area may be more expensive than a detached property located somewhere less desirable. This is where the properties postcode value comes in. Postcodes are comprised of two separate parts eg M28 1FU. The full postcode tells us which part of a street the property is located, so each full postcode may be shared by a small number of other properties on the same street. The first part of the postcode eg M28, on the other hand, relates to the general area where the property is located, so there can be hundreds of other properties sharing this part of the code. As such this can give us a good idea about the desirability of the properties location, and it is this part that we shall use for the column headings in our crosstab query.

Creating the Access Crosstab Query

Let us now look at how the crosstab query is created.

In the previous blog post on Using Access for Data Analysis we downloaded a dataset from the UK based HM Land Registry website and imported it into an Access table. We called the table tblSalfordHouseSales (after data cleansing). We shall be using this table to create the crosstab query below.

Here are the step by step instructions:

Open the Access database in which tblSalfordHouseSales is located and select the CREATE tribbon.
Click the QUERY DESIGN icon from the QUERIES group to open the QUERY DESIGN window.
Click the ADD TABLES icon from the QUERY SETUP group of the QUERY DESIGN ribbon. This opens the ADD TABLES sidebar (if it is not open already).
Select the TABLES tab from the ADD TABLES sidebar, and drag the tblSalfordHouseSales table onto the top section of the QUERY DESIGN window.
Next, we need to click the CROSSTAB icon from the QUERY TYPE group of the QUERY DESIGN ribbon. This adds CROSSTAB row to the QUERY DESIGN grid in the lower half of the QUERY DESIGN window.

Figure 2: : The CROSSTAB icon in the QUERY TYPE group of the QUERY DESIGN ribbon.

Figure 3: The QUERY DESIGN grid after selecting property_type in first column. Notice the extra CROSSTAB row heading appear after clicking the CROSSTAB icon in the QUERY TYPE ribbon group.
The first field we are going to add to the QUERY DESIGN grid is property_type. Do this by selecting the property_type field from the drop down list in the first column of the FIELD row of the QUERY DESIGN grid, or by dragging property_type down from the tblSalfordHouseSales table box in the top half of the window.
As explained above, each data value in the property_type field will act as a row heading in our crosstab query. To set this up on the QUERY DESIGN grid, we go down to the CROSSTAB row on the property type column, and select ROW HEADING from the drop down list as in figure 3 above.
The next field we are going to add to the QUERY DESIGN grid is postcode. However, as explained above, we are not interested in the full postcode, just the general postcode area. You may remember that we discussed how UK postcodes are comprised of two parts and it is the first part which tells us the general area. As such, we need to use a string function to extract the first part of the code from the full field value. We are going to achieve this end using the InStr function to count the number of characters in the first part of the code, and then using the Left function to extract these characters from the full field value. We use the InStr function to search for the space character which separates the two parts of the code. The value the function returns is the numerical character position of the space. The syntax for this is as follows:

InStr([postcode], “ “) -1

By subtracting 1 from the return value, we get the character count of the first part of the code. For example, if the postcode was “SK14 5RP”, our function returns 5 for the space position, and then 4 for the character count after we subtract 1.

We then use the Left function to extract the number of characters in the character count from the left side of the string. The syntax for this is as follows:

Left([postcode], InStr([postcode], “ “)-1)

… and this returns the first part of the postcode as a new string value ie “SK14” for the example used above.

As such, we can now add this function to the field row in the second column of the QUERY DESIGN grid. The string data this function will return will form the column headings in our crosstab query result, so select COLUMN HEADING in the crosstab row drop down list as in the screenshot below:

Figure 4: NB Once you enter the expression, Access gives the column it’s own alias of Exp1. You can overtype this with postcode_area to give it a more meaningful name. Doing so does not affect the query result in any way.

10. Now that we have sorted our row and column headings, we just need to take care of the actual values where the rows and columns intersect. As you may remember, these are going to be the average price paid for a property of a specific type (ie the property_type row) in a specific area (ie the postcode_area column). Do this by selecting the price_paid field from the drop down list in the third column of the FIELD row of the QUERY DESIGN grid, or by dragging price_paid down from the tblSalfordHouseSales table box in the top half of the window.

11. Next, we need to change the value in the TOTAL row of the price_paid column in the QUERY DESIGN grid. Change the value from GROUP BY to AVG. This will give us the average price_paid aggregate for properties of a specific type in a specific area.

12. This is where we set the CROSSTAB row of the price_paid column in the QUERY DESIGN grid. Select VALUE from the dropdown list as in figure 4 above. This simply tells Access that the price_ paid field is constituting the values in the crosstab query.

13. Finally, we just need to set field properties for price_paid so that we get a whole number without any decimal places. Do this by clicking anywhere in the price_paid QUERY DESIGN GRID column, and then clicking the PROPERTY SHEET icon in the SHOW/HIDE group of the QUERY DESIGN ribbon, and then set the FORMAT property to CURRENCY and the DECIMAL PLACES property to 0. This will make the query results much easier to read.

All we need to do now is click RUN in the RESULTS group of the QUERY DESIGN ribbon to see the results of our crosstab query!

Figure 5: Average price paid for property types in the different Salford poscode areas (data taken from sales during 2023).

As we can see, the different property type codes from data in our original table form the row headings in the crosstab query, and the postcode areas form the column headings. The actual table values, as we have learnt above, are an aggregated price_paid average of all properties of a specific type in a specific area. So, for example, the average price paid of all detached properties in the M27 postcode area is £335,592. If we compare this to the average price paid of the same property type in the M28 postcode area we see the value of £440,601 is more expensive, thereby suggesting that the latter area is more desirable. As such, the crosstab query has given us a meaningful summary of the 2,942 rows of data from our original table. In so doing it has helped us understand what the original data was telling us.

Using Access for Data Analysis

2025-05-26T03:29:00.000-07:00

Normally, when we think of MS Access, we do so in the context of database development; that is to say, as software to create database applications for long term use to keep track of data such as customer orders, income and expenditure, and stock control. Such database applications are used by one or more people over a period of time to store, process and retrieve information, and often support the day to day operation of a business.

In this tutorial, however, we are going to look at how MS Access can be used in the context of data analysis. This use is very much different from the database development model described above. Rather than setting up a database for long term use in the day to running of a business operation for example, we are going to use Access in a short term context, as a tool to import data from an external source, and then analyse that data through Access Queries to make it meaningful from a particular perspective. This summarised data can be presented to, say, a management team to make informed decisions based on what the information reveals.

To do this we are going to be working with 2023 house sale data for the Salford area of Greater Manchester. The process will involve extracting a dataset from the UK based HM Land Registry website, importing the dataset into Access, creating query's to categorise and aggregate the data, and then create a report containing a clustered column chart to present the data visually.

Downloading the Dataset

The first step in the process is to download the dataset from the HM Land Registry website. This will involve filling in an online webform to specify the data we require. In our case this is all data between 1st January and 31st December 2023 in the district of SALFORD.

1. Click this link the HM Land Registry link to open the web form in your browser.

2. Enter "SALFORD" in the DISTRICT textbox, 01/01/2023 and 31/12/2023 for the DATE range (note the UK date format) , and select "ALL" from the HOW MANY RESULTS option group at the bottom.

3. Click the SHOW RESULTS button.

This opens a new web page showing 2947 property sale transactions for 2915 properties. We now need to download the results into a CSV file.

4. Click the DOWLOAD DATA button at the top of the form. This opens a web page giving us options for downloading:

5. Click GET ALL RESULTS AS CSV WITH HEADERS button to start the download.

A dataset called ppd_data.csv will be saved to your downloads folder. You can now open the file to inspect the data. It will open in MS Excel by default and if you scroll to the bottom of the worksheet there should be 2947 rows.

Importing the Dataset into Access

We can now go ahead and import the data into our Access Database.

1. Click NEW DATA SOURCE on the Access EXTERNAL DATA ribbon. Choose FROM FILE when the context menu opens, and then click TEXT FILE.

This will start the GET EXTERNAL DATA - TEXT FILE wizard.

2. Click the BROWSE button on the first page of the wizard and select the text file which has been saved in your downloads folder. Leave the top radio button selected - it reads "import the source data into a new table in the current database". Click OK at the bottom of the wizard window.

3. The next page (see screenshot below) shows a sample of the data we are importing. At the top it reads "Your data seems to be in a delimited format. If it isn't, choose the format that more correctly describes your data". If you look at the sample of data displayed, you see each field is separated by a comma. We can therefore leave the "Delimited" radio button checked, and click NEXT.

4. The next page of the wizard (see screenshot below) asks us to choose the delimiter that separates our fields. As we noted above, our data is separated by commas, so we should now select the "Comma" radio button. We also need to click the "First Row Contains Field Names" tickbox. You may get pop up message explaining some of the field names have been modified to make them compatible with Access. This is fine. Just click OK. We can now click NEXT to move on to the next page of the wizard.

5. This page (see screenshot below) gives us the option to specify information about each of the fields we are importing. This includes specifying the data type for the field. We are going to change the data type for the price_paid field from SHORT TEXT to CURRENCY. To do this, highlight the price_paid column and select CURRENCY from the DATA TYPE combo box. We will also change the data type on the deed_date field from SHORT TEXT to DATE WITH TIME. Another thing we are going to change is the index type (ie the Indexed field option) on the unique_id field from "Yes (Duplicates OK)" to "Yes (No Duplicates). This will ensure the data set does not contain any duplicate records.

At this stage we can clean the data set further by excluding any fields that we do not need. To do this, highlight the field for exclusion and tick the "Do not import field" tick-box in the Field Options section. We are going exclude the estate_type field, and all the fields from transaction_category and after.

We can then click NEXT to continue.

6. The next page of the wizard lets us choose a primary key. There are three options: these are "Let Access add primary key", "Choose my own primary key" and "No primary key". We are going to select our own primary key, so tick the middle option and select the "unique_id" field from the combo box list. Then click NEXT.

7. In the last page of the wizard we are asked to choose a name for the table into which our data is to be imported. Entry the table name "tblSalfordHouseSales" and click FINISH.

We can now take an initial look at the table of data we have imported. Go to the Access navigation pane and open tblSalfordHouseSales (see screenshot below).

As we can see, the data set contains 2946 records. Each record provides information about the sale of a given property and consists of 13 fields. Some of most useful fields we shall be using for data analysis in this tutorial are price_paid, deed_date and postcode. We also have the option of analysing house sales by property_type and whether the property is a new_build.

Checking the Data

Now we have imported the data set into Access, we can start checking it's accuracy. However, before we start, lets make a copy of the imported table so we can undo any changes that we make without having to repeat the download and import process. Do this by right clicking tblSalfordHouseSales in the navigation pane and select COPY from the context menu. Then simply right click a blank space in the navigation pane and select PASTE. A window opens where we can enter a new name to call the table we are pasting, and select which paste option we would like. We are going to call the new table "tblSalfordHouseSalesOriginalData" and select the Structure and Data paste option. This will paste a new table based on the same structure, and containing the same data, as the one we copied. Then click OK.

The first thing we are going to check are the postcodes (zip codes) in our dataset. Since the data we have downloaded is for the Salford area of Greater Manchester, each record in the dataset should start with one of the following values in the postcode field: - M3, M5, M6, M7, M27, M28, M30, M38, M44, and M50 (after which there is a space followed by the second part of the postcode) . As such, we are going to create a query which groups the data into groups comprised of the first three values of the postcode, and provide some basic aggregate information for each grouping. From this information, we should be able to pick out any obvious anomalies contained in the data.

Create Query to Group & Aggregate Postcode Data

1. Create a new query by clicking the QUERY DESIGN icon on the CREATE ribbon.

2. Click the ADD TABLES icon (on the QUERY DESIGN ribbon) to display the ADD TABLES sidebar if it is not visible already.

3. Drag the tblSalfordHouseSales table from the tables list in the sidebar over to the QUERY DESIGN window.

4. Click the TOTALS icon in the SHOW/HIDE group of the QUERY DESIGN ribbon. This adds a TOTAL row to the QUERY DESIGN grid. This enables us to specify whether the field is to group the data contained therein, or perform an aggregation on it.

5. The first column in the query grid (see screenshot above) is going give us a list of postcode groups. All the post code groups in the Salford area (see above) are 2 or 3 characters long. So to extract the postcode group value from the value stored in the postcode field , we are going to create a calculated field in the first row of the first column of the query grid. The calculation is based on the postcode field in tblSalfordHouseSales and utilises the Left string function to take the first 3 characters of the field value. This is enclosed within a RTrim function to remove the right blank space if the postcode group is two characters rather than three. It is written as follows:

RTrim(Left([postcode], 3))

We will use the alias PCode as the field name. This is written ...

PCode:

... directly before the RTrim function syntax. So altogether the first row of the first column in the query grid (see screenshot above) will read ...

PCode: RTrim(Left([postcode], 3))

We then set this field to GROUP BY in the TOTALS row.

6. In the second column of the query grid, we are going to count the number of records within each postcode group. So, in the ADD TABLES side bar, drag the unique_id field from tblSalfordHouseSales down to the first row of the second column in the query grid, and select COUNT from the drop down list in the TOTALs row.

7. In the third column of the query grid we are going to SUM the total of price_paid to give us the total price paid within each postcode group. So drag price_paid from tblSalfordHouseSales down to the first row of the third column in the grid, and select SUM in the TOTALs row.

8. Click the RUN or DATASHEET icon in the RESULTS group of the QUERY DESIGN ribbon to see the query results.

Looking at the query results above, we can see that most of the properties have a post code in the Salford area - these are M3, M5, M6, M7, M27, M28, M30, M38, M44, and M50. However there are three properties which do not have any postcode, and one property which has a postcode starting with WA1. Taken together these come to a total value of £10,249,999. In order to ensure our data set is accurate, we need to investigate why these properties do not have a Salford post code and remove them if necessary. To do this we shall create another query to get the full information for the dubious records.

1. Repeat steps 1 to 3 above to create a new query based on the tblSalfordHouseSales table.

2. Drag the asterix * from the tblSalfordHouseSales table down to the first row of the first column on the query design grid. The asterix is a quick way to include all the fields in the table without having to drag each one individually. However, if we need to enter a criteria for a particular field (which we do) we can still drag that field down separately ..

3. As such, drag the postcode field down to the first row of the second column in the grid.

4 Click the CRITERIA row in the postcode column and enter the following expression:

Is Null or Like "WA1*"

The criteria Is Null Or Like "wa1*" will pull out any record containing a
postcode with a null value or a value starting with "wa1".

5. Click the RUN or DATASHEET view icon in the RESULTS group of the QUERY DESIGN ribon the see the query results

The first record returned by our query is for a property with the postcode WA14 5DY which (if you scroll across) is actually located in the town of Altrincham. If you are familiar with the geography of Greater Manchester you will know that this town is in the borough of Trafford rather than Salford. As such, this record does not belong in our dataset and should be removed. When we examine the data for the remaining three records, it is apparent that they pertain to land in a commerce park, a parking space, and a brown field site. As such they fall outside the scope of our interest in house sales, and can also be removed from our dataset. Its worth reflecting that had we not done this check, our dataset would have inaccurate to the value of £10,249,999.

A good way to remove these records from the dataset is to go back into QUERY DESIGN view and change the query type from SELECT to DELETE.

After changing the query type from SELECT to DELETE, you will see an addition row appear in the query grid entitled "Delete". You will also see that Access has filled in the values for this row. In the first column it has added "From" which means the records are being deleted from the tblSalfordHouseSales table, and in the second column it has added "Where" which means all records in the table matching our criterion Is Null Or Like "WA1*" will be deleted.

Click the RUN icon in the RESULTS group of the QUERY DESIGN ribbon to run the delete query.

Access will display a warning dialog saying that the 4 rows containing dubious records are going to be deleted from our table.

Click YES to continue the delete operation.

If we now go back and reopen tblSalfordHouseSales the record count will now show 2942 records, 4 less than the 2946 records we had previously.

There are many other checks that we can do to ensure our dataset is clean, but the steps mentioned above give us an idea of how we can use Access's queries and other features in this process. Let us now move on and see how we can use Access to analyse our dataset.

Analysing Data with Access Queries

To illustrate how Access can be used to make sense of our housing sale dataset, we are going to create a number of price bands and count the number of sales in each band. This will give us a basic understanding of the housing market in Salford during 2023.

This task will involve the creation of a subquery for each individual price band, and a parent query to pull together the sale counts within each band, showing them all in one row of aggregate data. Lets begin with the subqueries.

We are going to create a total of seven price bands as follows:

PRICE BAND SUBQUERY

< £100,000 qrySubBelow100K

>= £100,000 and < £200,000 qrySub100Kto200K

>= £200,000 and < £300,000 qrySub200Kto300K

>= £300,000 and < £400,000 qrySub300Kto400K

>= £400,000 and < £500,000 qrySub400Kto500K

>= £500,000 and < £1,000,000 qrySub500Kto1000K

>= £1,000,000 qrySubAbove1000K

Each of the above queries is based on the unique_id and price_paid fields of tblSalfordHouseSales. Once again, the query is going to use the TOTALS row in the QUERY DESIGN grid because we need to aggregate the data returned. We are going to set up our query to count the number of rows returned in the dataset when we add a criterion to the price_paid field. To do this we shall set the value in the TOTALS row of the unique_id column to COUNT, and the value of the TOTALS row of the price_paid column to WHERE. All we need to do then is enter the criterion in the CRITERIA row of the price_paid column. For example, when we create qrySubBelow100K to count the number of sales less than £100,000 the criterion we enter is <100000 as in the screenshot below:

You may have noticed how the tickbox in the SHOW row
of the QUERY DESIGN grid is unticked when we select WHERE
in the TOTAL row for price_paid. This is because the only purpose
of the price_paid column is to filter the rows to be included in the
count of unique_id's.

When we run the query a single row and column is returned with the count of unique_id's matching the query criterion entered for price_paid. For records with a price paid value less than £100,000 this should value should be 140.

Here are the instructions for creating the subqueries for the seven price bands. We will need to repeat this process seven times for each of the subquerie:

1. Create a new query by clicking the QUERY DESIGN icon on the CREATE ribbon.

2. Click the ADD TABLES icon (on the QUERY DESIGN ribbon) to display the ADD TABLES sidebar if it is not visible already.

3. Drag the tblSalfordHouseSales table from the tables list in the sidebar over to the QUERY DESIGN window.

4. Click the TOTALS icon in the SHOW/HIDE group of the QUERY DESIGN ribbon

5.In the first column of the QUERY DESIGN grid, select the unique_id field from the drop down list in the top row.

6. We now need to go down to the TOTAL row in the first column and select COUNT from the drop down list.

7. In the second column of the QUERY DESIGN grid, select the price_paid field from the drop down list.

8. Going down to the TOTAL row of the second column, select WHERE from the drop down list.

9. We now need to enter the criterion in the CRITERIA row of the second column. The criterion we enter depends which of the seven subqueries we are working on. The criterion for qrySubBelow100K, for example, is <100000. The criteria for all seven queries are listed in the table below.

10. Click the SAVE icon at the top left of the screen and the name of subquery that we were creating.

WHERE CRITERIA SUBQUERY

< 100000 qrySubBelow100K

>= 100000 AND < 200000 qrySub100Kto200K

>= 200000 AND < 300000 qrySub200Kto300K

>= 300000 AND < 400000 qrySub300Kto400K

>= 400000 AND < 500000 qrySub400Kto500K

>= 500000 AND < 100000 qrySub500Kto1000K

>= 1000000 qrySubAbove1000K

In addition to the seven subqueries above, we shall also create another subquery (qrySubCountAll) to give us a grand total for 2023 Salford house sales across all price bands. This subquery is the same as those we created above, except we do NOT need the price_paid column or any WHERE clause therein. We just need the COUNT in the unique_id column as in all the previous subqueries:

When this query is run we get the same output format as the previous seven subqueries (see screenshot below). The result is a count of all house sales in tblSalfordHouseSales. This should be 2942.

Now we have created all our subqueries we can begin work on the parent query which pulls all the subquery results together in one row of aggregate data.

The Parent Query

We are going to call our parent query qryHousePriceGroups. Each column in the query will display the aggregate total count of house sales for each of the price bands. The last column will display the grand total. To do this we will need to add all 8 subqueries we created to the QUERY DESIGN grid window and use the appropriate subquery as the data source for each of the parent query columns.

As we can see in the screenshot above, we have given each column name an alias such as "Below 100K" and "100K to 200K". This will make the query output easier for users to read and understand.

Here are the step by step instructions for creating the parent query.

1. Create a new query by clicking the QUERY DESIGN icon on the CREATE ribbon.

2. Click the ADD TABLES icon (on the QUERY DESIGN ribbon) to display the ADD TABLES sidebar if it is not visible already. Then select the QUERIES tab at the top of the sidebar. You should see all the subqueries that we have created previously.

3. Drag all 8 subqueries over to the QUERY DESIGN window. We will now start to dragging the CountOfUnique_id fields from each subquery onto the QUERY DESIGN grid below.

4. The first CountOfUnique_id field we are going to drag is from the qrySubBelow100K subquery. Drag this down to the cell in the top row of the first column in the grid.

5. We shall now enter the alias name for the column. We do this by typing the name Below 100K in the same cell just before CountOfUnique_id separated from it by a colon as follows:

Below 100K: CountOfUnique_id

6. In the top row of the next column, we are going to drag CountOfUnique_id from the qrySub100Kto200K subquery. We shall give this the alias 100K to 200K.

7. Repeat step 6 for each of the subqueries using the following alias's:

200K to 300K:

300K to 400K:

400K to 500K:

500K to 1000K:

Above 1000K:

All:

When we run the query we should get this result:

As we can see, out of a total of 2,942 house sales, the vast majority fall within the 100K to 200K, and 200k to 300K price groups. The 100K to 200K group contains 1,258 sales, and the 200K to 300K group contains 992 sales, giving a total of 2,250 sales out of 2,942, or 76% of the total 2023 Salford housing sale count. The figures also reflect the fact that Salford has some poorer districts as well as those which are very affluent. There were 140 house sales (5%) with property values of less than £140,000, but 100 sales (5%) with values between £500,000 and £1000,000, and 26 sales (1%) above £1000,000.

Lets end by showing this data visually in a clustered column chart embedded in an Access report. Here are the step by step instructions:

1. Click the REPORT DESIGN icon in the REPORTS group of the CREATE ribbon.

2. Resize the REPORT DESIGN grid so we get a larger section of screen in which to create the chart.

3. Click the INSERT MODERN CHART drop down menu in the CONTROLS group of the REPORT DESIGN ribbon. We are going to select the CLUSTERED COLUMN CHART from the COLUMN submenu.

4. The cursor will now change to a small chart symbol. Position the cursor in the top left hand corner of the DETAIL section of the design grid. A small chart will embed itself on the grid. At this stage we just have sample data displayed in the chart.

5. Resize the chart by clicking it's border on the bottom left corner and dragging the corner down and across so we get a bigger chart.

New chart showing sample data.

6. We will now connect the chart to our parent query. We can do this from the CHART SETTINGS sidebar. Click anywhere inside the chart so it is selected. Then click the CHART SETTINGS icon in the TOOLS group of the REPORT DESIGN ribbon (if the sidebar is not displayed already).

7. Click the QUERIES radio button under DATA SOURCE, then select the parent query that we created earlier from the drop down list.

8. Go down to the VALUES (Y axis) section below. You will see a list of checkboxes with the price bands we created in our query. Tick each of the boxes except ALL. That is from Below 100K down to Above 1000K. You will notice there is a COUNT OF aggregate function contained within brackets after each of the check box labels. We need to change the aggregate function to NONE by clicking the drop down list which appears when you hover the mouse over the text. You should now see the data from our parent query appear on the chart. We now need to customise the appearance of the chart to make it more user friendly.

Chart showing parent query data ready
to be customised.

9. The first customisation we shall make is the addition of a data label above each chart column. This shows the value for the count of sales for each of the price bands. To do this select the FORMAT tab at the top of the CHART SETTINGS side bar. Select the first price band Below 100K from the drop down list under the DATA SERIES title. Then tick the Display Data Label checkbox.

You should now see the value 140 appear above the Below 100K column on the chart.

10. Select the next price band from the Data Series drop down list and tick the Display Data Label checkbox. Do this for the rest of the price band columns on the chart.

11. For the remaining customisations, we are going to use the chart property sheet. Click anywhere inside the chart so it is selected, then click the PROPERTY SHEET icon from the TOOLS group of the REPORT DESIGN ribbon.

12. The next customisation we are going to make is the addition of a chart title. Select the FORMAT tab on the property sheet and locate the HAS TITLE and CHART TITLE properties further down the sheet. Ensure the HAS TITLE property is set to YES, and enter "2023 Salford House Sales" for the CHART TITLE property value.

13. We shall next move the legend from the top of the chart to the bottom. To do this locate Legend Position also on the FORMAT tab of the property sheet. Change the property value to "bottom".

14. The final customisations we are going to make involve adding a title to the vertical axis and remove the text saying "140" below the horizonal axis. To do this locate the primary values axis title on the property sheet and change the value to "Count of Sales"; and then locate the category axis font color property, click the ellipse button (...) in the value cell, and select white from the colour chart. This will hide the unwanted text from view.

15. We can now open the report by clicking the REPORT VIEW button on the VIEWS group of the REPORT DESIGN ribbon. The result should look like the screenshot below:

We can now see the data from our parent query in visual form, thereby gaining a clearer view of the concentration of house sales in the 100K to 200K, and 200K to 300K price bands.

There are, of course, many other dimensions we can use to analyse our data. For example, we can create queries to group house sales according to property type, and find out the average value in each group. We could also have downloaded a larger dataset which includes sales over a number of years, and create a chart showing how prices have changes over particular timespans. I intend to cover areas such as these in future posts.

Static Variables Part 2: the Advantage of Static Variables

2024-03-24T14:07:00.000-07:00

In part one of this tutorial - Using Static Variables - we examined the difference in behaviour between an ordinary local variable and a static variable. We learnt that whilst both types can only be referenced within the same sub procedure or function in which they were declared, the value of a static variable is retained after code in the sub procedure or function has completed execution. The terms we used to understand this behaviour were scope and lifetime respectively.

In part two, this current post, we are going to consider how we can use the unique scope and lifetime of a static variable to our advantage. In order to illustrate this we shall examine the workings of an order counter custom control comprised of a textbox and two control buttons.

An Order Counter Control

This sort of control may be familiar to you if you order your groceries online. It usually appears under an image of a product and allows the customer to set the order amount by clicking the plus and minus buttons without having to type in the actual number in the textbox. It is of interest to us because it lends itself to the use of a static variable to keep track of the order amount.

The advantage of Static Variables

So what is it about static variables that make them advantageous to use in contexts such as that of our order counter control? To understand this lets take look at how the VBA code used to power this it is organised.

VBA Code Powering the Order Counter

As we can see, the coding for the form containing the order counter is comprised of five sub-procedures and a general declarations section where three constants, INITIALIZE, INCREMENT, and DECREMENT are declared. Three of these sub-procedures, Form_Load(), cmdMinus_Click(), and cmdPlus_Click(), contain event handling code which execute when the form opens, or the minus and plus buttons are clicked, respectively. Each consist of one statement calling changeOrderAmount(...), the sub-procedure used to calculate the order amount displayed in the txtOrderValue textbox. This is the location where our static variable, intOrderAmount, is declared.

It is in the changeOrderAmount(...) sub-procedure where the value of intOrderAmount is initialized, incremented or decremented within a SELECT...CASE statement, which is, in turn, based on the value of the intApplyChange argument it received when called. For example, if the INCREMENT constant was received, the value of intOrderAmount is incremented by 1. Likewise, DECREMENT decreases the value by 1, and INITIALIZE sets the value to 0. Note the fact that the actual order amount is not set directly when incremented or decremented, but is calculated from its previous value. For this reason intOrderAmount has to persist across multiple calls to the sub procedure, and is why it is declared as Static.

In order to achieve the above without using a static variable, we could instead declare intOrderAmount in the general declarations section of the form's code module using a Dim statement or the Private keyword. This gives the variable a module level scope and lifetime: it can now be referenced from any sub-procedure or function within the same module, and any value it contains will be retained while the form connected to the module remains open. As such, the module level version of intOrderAmount has the same lifetime as our original static version declared within the changeOrderAmount(...) sub-procedure. However, since the variable only needs to be referenced from within the sub-procedure, to give it module level scope in the above mentioned way will leave it unnecessarily exposed to accidental editing as well as making our code less easy to read.

These issues arising from module level scope become more pronounced as more code is added to the module. Other functions and sub procedures are likely to use their own variables, some of which may also have module level scope. These are generally less easy to keep track of because they can be modified from any location in the module, often under a variety of different conditions. In this sort of context it becomes increasingly difficult to read and understand our code, and there is a greater chance of accidentally referencing a different variable to the one intended, especially if it has a similar name or performs a similar role. As such, using a static variable with a scope local to the sub-procedure or function in which it is declared, whilst also retaining it value over multiple calls, is the much better option.

Conclusion

The advantage of using static variables lies in the fact that they are, on the one hand, declared within a sub-procedure or function, and thereby have a local scope; and on the other hand, retain their value once code in the parent sub-procedure or function has completed execution. As a result, the variable is protected against accidental editing from code elsewhere in the project, whilst retaining its value in order to keep a running total over multiple calls to its parent sub procedure or function. Furthermore, since static variables are declared within sub-procedures or functions, as opposed to an external location in the project, our code is more manageable, and hence easier to read and understand. Generally speaking, our code is more streamlined and robust.

Using VBA Static Variables

2024-03-09T13:05:00.000-08:00

In this post we are going to look at what static variables are, how they work, and how they are different from ordinary local variables. In a future post we will build upon this and consider the advantage of using static variables over locals, and look at an example of how they may be used in practice.

The Difference between Local and Static Variables

So what are static variables exactly? To understand this, let's begin by examining how ordinary local variables work. All variable types have there own particular scope and lifetime. The first of these determines the locations within the program from which the variable can be accessed, and the latter determines how long the variable is active.

Local Variables

Local variables have a scope which is limited to the sub procedure or function from which it is declared. For example:

Private Sub testThis()
Dim intExample As Integer
intExample = 10
MsgBox ("The value of our test variable is " & intExample)
End Sub

Here we have declared an integer variable called intExample within a sub procedure called testThis(). Since the variable was declared within the sub procedure, it is local in scope - the value of 10 it subsequently contains can only be accessed within the testThis() sub after the declaration statement. So if the MsgBox Statement referencing the intExample variable is used outside the sub, the variable is not recognised.

Furthermore, once code in the sub procedure completes execution, the intExample variable ceases to exist, and it's value of 10 is lost. The next time the testThis() sub executes, intExample's value will need to be set anew. As such ordinary local variables are limited to the length of time the sub procedure or function is executing. This is an example what is meant by a variables lifetime.

Static Variables

Now let's take a look at how the scope and lifetime of a static variable works in contrast to an ordinary local variable. Consider the following code snippet:

Private Sub testThis()
Static intExampleStatic As Integer
Dim intExampleLocal As Integer
intExampleStatic = intExampleStatic + 1
intExampleLocal = intExampleLocal + 1
response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
", whereas the value of intExampleLocal is " & intExampleLocal & ".", vbInformation, "Static & Local Variable Output")
End Sub

Here we have created another sub procedure called testThis(), and declared a static variable and an ordinary local variable, intExampleStatic and intExampleLocal respectively. Notice how the declaration statement for the static variable uses the Static keyword rather than Dim ie:

Static intExampleStatic As Integer

in contrast to ...

Dim intExampleLocal As Integer

Since both variables are declared as Integer, they are automatically initialized with a value of 0. Then in the following lines of code we increment the value of each of the variables by 1:

intExampleStatic = intExampleStatic + 1
intExampleLocal = intExampleLocal + 1

... and display both values using a message box statement:

response = MsgBox("The value of intExampleStatic is " & intExampleStatic & _
", whereas the value of intExampleLocal is " & intExampleLocal & ".", vbInformation, "Static & Local Variable Output")

Now the scope of both kinds of variable, local and static, work in exactly the same way. That is to say, static variables, along with ordinary local variables, can only be accessed from within the same sub procedure or function from which they were declared. Where they differ is in their respective lifetimes. Whereas a local variable ceases to exist after the code within the same sub or function completes execution, the value contained in a static variable will be retained - at least until the codes' underlying form or report is closed, or in the case of a global module, until the database itself is closed. To demonstrate this in action, lets see what happens when the testThis() sub procedure starts being called.

The first time testThis() is called the message box output shows that the intExampleStatic and intExampleLocal variables have the same value ie 1:

It is not until the second and subsequent calls that we see the difference in lifetime between static and local variables taking effect. This is because the value stored in the local variable is lost each time the sub procedure completes, and then re-initialized the next time it is called. The static variable, on the other hand, retains its value from the previous call. Hence, the output after the second call shows the static variable to have a value of 2, but the local variable again has a value of 1:

This pattern continues each time the sub procedure is called: the value of the static variable is retained and any increments are added to this retained value, whereas the local variable is created anew and any increments are applied to its initialization value of 0. Hence the third time testThis() is called, the value of the static variable is increased to 3, and the value of the local variable remains at 1:

... and so on until the sub procedures's underlying form or report is closed. It is not until the next time the form or report is reopened that the static variable's value is re-initialized to 0.

So this has shown us how static variables work and how they differ from ordinary local variables. In the next blog post we are going to consider the advantages of using static variables over local variables, and will demonstrate this by creating a a simple order counter control where a user clicks two buttons to increment or decrement the quantity of an item to be ordered without having to type the actual number.

Display a Random Customer Record using VBA and Form Filter

2024-02-21T03:00:00.000-08:00

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!

Programming Access Option Groups with VBA

2021-06-23T02:58:00.892-07:00

In my previous post on How to Use Option Buttons, we learnt how to set up an Option Button Group bound to a field in the form's underlying database table. We learnt that an Option Button Group is comprised of two separate types of control - the Option Group control itself, and the individual Option Buttons within it. Once set up, these function together as a single unit.

Today we are going to learn how to set up a similar Option Button Group, but this time, we are going to use VBA to determine which button has been selected, and change the behaviour of the form based upon the selection.

Screen Colour Option Group

To do this we are going to set up an Option Group containing three Option Buttons (see screenshot above). When the user clicks one of the buttons in the Option Group, VBA subprocedures are called, and the background colour of the form changes in accordance with the users' selection. Here is a video of how it works in practice.

The process for setting up the form with the Option Group is very similar to that which we went through in my last blog post on How to Use Option Buttons. The main difference is that previously we bound the data selected in the Option Group to a field in the form's underlying database table. This time, however, we are going to leave the Option Group unbound, and reference the data selected in the Option Group with VBA code. To do this we will need to make use of the Option Groups On Click event to trigger the subprocedure containing that code when the user clicks any button in the Option Group.

Setting up the Form and Option Group

Here is the procedure for setting up the Option Group:

1) Create a new form by clicking the FORM DESIGN icon on the CREATE ribbon.
2) Go to the CONTROLS section of the DESIGN ribbon and drag an OPTION GROUP control down onto the design grid.

3) Open the PROPERTY SHEET while the OPTION GROUP CONTROL is highlighted and enter the name "opGrp" in the NAME property (under the OTHER tab)

4) Next go back to the CONTROLS section and drag an OPTION BUTTON control onto the OPTION GROUP which we placed on the grid in the previous step.

5) Enter the name "optRed" in OPTION BUTTON's NAME property whilst the control is highlighted.

6) Repeat steps 4 and 5 twice, creating two more OPTION BUTTONS called "optGreen" and "optBlue" respectively.

Design View of our Option Group containing the three Option Buttons - optRed, optGreen and optBlue

The next step in the process is to check the OPTION VALUE property is set up correctly for each of the buttons. To do this you will need to select each Option Button in turn, and go to the property sheet DATA tab. The values are set automatically when you drag the Option Button controls into the Option Group, but just to be sure, check the value for optRed is "1", optGreen is "2" and optBlue is "3".

The Option Value property for optRed.

As you may recall from my last blog post, the Option Button selected by the user at runtime determines the Value of the Option Button Group as a whole. As such, if the user selects optGreen, the value of the group is "2" etc. It is this value that our VBA code needs to reference when the subprocedure runs.

Programming the Option Group

Let us now turn our attention to the VBA code which executes when the user clicks one of the Option Buttons at runtime. In order for our code to detect when any button within the Option Button Group is selected, we need to make use of the Option Group's ON CLICK event.

This is done as follows:

1) Select the Option Group Control (which we have called "optGrp") on the design grid so it is highlighted orange.

2) Go to the PROPERTY SHEET and select the EVENTS tab.

3) Click the ellipse button ("...") at the end of the ON CLICK property row to open the VBA editor.

Selecting the ON CLICK event for the "optGrp" OPTION GROUP. Clicking the
ellipse button ("...") opens the VBA editor.

After clicking the ellipse button on the ON CLICK row of the PROPERTY SHEET, the VBA editor will have opened and automatically created a sub procedure called "optGrp_Click". This is the ON CLICK event-handler. Any code we enter here will be executed when the user clicks any button in the option group at runtime.

Our first programming task is to determine which Option Button the user clicked. This is done by referencing the Option Groups VALUE property as follows:

formname!optiongroupname.Value

In our case the code will be:

Me!opGrp.Value

"Me" is a quick and simple way to reference the form name within which the code module is contained; "opGrp" is the name of our Option Group; and Value is a reference to the value property of the Option Group as a whole. Note the form name and control name is separated by the "!" character, and the control name and property is separated by a full stop ".".

Referencing the OPTION GROUPS VALUE property in this way gives us the OPTION VALUE of the button selected by the user. As you may recall the OPTION VALUE for the red button was "1", green was "2" and blue was "3". So if the user clicked the green button, the above statement will return "2" when it executes at runtime.

In order to use the OPTION GROUPS VALUE property to change the background colour of the form, we are going to do two things. Firstly we are going to call a separate VBA sub procedure from the OPTION GROUPS ON CLICK event-handler, passing the VALUE property as the parameter; secondly, we are going to create this subprocedure which will process the VALUE property and then change the background colour of the form.

The event-handler code is as follows:

Private Sub opGrp_Click()

Call setBackgroundColour(Me!opGrp.Value)

End Sub

As we learnt above, the first and last line of this event handler is created automatically when we clicked the ellipse button ("...") in the ON CLICK row of the Option Groups property sheet. All we have done is add the middle line to call a sub procedure called setBackgroundColour (yet to be created), and pass the referenced value of the Option Group as the parameter.

Let us take a look at the code for our setBackgroundColour sub procedure:

Private Sub setBackgroundColour(intOpValue)

Dim lngRed As Long, lngGreen As Long, lngBlue As Long

lngRed = RGB(255, 230, 230)

lngGreen = RGB(242, 255, 230)

lngBlue = RGB(230, 240, 255)

Select Case intOpValue

Case 1

Me.Detail.BackColor = lngRed

Case 2

Me.Detail.BackColor = lngGreen

Case 3

Me.Detail.BackColor = lngBlue

End Select

End Sub

Here is a breakdown of how the code works:

1) The first line of the sub procedure receives the option value argument (intOpValue) which was passed to it from the calling statement we wrote in the ON CLICK event handler.

2) The next section sets up three variables of the LONG data type and assigns each one a colour value which is used to set the forms BackColor property to red, green or blue as required.

3) Next we have set up a SELECT CASE statement to determine the value of the intOpValue variable which, as we have learnt above, contains the OPTION VALUE of the OPTION BUTTON clicked by the user at runtime.

4) Then each respective CASE statement tests whether it's value matches intOpValue. When it comes to one that does, the BackColor property of form is set accordingly. So if the value of intOpValue is "2", the second Case statement invokes ... Me.Detail.BackColor = lngGreen ... to set the form's background colour to green.

There is just one more thing we need to do before we can see our form and code in action. This involves calling the setBackgroundColour sub procedure as soon as the form has opened. This is so the form's background colour is set in accordance with the default option button selection in the first instance.

To do this we are going to make use of the forms ON CURRENT event which fires just after the form opens. The procedure is similar to how we set up the ON CLICK event-handler for the Option Group. This time we need to select the form in the design grid by clicking the square at the top right and then open the property sheet for the FORM.

Selecting the FORM in design view.

Then we need to open the EVENT tab (of the FORMS property sheet) and click the ellipse ("...") button at the end of the ON CURRENT row. This creates the event-handler in the VBA editor. Once that has been created we just need to enter the same code as previously to call the setBackgroundColour sub procedure as follows:

Private Sub Form_Current()

Call setBackgroundColour(Me!opGrp.Value)

End Sub

That's it! Our form with the programmed option group is now ready to open and use.

How to Use Option Buttons (aka Radio Buttons or Option Groups)

2021-06-05T06:23:00.006-07:00

What are Option Buttons?

So what are Option Buttons, and what are they used for? Option Buttons are a user-friendly form control that enables users to select a single value from a group of given options. This is done by clicking one of a number of boxes, with each box representing one of the available options. When the user clicks a box to select the value, any previously selected boxes are unselected as a result. In this way, only one value can be selected at any one time.

Example of an Option Button Group.

For example, in the screenshot above, there are three options for the user to select one out of a possible three teams - team 1, team 2 and team 3. The advantage of using an option button group over a combo box with the same available list of values is that it is quick and simple for the user to enter data. It is also preferable to a textbox in so far as it limits the value entered to one in the group of options. On the downside, an option button group tends to take up more space of the form, particularly if there are many options to choose from. If this is the case, a combo box with data entry restricted to items in the list, maybe a better choice.

In the following exercise, we are going to re-create the Option Button Group from the above screenshot. To begin with, let take a look at the underlying database table.

The underlying database table containing the "team" field
for the Option Group's Control Source.

As we can see, the underlying table contains four fields - ID, firstName, surname and team. It is the latter field, team, which is going to be the Control Source for our Option Button Group. Before we start the exercise it is worth mentioning that the Option Button Group is actually comprised of two separate types of control: first, we have the Option Group control, and then we have the individual Option Button controls which are contained within the Option Group. So to re-create the Option Button Group in the exercise below, we will need to use 1 Option Group control, and 3 Option Buttons, four separate controls acting together as one. It is the Option Group control that contains the Control Source property for the group as a whole, whilst the Option Buttons each have their own individual Option Value properties.

How to Create an Option Button Group

Open the form in DESIGN VIEW.
Select the OPTION GROUP control from the DESIGN RIBBON
and drag it down onto the form design grid.
Whilst the OPTION GROUP control is still selected in the design grid, open or go to the PROPERTIES window, select the DATA tab, and then select team from the CONTROL SOURCE drop-down list (NB this assumes you have already set the CONTROL SOURCE of the form itself to the underlying table).
Form design grid with the option group control
added (highlighted orange). The property sheet is also visible
for the control with team entered as the control source.
Next, select the OPTION BUTTON control from the DESIGN RIBBON, and drag it down onto the OPTION GROUP which you added to the design grid in step 2. You should notice the OPTION GROUP turn black once the OPTION BUTTON is over the control and ready to drop. This indicates that the button will be added to the group.
Whilst the OPTION BUTTON is still selected, open and/or go to the PROPERTY WINDOW, select the DATA TAB, and check the OPTION VALUE property. It should say "1". This is the value that will be bound to the team field if the button is selected by the user at runtime.
Repeat steps 4 and 5 for the remaining two CONTROL BUTTONS checking the OPTION VALUE properties say "2" and "3" respectively.

Design grid showing three option buttons added to the
option group control. The property sheet for the third control button
(showing the option value property) is also visible.

The form should now be ready to open in FORM VIEW. The screenshot below shows the finished form with the underlying table. I have added some data to demonstrate how information contained in the Option Button Group is stored in the database.

Form with option button group. Select an option button on the form results in the option value property being stored in the underlying database table.

Note how team 3 is selected for the Sarah Arden record (ID 7) in the option button group. As you may remember, the option value property was set to "3" for the last button. This is the value that has been stored in the underlying database table.

This post has shown how we create an option button group using a combination of an option group control and three option buttons. I have demonstrated how the control source for the group as a whole is bound to a field in the forms underlying database table, and how each option button has a unique option value property which is the value stored if the user selects a given button at runtime.

In a future post, I intend to show how option button groups can be used to control the behaviour of a form at runtime, rather than being bound to a field in an underlying database table. To do this I will demonstrate how we can reference the option button group with VISUAL BASIC code, and respond to its click events.

Creating Custom Objects with Class Modules

2019-12-20T07:19:00.004-08:00

Suppose we wanted to work with a set of related variables with VBA code. Let's say these variables relate to a person. The information we want to store and process may include the person's name, age and gender. The best way to do this would be to create our own custom person object; with the above-mentioned variables as the object's properties, and any code to process or work with these properties, as the object's methods.

The advantage of creating a custom object is that a lot of related information, and the means to process it, is contained within a referenced entity that is easily accessed and used from different parts of the program.

There are two steps to creating a custom object. The first is to create a blueprint or design for the object which defines what information the object holds and the processing to be done on that information. To do this we create what is known as a Class Module. The second step is to create the actual object based on the class module - this is known as instantiation. Once the class module is in place, we can create as many instances, or objects based on the module, as we need. Hence, we may reuse the code we write in the class module over and over again.

Let's go through the steps to create a class module:

Creating a Class Module

Open the database in which the class module is to be created.

Click the CLASS MODULE icon in the MACROS & CODE group of the CREATE ribbon. This opens the VBA editor ready to enter code in our new class module.

Click SAVE CLASS MODULES in the FILE menu of the VBA editor.

Give the new class module the name "clsPerson" when the SAVE AS dialog window opens.

Save As dialog window

Although we haven't started to enter code, the new clsPerson Module shows up in the VBA Project in the CLASS MODULES node of the VBA Project Explorer, and in the MODULES section of the main Access Navigation Pane.

VBA Project Explorer

Access Navigation Pane

Code to Create an Instance of the Class Module

We will add code to our class module in the following section. Before doing so, however, let's look at how the class module is instantiated - ie how the blueprint becomes an actual object.

Objects are instantiated in code outside of the class module itself, where the object is to be used - this may be in the code belonging to an Access form or report, or perhaps in a conventional module.

There are two things we need to do. Firstly we define a variable through which the object can be referenced. We do this as follows:

Dim objPerson As clsPerson

Here objPerson is the name of the new object, and clsPerson defines the object's type - ie the Class Module on which it is based.

Secondly, we instantiate the object with the New keyword; and link the object to the variable that we created above with the Set keyword. This is done as follows:

Set objPerson = New clsPerson

However, before we can create an instance of the class we must first add our code to the class module. Let's start by writing the code to create the class module properties.

Setting and Getting Class Module Property Values

The person object we are creating has three properties - name, age and gender. Class properties are basically variables with CLASS MODULE level scope. As such, they are declared pretty much the same way as variables in the declarations section of the CLASS MODULE, except we use the PRIVATE keyword instead of DIM. As such, this is the code we need to write:

Private strName As String
Private intAge As Integer
Private strGender As String

By restricting scope to CLASS MODULE level (as opposed to GLOBAL), the data stored in each variable cannot be accessed directly from outside the class module. This means we must set up a special sort of interface within the class module so we may get and set our object property values after the object has been instantiated. We do this through PROPERTY LET and PROPERTY GET. These are located in the main section of the class module code with it's SUB's and FUNCTIONS. Here are the blocks of code to set and get the NAME property stored in the strName class module variable.

Property Let Name(strNameIn As String)
strName = strNameIn
End Property

Property Get Name() As String
Name = strName
End Property

Let's examine how these work. Imagine we have created an instance of the clsPerson class and called it objPerson. To set the Name property of the object to, let's say, "Sarah", we would use the following code:

objPerson.Name = "Sarah"

Doing this invokes PROPERTY LET NAME in the clsPerson class module code (see code snippet above). The string value "Sarah" is received as the strNameIn argument, and is then assigned to the strName class module variable that we set up in the declarations section above. As such, PROPERTY LET has set the value of a private class module variable from a location external to the class module itself.

Then to retrieve the value of the objects Name property from outside the class module, we just need to reference the object name (eg objPerson) and property (eg Name) in the same way. For example, to retrieve the property value and store it in a variable called strGetName we would use the following code:

strGetName = objPerson.Name

Doing this invokes PROPERTY GET NAME in the clsPerson class module (see code snippet above). PROPERTY GET returns a returns the value of the property by assigning a value to NAME. As such, "Name = strName" assigns the value stored in the strName class module variable to the objects Name property and returns this value whenever the object's Name property is referenced outside of the class module. It works in a similar way to calling a VBA FUNCTION.

The Age and Gender properties in the clsPerson class are set and retrieved in the same way. The screenshot below shows all the class module code to set and get the clsPerson properties:

Class module code for clsPerson

Adding SUB's and FUNCTION's to the Class Module

Class modules and objects are not just about storing and retrieving property values. As written above, we can also process and work with those property values in all kinds of ways. To do this, we add SUBs and FUNCTIONs to the class module. These are referred to as class methods, and providing they have been created using the PUBLIC keyword (as opposed to PRIVATE), they can be called from outside the class module after the object has been instantiated.

We are going to create a simple method to categorize a person based on the information stored in the object properties. When the method is called after instantiation, it will return one of eight categories which describe the person stored in our object. These are:

Male under 18
Female under 18
Male 18 to 30
Female 18 to 30
Male 31 to 64
Female 31 to 64
Male 65 and over
Female 65 and over

To do this we shall write a PUBLIC FUNCTION called categorizePerson. Here is the code we shall use:

Public Function categorizePerson()

On Error GoTo myError

    Dim strAgeCat As String
    If intAge < 18 Then
        strAgeCat = "under 18"
    ElseIf intAge > 18 And intAge <= 30 Then
        strAgeCat = "18 to 30"
    ElseIf intAge > 30 And intAge <= 65 Then
        strAgeCat = "31 to 64"
    ElseIf intAge >= 65 Then
        strAgeCat = "65 and over"
    End If
    categorizePerson = strGender & " " & strAgeCat
leave:
    Exit Function
myError:
    MsgBox Error$
    Resume leave
End Function

As we can see, the method uses IF .. THEN .. ELSEIF statements to select an age category based on the value of the age property stored in the intAge class module variable. The person's gender is then added to the description (thereby doubling the number of potential categories) and returned to the calling code.

The code to invoke the method after the object has been instantiated is as follows:

objPerson.categorizePerson()

Testing the Custom Module

In order to demonstrate how all this works in practice, I have created an unbound Access form to test our clsPerson class. The form allows us to enter the three property values used by clsPerson, and create a new instance of the class containing these values when the SET PERSON PROPERTIES control button is clicked.

After clicking SET PERSON PROPERTIES the btnSet_Click event (shown in the screenshot below) is invoked. It's code creates a new object instance called objPerson using the SET and NEW keywords. The values contained in the unbound textboxes are then referenced and set as the respective Name, Age and Gender properties for the object. Note how the objPerson variable is declared in the declarations section of the form module so it will persist as long as the form is still open or its value is reset to NOTHING when the DELETE PERSON button is clicked thereby invoking the btnDeleteObj_Click event.

Once the object has been created, the btnSet button is disabled through the code logic in the setControls sub (see screenshot above). This also enables the other three buttons which let us retrieve the property values, categorize the person, or delete the object.

Clicking the GET PERSON PERSON PROPERTIES button invokes the btnGet_Click event where the property values are retrieved, added to a string variable, and displayed in the OUTPUT textbox (see screenshot below).

Finally, clicking the CATEGORIZE PERSON button invokes code in the btnCat_Click event and the category is displayed in the OUTPUT textbox (see below)

As such, we can see how the properties and methods in clsPerson (ie PROPERTY LET, PROPERTY GET, and CATEGORIZE PERSON) are invoked with statements such as objPerson.categorisePerson after the object has been instantiated in the TEST CUSTOM PERSON OBJECT form. I hope this gives you a good example of how VBA class modules and custom objects work in practice.

Linking Access to an Excel Worksheet

2019-05-17T04:19:00.000-07:00

Excel is great for number crunching, and can even be used as a basic database to store information. However, when it comes to displaying and presenting information, the MS Access database has some big advantages over it's peer in the Office 365 suite of software. The reason for this is that Access has a number of tools and features specifically designed to make data easier to read and understand - the Reports facility being just one.

The good news is that Access has the ability to connect to, and work with, Excel data in two different ways. Firstly, Access can import data from an Excel worksheet into an Access table. This method lets us work with a "snapshot" of data from Excel as it was at the time the data was extracted (there is more information about importing from Excel in my post on "Importing and Exporting Data between Access and Excel"). The second method is to set up a "live" link between the Excel worksheet and the Access table. This has the advantage of giving us direct access to current data in the spreadsheet; so any updates, additions or deletions will show up in Access (after data is refreshed/reloaded) as soon as the data is saved in Excel. However, please do note that this connection is read-only; new data cannot be saved back into the Excel spreadsheet without specifically doing an additional export.

Today we are going to learn how to set up this "live" link. To do so, I have set up a sample Excel worksheet with fictitious personnel records.

Linking to Excel

Create a new Access Database, or open an existing one.
Select the EXTERNAL DATA ribbon.
Click the NEW DATA SOURCE icon in the IMPORT AND LINK group.

Above: Adding a New Data Source from the External Data ribbon.
Select FROM FILE from the drop-down menu, and then EXCEL from the sub-menu. This opens the GET EXTERNAL DATA - EXCEL SPREADSHEET dialog form.

Above: The Get External Data - Excel Spreadsheet dialog form.
Click the BROWSE button and select the Excel spreadsheet you want to link to.
Click the LINK TO A DATA SOURCE BY CREATING A LINKED TABLE radio button, and then click OK.
The next page of the wizard opens where you should see a sample of the worksheet you are linking to. If your worksheet had column headings, ensure the FIRST ROW CONTAINS COLUMN HEADINGS box is ticked, and click NEXT. (Please note that if your spreadsheet has more than one worksheet or named range, you will see an additional wizard page asking you to select the specific worksheet or range before the page in the screenshot below).

Above: First page of the Link Spreadsheet Wizard.
Enter the name you are going to call the linked Access table in the last page of the wizard, and click FINISH.

Once you have done this, you will see the new linked table appear in the left-hand Access navigation pane under TABLES. Note how the linked table icon is a blue arrow pointing to the Excel logo, thereby indicating the table is linked to an Excel spreadsheet.

Above: The table linked to Excel.

Now the link has been established to the Excel worksheet, we are free to base forms, reports and queries on the linked table as if it is native to Access itself.

Using Custom Functions in Calculated Controls

2019-04-05T02:30:00.002-07:00

Back in May 2012, I wrote a blog article on Writing Custom Functions for Access VBA. Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database.

The blog I wrote in 2012 explained how these custom functions can be accessed from sub procedures associated with forms and reports elsewhere in the database. Today, however, I am going to explain how custom functions can be utilised in calculated form controls, without needing to invoke them with VBA itself.

Let's begin by looking at the custom function we are going to use.

Custom Function to get Week Commencing

I have created a custom function called getWeekCommencing to calculate the week commencing date of a given week, in a given year. For example, the week commencing date of the 14th week of 2019 is 1st April 2019 (if we count Monday as the first day of the week). As such the function takes two arguments - week number and year. The full syntax for using the function is as follows:

getWeekCommencing(weekNumber, Year)

The code I used to create this function is as follows:

Public Function getWeekCommencing(intWeekNo, intYear)
On Error GoTo myError
    Dim intJan1 As Integer
    Dim varStartDate As Variant
    intJan1 = Weekday(DateSerial(intYear, 1, 1), vbMonday)
    varStartDate = DateAdd("d", -(intJan1 - 1), DateSerial(intYear, 1, 1))
    getWeekCommencing = DateAdd("ww", intWeekNo - 1, varStartDate)
leave:
    Exit Function
myError:
    MsgBox Error$
    Resume leave
End Function

If you are unfamiliar with VBA you can still copy this code into your database and use it within a calculated control. There are instructions for doing this below:

Instructions for adding the function to your database.

Copy the code above onto your clipboard.
Open the database you are going to add the function to.
Select the CREATE ribbon.

Above: The MODULE icon is located on the right-hand side of the CREATE ribbon.
Click the MODULE icon from the MACROS AND CODE group. This opens the VBA editor and creates a new module ready for the code to be added:
Paste the code into the VBA editor as in the screenshot below:

Above: The VBA Editor:

You may notice that I have changed the default name of the Module (see the PROJECT EXPLORER in the

left-hand side bar) from Module 3 to "myFunctions". You can do the same by highlighting the module name in the

project explorer and hitting the F4 button to bring up the PROPERTIES window. You may then change the name property

to one of your choice. However, this is not essential.

The custom function is now ready to use in a calculated form control.

Setting up the Test Data for this Exercise

Before we create the form to test the function, we first need to set up a simple database table containing test data. The table needs just two fields: WeekOfYear and Year, both of which are of the NUMBER data type.

Above: tblWeeks in Design View

Above: tblWeeks in Form View with test data entered.

Once the table is set up, we can add our test data as I have done in the screenshot above. Although there are 52 week in the year, the first week may not start on a Monday, so the data entered in WeekOfYear field can be anything between 1 and 53 (so the possibility of a partial first week is offset by Week 53).

The Calculated Control

Now we have added the getWeekCommencing function along with test data to our database, we can create the form containing the calculated control.

Above: The Week Commencing Calculator form in Design View.

To do this we need to create a form bound to the tblWeeks table. We then need to add three text boxes: the first text box (txtWeekOfYear) is bound to the WeekOfYear field in tblWeeks, and the second (txtYear) is bound to the Year field. The third text box (txtWeekCommencing) is unbound. This is where we are going to add our calculated control which returns the week commencing date (based on the data displayed in the WeekOfYear and Year textboxes).

Here are the instructions for creating the calculated control:

Create the third text box that we are going to use for the calculated control as in the screenshot above.
Display the PROPERTY SHEET if it is not already visibile. The PROPERTY SHEET icon is located in the TOOLS group of the DESIGN ribbon.
Select the new text box by clicking on it.
Click the OTHER tab of the PROPERTY SHEET, and change the NAME property to txtWeekCommencing. (I also suggest you change the names of the first textbox to txtWeekOfYear and the second to txtYear by selecting them and changing the NAME properties).
Click the DATA tab on the PROPERTY SHEET (having first selected txtWeekCommencing). We are now going to enter an expression which uses our getWeekCommencing custom function.
Enter the expression below directly into the CONTROL SOURCE property:

=getWeekCommencing([txtWeekNumber],[txtYear])

The txtWeekNumber parameter references the value in the txtWeekNumber textbox, and the txtYear parameter does the same for the txtYear text box.

In this exercise, the expression was entered directly into the CONTROL SOURCE

property. Alternatively, we could have clicked the elipse button at the end of the CONTROL SOURCE
property row to open the EXPRESSION BUILDER. Using the Expresison Builder is, however, something

I would need to cover another day, in another post!

And that's it! All you need to do now is save the form and open it in FORM VIEW. Use the form's navigation buttons to view each record in the tblWeeks table. The calculated control uses the getWeekCommencing custom function to processes the week number and year data for the current tblWeeks record, and displays the appropriate date.

NB - Just to clarify, the format for the week commencing date in this screenshot is for the UK (ie dd/mm/yyyy).

The same date using the US format would be 01/07/2019 (mm/dd/yyyy). However, your computer should be set up
to display the date format appropriate for your location automatically.

Converting Minutes to Hours:Minutes in MS Access

2019-03-02T14:14:00.000-08:00

There may be occasions when we need to convert the difference between a start time and an end time, and display the result in hours and minutes. For example, suppose we set up an Access table field to record the time a person starts work (the session_start field) and another to record when s/he finishes (the session_end) field. Rather than set up a third field to store the total time worked, we can instead calculate this value automatically using a calculated control in an Access form, or a calculated field in an Access query.

Above: the bottom text box (txtHoursWorked) is a calculated control.
The CONTROL SOURCE is an expression based on the values contained in the top two text boxes
(bound to the session_start and session_end fields in the undelying table).

However, to do this we must use two expressions to calculate the time difference, and then display the time difference in the hrs:mm format. So if we enter 20:40 as the session_start, and 22:06 for session_end, we need an expression to calculate the total minutes worked (which in this case is 86 minutes), and then convert the total minutes to hours/minutes format (which would be 01:26). To achieve this we use the DateDiff and FormatDateTime functions. Lets take a look at each function individually before combing them to produce the required result.

The syntax for the DateDiff function is as follows:

DateDiff("interval", time_1, time_2)

The function works by calculating the time difference between the time_1 and time_2 parameters, and returns the result in accordance with the interval parameter. In our case, we want need the result returned as minutes, so enter "n" as interval. Time_1 and Time_2 correspond to the start time and end time. To get these values for the expression, we can we put a reference to the values contained in the text boxes for "Start time" and "End time". In our example these text boxes are called txtStartTime and txtEndTime. This what our DateDiff function looks once we add the parameter values:

DateDiff("n", txtStartTime, txtEndTime)

The value returned in our example is 86 minutes.

The second function that we are going to use is FormatDateTime which converts the value returned by the DateDiff function into hours and minutes. The syntax we need to use in the FormatDateTime is as follows:

FormatDateTime( (minutes/(24*60) , named_format)

This first parameter is a division of the number of minutes (returned in the DiffFunction) by the total number of minutes in a day - which in our example is 86/(24*60) - and returns the result in accordance with the value contained in the named_format parameter. The latter is a numeric value indicating the required time format - which in our example is vbShortTime ie hours:minutes. The numeric value we enter for the vbShortTime format is 4. This is what our FormatDateTime function looks like once we add the parameter values:

FormatDateTime((86/(24*60) , 4)

The value returned from this expression is 01:26 - ie 1 hour and 26 minutes.

When we come to enter the actual expression used as the control source for the TOTAL (HRS:MINS) text box (referred to as txtHoursWorked in the screenshot below), we need to combine these two functions by nesting the DateDiff function inside the first parameter of the FormatDateTime function. This is done as follows:

FormatDateTime((DateDiff("n", txtStartTime, txtEndTime/(24*60) , 4)

Finally, we just need to enter this expression into the CONTROL SOURCE property of the
txtHoursWorked text box (see screen shot below):

The txtHoursWorked text box is highlighted on the left, and the expression used as the CONTROL SOURCE property value is highlighted on the right.

Creating a Custom Save Button for an MS Access Form.

2019-02-15T14:49:00.000-08:00

When we use an MS Access form to create a new record, or update one that already exists, the process of saving that data is normally taken care of automatically "behind the scenes." All we need to do is move to or create a new record, close the form, or close the database, and Access saves the original record for us.

Record Navigation Buttons: when a record is added or changed, moving to a new or different record

via the navigation buttons, will save the origin form record automatically.

However, there are times when it may necessary to save a record manually. Suppose, for example, we have two tables joined together in a one to many relationship. We create a new record on a form bound to a table on the one side of the relationship, and then click a button to open a new form bound to a table on the many side of the relationship. The newly opened form is then used to create a sub-record for the parent record on the original form.

Parent and sub-forms: In this screenshot, the parent form "Work Session" is bound to the tbl_work_session table, and the "Entry Log" form is bound to the tbl_entry_log table. There is a one to many relationship between the two tables, with tbl_work_session being on the one side of the relationship.

The problem in this scenario is that when we come to close the form for the sub-record (thereby saving the data to the underlying table on the many side of the relationship), the parent record on the original form has not, at this stage, been inserted into the underlying table on the one side of the relationship. That is to say, Access has not got around to autosaving the parent record since the parent form has not moved to any other record, or been closed etc - all we have done is click a command button to open a new form. As such we get an error message saying "You cannot add or change a record because a related record is required in [the parent] table"; this is because the rules of referential integrity have been broken (ie the sub-record in the many table requires a parent record in the one table).

However, it is simple to overcome this issue. We just need to save the parent record manually before trying to close the sub-record form. A quick and easy way to do this is to click the pencil symbol on the top left of the parent-form, or the SAVE icon in the RECORDS group of the HOME ribbon. This will save the parent record, thereby allowing it's sub-record to be saved afterwards.

The pencil symbol (above left) can be used to save the current record manually. Alternatively, the SAVE icon in the RECORDS group of the HOME ribbon (above right) can also be used.

However, this is not particularly useful from the perspective of user-friendliness. That is to say, a user may not realise or understand that this action is necessary. A better way of doing it would be to save the parent record automatically when the user clicks the command button to open the sub record form. As such, we need to alter the open form button (referred to as "CREATE NEW ENTRY" in our example above) so it also becomes a custom save button. To do this, we just add the following line of VBA code to the buttons ON CLICK event:

Application.RunCommand acCmdSaveRecord

See the screenshot below for the full code:

VBA code to save the parent record before opening the sub-record form.

Creating a Custom Save Button from Scratch - A Tutorial

Here is a step by step tutorial for creating a custom save button from scratch on Access for Office 365:

Open the form in DESIGN VIEW.
Go to the CONTROLS group of the CREATE ribbon.
Click the MORE button () to the lower right of the control icons, and de-select the USE CONTROL WIZARDS icon when the additional options appear.

Above: additonal options below control icons.
Click the BUTTON control icon () from the CONTROLS ribbon. The cursor will now change to a button icon when it enters the design grid.
Position the cursor at the desired location on the design grid, and click the left hand mouse button. This will create the new button at the selected location.
Select the new BUTTON contol by left clicking it, and open the PROPERTIES sheet from the TOOLS group of the DESIGN ribbon (if it is not already open).
Click the EVENTS tab on the properties sheet, and left click the ON CLICK row of the events tab.
Click the ELIPSE button (three horizontal dots) which appears on the right hand side of the row.

Above: the ON CLICK event row on the PROPERTIES sheet.
Select CODE BUILDER from the CHOOSE BUILDER dialog form which opens. This opens the VBA editor.
Enter the following code in the CLICK sub procedure for the control button.

Above: The CLICK sub procedure for the new control button shown in the VBA editor.

Now when the user clicks the new button at runtime, any data s/he entered will be saved to the corresponding record in the forms underlying table.

Passing Multiple Values via the OpenArgs Parameter of DoCmd.OpenForm

2019-01-25T14:28:00.001-08:00

The DoCmd.OpenForm command is a useful tool for the Access for Office 365 database developer wanting to control the manner in which a form opens during runtime. I have written about it previously in my post on how to "Control Form Opening". However, in today's post, we are going to focus on a specific area of this command's use - that is, how we can pass multiple values via its OpenArgs property.

The OpenArgs parameter of DoCmd.OpenForm gives us the means to pass a single string value to the OpenArgs property of new form being opened. After the form has opened, this value can then be read and used by any code able to reference the new forms OpenArgs property. As things stand, the limitation of this is that we can only pass one string value as the command executes. So let's see how we can get around this, and pass any number of strings values in one go.

To demonstrate how this works, I have created an Access for Office 365 form containing three text boxes and a submit button. When the user populates the text boxes and clicks SUBMIT during runtime, the values contained in each of the three text boxes are passed over to a new form by means of the OpenArgs parameter of the DoCmd.OpenForm command.

Above: Form to pass multiple values via the OpenArgs parameter of DoCmd.OpenForm.

You can download the full OpenArgsPost.accdb database used in the this exercise from here.

(NB I have tested the download process using the Chrome browser which works OK. The download process may fail using other browsers such as Microsoft Edge. If you experience issues please try using a different browser).

Part One: Passing the Values

Let's look at what happens behind the scenes when the SUBMIT button is clicked. First of all, clicking SUBMIT (referred to as btnSubmit in DESIGN VIEW) results in the control button's ON CLICK event firing. This, in turn, starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnSubmit.

The code begins by declaring a string variable called strOpenArgs:

Dim strOpenArgs AS String

The next two lines of code reference the value entered by the user in the first text box. The value is stored in the strOpenArgs string:

txtValue1.SetFocus
strOpenArgs = txtValue1.Text

The next two lines of code after that reference the value of the second text box, and append it to the strOpenArgs string preceded by a semi-colon (the significance of the semi-colon will be explained in part 3 of this post):

txtValue2.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue2.Text

This is then repeated for the third text box:

txtValue3.SetFocus
strOpenArgs = strOpenArgs + ";" + txtValue3.Text

So at this point in the program flow, the value of strOpenArgs is as follows:

"one;two;three"

This is the final value of the DoCmd.OpenForm OpenArgs parameter which will be passed to the new form in the following line of code:

DoCmd.OpenForm "frmReceiveOpenArgs", , , , , , strOpenArgs

Part Two: Retrieving the Values

Let's take a look at the new form which opens after the SUBMIT button has been clicked, and the event procedure explained above has finished executing:

Above: the new form which opens in response to clicking the SUBMIT button.

As you can see in the screenshot above, the new form consists of three empty text boxes (for displaying retrieved values) and a RETRIEVE button. When the user clicks RETRIEVE the text boxes will be populated with the values submitted from the first form. In our case, Retrieved Value 1 will display the value "one", Retrieved Value 2 will display the value "two", and Retrieved Value 3 will display the value "three".

Let's look at what happens behind the scenes after the RETRIEVE button is clicked. Like the first form, clicking the command button, which in this form is called btnRetrieve, results in it's ON CLICK event firing. This starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnRetrieve.

The code begins by declaring a string variable called strOpenArgs before referencing the forms OpenArgs property and storing its value in the string. (Just to recap, the value of the OpenArgs property (ie "one;two;three") was determined by the OpenArgs parameter of the DoCmd.OpenForm command invoked when the user clicked the SUBMIT button on the previous form):

dim strOpenArgs As String
strOpenArgs = Me.OpenArgs

The next couple of lines of code are very important. A string array called strValues is declared and has its elements populated with data returned from a function called getOpenArgsArray. This function is located in a separate module called Utilities which is global in scope. This is where the core of the processing is done for this exercise:

Dim strValues() As String
strValues = getOpenArgsArray(strOpenArgs)

The purpose of the getOpenArgsArray is to split the value of its parameter, strOpenArgs (which in our case is "one;two;three"), into three separate strings, with each array element containing an individual string. So for us, the strValues array is populated with the following values:

Array Element	Value
strValues(0)	"one"
strValues(1)	"two"
strValues(2)	"three"

These values are assigned to each of the three text boxes in the next six lines of code:

Me!txtRetValue1.SetFocus

Me!txtRetValue1.Text = strValues(0)

Me!txtRetValue2.SetFocus
Me!txtRetValue2.Text = strValues(1)

Me!txtRetValue3.SetFocus
Me!txtRetValue3.Text = strValues(2)

The end result can be seen in the screenshot below:

Above: the end result after the user clicks RETRIEVE.

In this way, we have used the DoCmd.OpenForm OpenArgs parameter to pass multiple values to a new form, thereby overcoming the limitation of only being able to pass a single string within the statement.

Part Three: The getOpenArgsArray Function

However, this post would not be complete without an explanation of the getOpenArgsArray function, as this is where the core of the processing was done for this exercise. Let's take a look at the code used in the getOpenArgsArray function:

Above: code for the getOpenArgsArray function.
NB: in this screenshot the strArgs array is declared with 7 elements.
This is because the function is global in scope and other event procedures using
the function may need addtional elements. This means that the OpenArgs
parameter of DoCmd.OpenForm can pass up to 7 values with this current setup.
If more elements are needed, the array can be declared with an adjusted number as necessary.

As mentioned in section 2 above, the purpose of the getOpenArgsArray function is to split the value of the strOpenArgs parameter passed to the function, into individual sections, and to save each section as a separate array element. So in our case, the function receives a string containing the value "one;two;three", and returns an array with three elements ie

Array Element	Value
Element 1	"one"
Element 2	"two"
Element 3	"three"

This is acheived by iterating through the strOpenArgs string in a FOR NEXT loop and appending each individual character to a temporary string called strBuildString. Then when the current character of strOpenArgs in the loop is one of the semi-colons, the value of strBuildString is saved to a new array element.

Lets break this down further.

The following three lines of code are concerned with iterating through each character contained in the strOpenArgs string. Each character is temporarily stored in a string variable called strChr:

For i = 1 To Len(strOpenArgs)
Dim strChr As String
strChr = Mid(strOpenArgs, i, 1)

The value of strChr is then checked to see whether or not it is a semi-colon. If it is NOT a semi-colon, the character is appended to the strBuildString variable :

If Not strChr = ";" Then
strBuildString = strBuildString + strChr

If, on the other hand, the character IS a semi-colon, the current value of strBuildString is saved as a new array element in strArgs(). The strBuildString variable is then cleared ready to build the value of the next array element:

Else
strArgs(argCounter) = strBuildString

strBuildString = ""

The element number of the array is kept track of in an integer variable called argCounter. This is incremented by one after the strBuildString variable is cleared:

argCounter = argCounter + 1
End If

Next i

After the last iteration of strOpenArgs has completed, the final array element is saved, and the return value of the getOpenArgsArray function is set to the value of the strArgs() array:

strArgs(argCounter) = strBuildString
getOpenArgsArray = strArgs
Exit Function

Changing "Entry Key Behaviour" to Allow New Lines within a Textbox

2019-01-04T04:04:00.002-08:00

In my previous blog post, I wrote about Adding Rich Text Formatting to MS Access Data. However, there was one related topic which I didn't cover - ie how to change the entry key behaviour to allow new lines within a text box. This is done by changing the ENTRY KEY BEHAVIOR property of the text box in question.

The ENTRY KEY BEHAVIOR property set to

NEW LINE IN FIELD for the txtDescription text box.

Let's imagine we have a text box on a form in which we are going enter a large amount of text data separated by paragraphs. We have set the field in the underlying table to accept LONG TEXT data, and perhaps also enabled rich text formatting. The first thing we do is resize the text box in FORM DESIGN view by selecting the text box and dragging the lower right corner so it is large enough to display a good area of text without having to constantly scroll down.

If we left the ENTRY KEY BEHAVIOR property at its default setting, it would not be possible for a user to create a new paragraph within the text box whenever they hit the ENTER key. What we need to do, therefore, is change the property value from DEFAULT to NEW LINE IN FIELD. This is how:

Open the form in DESIGN VIEW.
Select the text box control to be modified by clicking it.
Open the PROPERTY SHEET by clicking the icon in the TOOLS group of the DESIGN ribbon (if it's not already open).
Click the OTHER tab on the property sheet.
Locate the ENTRY KEY BEHAVIOR property and change the value to NEW LINE IN FIELD.

Once the property has been changed, the user can enter text in the text box and use the ENTER key to create a new paragraph without the focus shifting to the next control in the tab order.

Above: Example of a text box with the ENTRY KEY BEHAVIOR property modified to

NEW LINE IN FIELD.

Adding rich text formatting to Access data

2018-12-27T14:52:00.001-08:00

When we store information in an Access for Office 365 database, text formatting (eg setting text as bold or italic, and changing font size etc) isn't normally a consideration. For example, if we have a field storing a title, it is very unlikely that we would need to save the titles' font size. This is something we can add later to a text box property of a form or report where the date is presented to the user.

Above: notice the text box for the task field
is in a larger font with bold text.

Above: the property sheet where
the db designer sets formatting
properties of the text box.

However, there are certain instances when we do want to save formatting information relating to the data stored in a particular field of a table. This may be the case when a field contains a large amount of textual data, and we want the user entering the data to decide which text within the field is formatted in a particular way; not the database designer who can only apply formatting to all text displayed in any given text box control.

So how do we add rich text formatting to an Access for Office 365 database field?

Rich text formatting is achieved through the addition of html markup tags to the text they relate to. These are the same tags used by web pages for formatting content. However, we don't need any knowledge of HTML markup, because Access for Office 365 does it for us. All we need to do is change the TEXT FORMAT property of the field within the database table. NB this can only be done on a table field set to the LONG TEXT datatype.

Here are the instructions for setting up a LONG TEXT field for rich text formatting:

Open (or create) the table containing the field to be set to rich text in DESIGN VIEW.
Change the data type for the field to LONG TEXT in the TABLE DESIGN GRID.

Above: the entry field being set to LONG TEXT.
Change the TEXT FORMAT property to RICH TEXT in the field property grid.

Above: changing the TEXT FORMAT property to RICH TEXT.

We can now change from TABLE DESIGN VIEW to DATASHEET VIEW and add rich text formatting to data stored in the field we have just modified. Do this by highlighting some text within the field and selecting the desired formatting from the context menu that opens automatically. Alternatively, you can also use the TEXT FORMATTING group of icons from the HOME ribbon.

Above: an example of rich text formatting within an Access for Office 365 table field.

Displaying formatted data on Access for Office 365 forms and reports.

Now that we have set up the table for rich text formatting, there is one other thing we need to do if we want to display our formatted data in a text box control on a form or report. This involves changing the text box's TEXT FORMAT property to RICH TEXT.

Open the form containing the text box we want to change in DESIGN VIEW.
Select the text box control by clicking it in the design grid.
Open the properties sheet by clicking the PROPERTIES SHEET icon from the DESIGN ribbon.
Click on the DATA Tab on the properties sheet, and change the TEXT FORMAT property to Rich Text.

When we now re-open the form in FORM VIEW, rich text formatting is applied to any text the user had previously set before it was saved in the forms underlying table.

Above: an example of rich text formatting within an Access for Office 365 text box control.
Had we left the TEXT FORMAT property as PLAIN TEXT, any data displayed in the text box would
not have been formatted; any text that should have been, would have appeared in plain text between html markup tags.

Using MS Access Data in a Mail Merge

2012-06-08T05:45:00.000-07:00

Suppose you have an Access Database containing the names and addresses of your customers. Every now and again you may want or need to send a standard letter to each customer who has opted in to such mailings. However, you are aware that these letters are going to look so much better, and work so much more effectively, if they are personalised to each particular recipient. A basic example of this is to print the name and address of the customer at the top of the letter, and to include the customers title and surname in the greeting line. We can go much further than this, of course, and include information about the customers account and even their previous orders (if appropriate).

There are two ways of doing this. We could create an access report which is fine and good. After all, the data is stored in Access, so why not create a the letter by means of such a report? It is certainly possible to do so. However, there may well be an advantage in merging your customer data stored in an Access table or query into an MS Word word-processing document. It is arguably much easier to sit down and compose a well presented standard letter using MS Word (than it is to create the same document in an Access report). It might be said that Word is a better tool for this particular job.

Mail Merge

The process whereby data from a table is integrated into a word processing document is known as a mail merge. The following exercise explains how to use information from the Access table in Figure 1 below.

Figure 1: The schema of the Access table we shall be using.

Connecting to Access Data

This first stage in the process involves connecting MS Word to the database where the customer data is stored. We are going to be working on blank document here, but normally you would begin by composing your standard letter. The object of this exercise is to show how a Word document gains access to data in an MS Access table or query (rather than how to compose a well presented Word document per se).

Open MS Word.
Click the SELECT RECIPIENTS icon. This is located in the START MAIL MERGE group of the MAILINGS ribbon. Clicking the icon reveals a drop down menu.
Select USE EXISTING LIST from the drop down menu.
Browse to the location of your Access Database (in the window that has opened) and double click the it's file name. This opens the SELECT TABLE dialog form in which you see all the tables and queries from your database.

Figure 2: The SELECT TABLE dialog form.
Double click the name of the table or query containing the customer names and addresses. We are interested in the tblCustomer. The dialog form then closes. You are now ready to select which fields to use in your document.

Accessing Table or Query Fields

We have now connected to the Access Database and the table in which the customer data is stored. The next stage in the process is to insert fields from that table into the actual Word Document itself. We are going to use fields to enter the customer's address at the top of the letter, and to produce a greeting line. If we wanted, we could also go further and insert data from the table with the main text of the letter.

Click the location on the document where the field is to be inserted.
Click the lower half of the INSERT MERGE FIELD split button icon. This is located in the WRITE AND INSERT FIELDS group of the MAILINGS ribbon. A drop down list of fields appears (see figure 3 below).

Figure 3: Insert Mail Merge Fields.
Select the customers title field from the list. A title field place holder now appears on the document.
Repeat step 2, this time selecting the surname field. Make sure you leave a space between the two place holders.
Go to a new line and then repeat step 2 again, this time selecting the first address field. Repeat this step for each of the address fields, each field on a separate line.
Go down two lines below the address fields to enter the greeting line. Type a greeting such as "Dear ", leave a space, and then insert a title field, followed by another space, and then the surname field

Your document should now look like this:

Figure 4: The Mail Merge Field Placeholders.

Merging Access Data

Once you have added the mail merge fields to your document, you are nearly ready to run the mail merge. Before we do, however, it is a good idea to preview what the documents are going to look like after the data is merged. Do this by clicking the PREVIEW RESULTS icon on the MAILING RIBBON. Each record from the database is then merged onto a separate document. You can scroll through each of these "document records" using the navigation buttons in the PREVIEW RESULTS group. This is how our document looks when previewed:

Figure 5 (above): Preview for the first record of merged data.

Figure 6: FINISH AND MERGE.

We can now run the mail merge. Do this by clicking the FINISH AND MERGE button (see figure 6 above). You are then given the option to PRINT DOCUMENTS or EDIT INDIVIDUAL DOCUMENTS. Doing the latter opens a new instance of Word containing the merged documents. You are then free to make any adjustments that you see fit, and print out when ready.

VBA Project Anatomy and Scope

2012-06-02T11:39:00.000-07:00

Last week I explained how to create an Access VBA Custom Function in a global module. This was something of a departure from what I covered in previous blog posts. All of the programming I had covered previously has been associated with a given form or report - what we refer to as a Class Object. In this post we are going to take a step back and look at the general structure of a VBA Project as a whole, and in so doing we will examine the concept and workings of Scope.

VBA Projects

I have mentioned in the past that Access and VBA are two separate, albeit closely related, pieces of development software. When we create an access database, a VBA project linked to that particular database is created for us automatically. It is within this project that we create and store all the code relating to our database.


Figure 1: The VBA Project Explorer showing folders for Class Objects, Modules and Class Modules.

The VBA project consists of three main elements: these are class objects, modules and class modules. These can be viewed in tree diagram format via the VBA Project Explorer (see Figure 1 right).

Class Objects
Class Objects contain code relating to elements in a form or report. We covered these in some detail in the Learning Access VBA series of posts. As you may remember, code from class modules is generally triggered when a form or report event is triggered (for example, the OnCurrent Event is triggered when the user moves into a record displayed on a form).

Modules

Modules, on the other hand are not associated with any particular form or report, but do contain variables, sub procedures and functions that can communicate with them in various ways. They are a convenient place to keep code which can be read, written to, or called from the class objects. Moreover, when declared Public, any given variable, sub procedure or function within a Module can be accessed from many different class objects (and sub procedures within class objects) elsewhere in the project. As such, modules help us organise and manage our code, thereby making our VBA project more logical and efficient.

Lets elaborate on this further. Suppose we have a sub procedure called deleteRecord stored in a module. It purpose is to delete a particular record from a particular table when called. The advantage of locating this code in a module is so different forms in the database can also make use of this same code. So rather than re-write the same section of code in each form, we write it once in a module, and call it whenever one of the forms needs to use it. As such, we not only write less code, and save time in so doing, but we can easily maintain and modify that code since it is located in one module. That is to say, if we want to change how the deleteRecord command button behaves in all the sharing forms, we only need update our code in one central place.

Class Modules

So far we have said little about Class Modules, the third main element of a VBA Project. These are actually worthy of a complete series of blog posts in their own right (something which I may well do in the future). For now, however, let's just say they offer the programmer a way of creating their own custom objects made up of properties, methods and events. Like conventional modules, class modules also help the programmer organise and manage code, but in a way that is potentially more complex and powerful.

Scope
With the partitioning of class objects, modules and class modules, VBA Projects have the potential to become very large and complicated. On the one hand these different project 'zones' need to be self contained so something happening in one class module does not interfere with something happening in another elsewhere; yet on the other, they also need to exchange information and communicate. This is where the concept of Scope comes in.

The concept of scope relates to the way in which different zones of a VBA Project partition the processing of data that occurs within and between each of those sections. We are going to illustrate how this works in relation to variables and sub procedure/function calls. Lets start with how variables behave within a form's object class.

Figure 2: A basic Class Object to illustrate Scope .

Variables and Scope
The screenshot in figure 2 shows a class object containing two sub procedures, Form_Load() and mySub(). A total of three variables are declared within the class object, two of which, - varB and varC -are contained within sub procedures. VarA on the other hand is declared above both sub procedures in what is known as the General Declarations section. The difference in positioning has a direct effect on the scope of each variable. Let's examine this in more detail.

VarB is declared within the form's Form_Load() sub. This means the scope of varB is local to this sub, and can only be accessed by code within it. As such, we can can only read and write to this variable from within the same sub procedure in which it is declared. So, for example, if we assign a value to varB within the Form_Load() sub, we will not be able to read that value from code executed within mySub and vice versa. This is what is meant by a local variable.

VarA on the other hand is written within the General Declarations section outside of any sub procedure. The scope of any variable declared here is class object wide. That is, it can be accessed from anywhere within it's own class object. As such, we can assigned a value to varA in the Form_Load() sub, and then read that same value externally from mySub. What we can't do, however, is access the variable from outside of this particular class object. To do this we need to declare a Public Variable from a Module.

Public Variables
The scope of a public variable declared in a module is project wide - that is to say, it can be accessed from any class object, module or class module in the project. Public variables are declared using the Public Statement (as opposed to the usual Dim Statement) in the module's General Declarations section (see Figure 3 below for an example).

Figure 3: The shows the Public Variable, varX, in the General Declarations section of Module1.

As you can see, the VBA Project in Figure 3 contains two class objects (Form_frmReadPublicVariable and Form_frmScopeTest) and a module (Module1). Since the variable varX has been declared with the Public statement in the General Declarations section of Module1, it can be accessed from both class objects. As such we can assign a value to varX in the Form_frmScopeTest class object, and then read that same value some time later from a sub procedure in Form_frmReadPublicVariable. However, the disadvantage of using public variables is that they make code potentially more difficult to debug, especially in large projects where the variable might be accessed from sub procedures in many different class objects, modules and class modules. To some extent we could say that public variables undermine the code management advantages from working in a partitioned VBA project. That said, they can be a really useful programming tool if used carefully.

Scope and Persistence
Before we move on to look at sub procedure and function calls, it is important to say something about the nature of scope and persistence. In the context of scope, persistence is how long a value assigned to a variable lasts or persists. How long, that is, before the variable value is lost and forgotten. So, for example, if we assigned a value to a local variable declared within a sub procedure using the Dim Statement, that value will persist only as long as the sub procedure is running⁽¹⁾. Once the sub has finished the value assigned to the variable no longer persists. Likewise if a variable is declared within the general declarations section of a form class object, any value assigned to the variable will only persist whilst that form is still open. Once closed the value is lost. Of course it is public variables which have the greatest persistence. Any value assigned to one of these will last until the database application is closed.

(1) There is an exception to this rule. We can declare the variable using the Static Statement instead of Dim. Doing so enables the value to persist, but only within the sub where it has been declared. The next time that particular sub executes, the previous variable value is remembered and can be read by code (providing that code is still within the same sub).

Scope and Sub Procedure / Function Calls

If you read my post on Writing Custom Functions for Access VBA last week, you may already be familiar with the concept of calling a function. You may recall how the myPercentage function located in an external module was called from a sub procedure within a form's class object. You may also recall how we passed three parameters to that function. The significance of being able to pass parameters in this way, is very important from the perspective of scope. That is to say, if the calling sub procedure needs to share a local variable with the called function or sub, passing local data in the form of a parameter enables us to bypass the issue of scope. As such, parameters are a way of letting various parts of the VBA project share information and communicate without compromising the real need to keep the project strictly partitioned in an organised manner.

Lets examine a simple example of how passing a parameter, this time in a sub procedure call, can bypass the issue of scope:

Private Sub Command9_Click()
Dim varThis As Integer
varThis = 10
Call thatSub(varThis)
End Sub

Private Sub thatSub(argReceived As Integer)
MsgBox ("The argument received is: " & argReceived)
End Sub

Here we have two private sub procedures within the a form's class object. When the user clicks a command button, the Command9_Click sub procedure begins to run. It defines a local variable called varThis and assigns it with the integer value 10. The next line calls the thatSub sub procedure, passing the local variable varThis as the parameter. This call results in the execution of thatSub. Notice how the first line of thatSub contains the code argReceived As Integer within brackets. This is where the passed parameter (varThis) is received by the called sub procedure. The parameter which has been passed is now referred to as an argument, and it's value can be accessed from code within the receiving sub procedure. As such, when the second line of code in thatSub runs, a message is displayed ending with the value contained in argReceived (see figure 4 below).

Figure 4: The message displayed from the
MsgBox method in thatSub.

We see, therefore, that the value contained in what is a local variable has been passed to a different sub procedure which would otherwise have been outside that variable's scope.

Conclusion
In this post we have seen how the VBA project is partition into various zones, and how those zones are largely self contained in terms of data processing. This partitioning is maintained via a set of rules collectively known as scope, the advantage of which is the management of code within the project. Since there is a legitimate requirement for these zones to exchange information, the working of scope can be bypassed in a managed manner through the passing of parameters and receipt of arguments.

Writing Custom Functions for Access VBA

2012-05-25T07:38:00.000-07:00

VBA Custom Functions work in a similar way to any inbuilt MS Access Function. Both types can be called within VBA sub-procedures, and then return a value for the function's 'result'. Take the inbuilt DLookUp function, for example. This is written as follows:

varResult = DLookUp ( parameter1, parameter2, parameter3 )

Three things happen when we call this function:

The function is called using VBA code (passing three parameters in the process),
The function looks up information stored in an access table (based on the parameters passed),
It then returns that information, storing the result in a variable called varResult.

On account of DLookUp being a built in function, we do not get to see what happens in stage two. Custom Functions are different in that we actually write a block of VBA code that executes at that stage. Once we have set this up, we can call our custom function as often as we like. Moreover, because custom functions tend to be located in a VBA global module, and declared as 'Public' in scope, they can be called from multiple points within our application. In this way, the creation of custom functions make our programming more organized and manageable.

Figure 1: This screenshot of the VBA Project Explorer shows
a module call modCustomFunction. Providing the custom functions contained within are
declared 'public' they can be called from anywhere within the project - such as
the form which I have called frmFunctionDemo.

How to Create a Custom Function

When we create a custom function we need to write the code that executes once it has been called from a sub procedure. As we shall see, this code is structured in a similar manner to an ordinary sub. However, we also need to include a line of code which specifically returns a value back to the calling code. This is done by assigning a value to the name of the function (in a similar manner to assigning a value to a variable). For example:

Public Function myPercentage(argScore As Integer, argOutOf As Integer)

myPercentage = (100 / argOutOf) * argScore 2

End Function

This creates a function called myPercentage. The first line of code declares the function, and receives two arguments from the calling code ie argScore and argOutof. The second line of code in this example returns the result of the myPercentage function. There can any number of lines of code preceding the returning line. However, I have provided a simple example which returns the result of the function in the same line of code as a percentage calculation. This code is stored in a global module called modCustomFunctions (see Figure 1 above).

Here is the code I have used to call this function:

Private Sub cmdCalculate_Click()

Me!txtPercentageResult = myPercentage(Me!txtScore, Me!txtOutOf)

End Sub

This code executes when a form command button (cmdCalculate) is clicked by the user. The function (myPercentage) is called in the second line of code. As you can see, the calling code for myPercentage passes two parameters, the values of which reference two text box controls - txtScore and txtOutOf. The function uses these parameters (which are received as the arguments argScore and argOutOf) to calculate the percentage and return the result. The result is then displayed in a third text box called txtPercentageResult.

Figure 2: This is the form from which our function is called. The CORRECT ANSWERS textbox is called txtScore, No OF QUESTIONS is called txtOutOf, and the PERCENTAGE textbox is called txtPercentageResult. The CALCULATE button is called cmdCalculate.

Figure 3: The top window shows the code calling the myPercentage function. The lower window shows the code from the myPercentage function itself.

Your Feedback?

2012-05-18T06:27:00.001-07:00

I have been writing the MS Access Tips blog around three years, and it is now being read by hundreds of people each day. I am very interested to learn more about who is reading my blog, and and what people think about it. I would be really grateful if you could take the time to answer 6 simple question in a survey that I have set up for this purpose. This will help me to continue writing articles that are relevant and interesting to you, the readers.

Please click the link below to begin the survey. Many thanks.

Survey for the MS Access Tips Blog

Using the Make Table Query to Collect Archive Data

2012-05-11T08:31:00.000-07:00

Information stored in a database is likely to change over time. When details change, data may be overwritten, and once this happens it is lost. There are certain scenario's where it is important to have access to 'historic' data, by which I mean a snapshot of information which was stored in the database at a given point sometime in the past. Let's take an Order Management System for example. Suppose you need to find a customer contact name from two years previous? If the customer in question happened to be an organisation or business, it is possible that there was a different person in post at that time. In this case, it would be useful to have some facility to access data from that point in time. Whilst we can't create this sort of archive from the present backwards, we could have taken a snapshot of this data at periodic intervals previously. One way of doing this was to have created a Make Table Query to extract relevant data from the database with a query and store the results in a new table. These can then be saved in a separate archive database ready to be accessed if and when needed.

Taking the Order Management System scenario, there is data stored in four related tables, and only certain fields from each table need to be archived. We could simply make a copy of each of the four tables, but this would result in the storage of unnecessary information. It would also be less convenient to access at a later point in time.

The related tables in question are tblCustomer, tblOrganisation, tblPerson and tblAddress.

Figure 1: The Make Table Query for an Order Management System.

Creating a Make Table Query

Before we create the Make Table Query, you might like to create a new blank database file. This is so we have a separate database into which we can paste the new table from the query. In this exercise I have called it HistoricData.accdb.

Here is the procedure to create a Make Table Query:

Open the database which we are going to query (In my case this is the Order Management System).
Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).
Select the tables to be used from the SHOW TABLE dialog form. I have selected tblCustomer, tblOrganisation, tblPerson and tblAddress.
Next select all the fields from the existing tables to be used in the query. These not only form the query results, but also the structure for the new table. I have chosen fields from all four tables so that the new table will be self sufficient.
Amend the table relationships (or Joins) as required.
Click the MAKE TABLE icon (located in the QUERY TYPE group of the DESIGN ribbon). This opens the MAKE TABLE dialog form (see Figure 2 below).

Figure 2: The Make Table Dialog Form.
Enter the name of the new table that we are going to create. I have called mine tblCustomerTeamsMay2012.
Click the option button for ANOTHER DATABASE. This ensures the new table will be created in a different database.
Browse to the location of the external database. I have called mine HistoricData.accdb. Double click the file to select and then click OK to close the dialog form.
Test the Query by clicking the DATASHEET VIEW icon (located in the RESULTS GROUP of the DESIGN ribbon).
If you are happy with the data displayed, you can run the Make Table Query by clicking the RUN icon (located in the RESULTS group of the DESIGN ribbon). This will now create the new table in the external database.

You can now open the external database and view the newly created table:

Figure 4: The newly created tblCustomerTeamsMay2012 table (Design View).

Now all the database administrator has to do is run this query every month, changing the new table to the appropriate month. Over time, we collect a 'warehouse' of data ready to be accessed if and when needed. Since this data is stored in a separate database, it does not impact upon the performance of the actual system from which the information was collected.

Using the Access Database Splitter

2012-05-04T08:15:00.000-07:00

Last year I wrote a post on Linking to an External Data Source. This is where we split an Access database into a Back End (which stores all the tables and data), and a Front End (which contains all the forms, queries and reports). There are a number of advantages to observing this practice. To begin with, splitting a database file in this way is all but essential if it is to be deployed on a network in a multi-user environment. Another big advantage is that we can easily replace a front end file with a new updated version with minimal disruption - the actual data stored in the back end tables is not touched in this process.

If you read the my post last year, you may remember that I showed you how to link a front end Access file to an existing back end database. This is fine if the back end database already exists, or if you decide to create the application in two separate files from the start. However, many developers create or inherit a complete database application as one stand alone file, and then proceed to split the database. Splitting the database can be done manually, but this is often time consuming and there is always the possibility of making errors in the process. An easier and quicker way of doing this is by using the Access Database Splitter. This is a wizard that splits the database into a front and back end, as described above.

Splitting an Access Database

The database that I am working with here is an Order Management System containing a number of tables. If you want to try this on one of your own database files, it does not matter how many tables it contains - it could have a hundred or it could just have one.

Figure 1

Click the ACCESS DATABASE icon (located in the MOVE DATA group of the DATABASE TOOLS ribbon). See figure 1 on on the right. This opens the DATABASE SPLITTER Wizard.
Click the SPLIT DATABASE button at the bottom of the Wizard (see figure 2).
You are now asked to select one of your folders for the new back end Access file. Browse to a location of your choice, enter a back end file name, and then click SPLIT. After a moment or two, you should get a message box saying "Database Successfully Split".
Click OK.

Figure 2: The Database Splitter Wizard from Stage 2.

You can now look at the navigation pane of the front end database and see the linked tables (see figure 3). You can try adding data to one of your tables and then open the back end file to see it stored there. You could also try copying the front end file in order to access the back end tables with different front ends. In so doing you can see the basic principle behind how multi-user systems operate*.

Figure 3 (above): This is how linked tables appear in the Front End Database file.

Figure 4: Your Database is now split into two files one for the front end and one for the back end.

*Tip: If you want to run more that one Access front end at a time, you will need to open multiple instances of the Access Database. So rather than clicking on the access front end file directly (ie from within Windows Explorer), you should open Microsoft Office Access from the windows START button or DESK TOP and then open each front end file from the GETTING STARTED WITH MICROSOFT OFFICE ACCESS screen.

Hiding the Navigation Pane with VBA

2012-04-27T05:48:00.000-07:00

Once you have created an Access Database Application to be used by other people, it may well be important for you to prevent users from gaining access to any of its design features. By this I mean, you may not want users to modify your tables, forms and queries etc in design view (inadvertently or otherwise). There are a number of measures you need to take in order to secure your database against this possibility. One of these involves preventing users from accessing the NAVIGATION PANE, a major doorway to all of your database objects. Hiding the NAVIGATION PANE can be done with a couple of lines of VBA Code.

To use this code, you will need one of your database forms' to open automatically as your application opens. There are instructions to do this in my post on How to Display a Form Automatically. Once this is in place you can insert the following code into that form's ON LOAD event.

DoCmd.SelectObject acTable, "tblCustomer", True
DoCmd.RunCommand acCmdWindowHide

The purpose of the first line of code is to select any object in the navigation pane. We have selected one of the database tables called tblCustomer, and then set the INNAVIGATIONPANE parameter to true. Once a database object from the navigation pane has been selected, we can then use the DoCmd.RunCommand with the acCmdWindowHide parameter in the following line of code. This has the effect of hiding whichever window happens to contain the selected object.

The user can quite easily get the Navigation Pane back by pressing the F11 Access Special Key (assuming that he or she knows about it), but this facility can be disabled. To do this you will need to deselect the USER ACCESS SPECIAL KEYS check box in the CURRENT DATABASE section of ACCESS OPTIONS, (via the OFFICE button).

Hiding the Navigation Pane alone is not sufficient to secure a database, but it is one important part of doing so.

Calculating a Future Date with the DateAdd Function

2012-03-30T02:59:00.000-07:00

If you read my blog last week you may have seen the sample Lending Library Database that I created to demonstrate a working example of the DAO Recordset. You may have noticed that I used a function called DateAdd to calculate the future date that a library book is due for return. As you may remember, this date was automatically entered as the value for the DueDate field in the tblLoan junction table via VBA code.

I thought it would be interesting to take a closer look at how the DateAdd function works. However, please do bear in mind that the DateAdd funciton can also be used in Queries and Calculated Form Controls (not just in VBA code). This is how the syntax for DateAdd is constructed:

DateAdd("interval", number, date)

Figure 1 (above): This shows the DateAdd function used
as a Calculated Field in a Query. You can see an example of a Calculated Field
in my post on the related DateDiff Function. (NB DateDiff calculates and returns the difference between
two dates, whereas DateAdd calculates and returns a new date based on the value of the number parameter passed).

Figure 2: This shows the DateAdd function used as a Calculated Control in a form. It can be entered
into the CONTROL SOURCE property or directly into the text box on the Form Design Grid.

DateAdd Parameters

As you can see, the DateAdd function has three parameters - interval, number and date.

Interval

The interval parameter determines whether we are adding intervals of days, weeks, or months etc. The parameter is entered as a string value; days are entered as "d", weeks as "ww" and months as "m". There are 10 possible parameter values to choose from, ranging from years "yyyy" right down to hours "h", minuets "n", and seconds "s".

Number

The number parameter is the number of intervals to be added. So if we want to calculate a date 30 days in the future, we would set the interval to "d" and the number to 30.

Date

The date parameter is the base date we are applying the DateAdd function to. As such, if we want to calculate a date 30 days from 1/4/2012, we would simply enter 1/4/2012 as the date parameter value. If you want to calculate a date from today (whatever date that may be on the day you are reading this) you can enter the Date() function as the actual parameter value. This will return the current date when the database is being run.

Calculating a Library Book Due Date

This is the syntax I used in the last weeks lending library database to calculate a DueDate 30 days from when a book is issued:

DateAdd ("d", 30, Date())

Figure 3: This shows the DateAdd function used in the fourth line of this VBA code snippet.
The rstLoan object is a DAO Recordset, and DueDate is the field name in the tblLoans Table.
You can see the rest of the code in last weeks blog post on the DAO Recordset.

As you can see, the interval used is days, the number parameter is 30, and I have used the Date() function to return the current date for the date parameter.

The DAO Recordset in Action - An Example Lending Library Database

2012-03-23T07:45:00.000-07:00

Last week I wrote a post on How to Access Data with VBA Code. Instrumental in this was the use of the DAO Recordset Object. Through using this object we were able to add, find, edit and delete records from an Access Database table or query. This week I am going to provide a working example of how the DAO Recordset is used in an actual database application. In order to do this, I have created part of a Lending Library Database for demonstration purposes. This can downloaded by clicking the link.

The Lending Library Database

The table at the centre of this database is the Loans table (jnkLoan). As you can see from figure 1, it is a junction table (from a Many to Many Relationship) which contains the foreign key from both the Library Users table (tblLibraryUser) and the Library Stock Table (tblLibraryStock). The purpose of tblLoans is to store the details of each loan transaction whereby a library user borrows or returns an item of library stock.

Figure 1: The table relationships from the Lending Library Database.

Figure 2 below shows the Library User Account Form where these loan transactions are made. Transactions are occur when the user enters a number into the ITEM BARCODE text box, and clicks the ISSUE or DISCHARGE command button. This then runs a VBA sub procedure which uses the DAO Recordset to create a loan record in tblLoan.

Figure 2: The User Account form. Library items are issued and discharged from here.

The User Account Form also has an On Loan subform to list items out on loan to the library user. The RECORD SOURCE for this subform is qryLoansSub, and contains fields from the jnkLoan, tblLibraryStock, and tblBook tables. There is a criteria value of Is Null on the ReturnDate field. As such, any loan transaction without a ReturnDate (belonging to the UserBarcode from the parent form) is listed as being On Loan. Moreoever, the VBA code initiated by the ISSUE and DISCHARGE buttons ends with a line to requery the subform. This ensures the subform updates as soon as a stock item is issued or discharged.

Issuing an Item

So lets have a look at the VBA code which runs when the ISSUE button is clicked:

Private Sub cmdIssue_Click()
On Error GoTo trapError
Dim rstLoan As DAO.Recordset
If IsNull(DLookup("StockBarcode", "tblLibraryStock", "StockBarcode = " & Me!txtIssueBarcode)) = True Then
MsgBox "Sorry - Barcode Does Not Exist", vbInformation, "Invalid Barcode"
GoTo leave
Else
Set rstLoan = CurrentDb.OpenRecordset("jnkLoan", dbOpenDynaset)
rstLoan.FindLast "StockBarcodeFK = " & Me!txtIssueBarcode
If rstLoan.NoMatch = False Then
If IsNull(rstLoan!returndate) = True Then
MsgBox "Book Is Already On Loan. Please discharge before Issuing.", vbExclamation, "Already On Loan"
GoTo leave
End If
End If
rstLoan.AddNew
rstLoan!StockBarcodeFK = Me!txtIssueBarcode
rstLoan!UserBarcodeFK = Me!UserBarcode
rstLoan!IssueDate = Date
rstLoan!DueDate = DateAdd("d", 30, Date)
rstLoan.Update
Me!subOnLoan.Requery
Me!txtIssueBarcode = Null
DoCmd.GoToControl "txtissuebarcode"
End If
leave:
If Not rstLoan Is Nothing Then
rstLoan.Close: Set rstLoan = Nothing
End If
Exit Sub
trapError:
MsgBox "Error " & Err.Number & ": " & Error$
Resume leave
End Sub

The code begins by checking that the item barcode actually exists, and is not, therefore, a data entry error. This is done using the DLookUp function on the StockBarcode field of tblLibaryStock. If it does not exist, a warning message is displayed, and the sub procedure comes to an end. If it does exist, the code continues to open a DAO Recordset object based on jnkLoan. This is then searched using the FINDLAST method of the recordset object to find the most recent transaction involving that particular item. If a record is found, the ReturnDate value is checked to make sure the item is not recorded as already being on loan (it is an not an uncommon human error for some items to be shelved without first being discharged). If the item is recorded as being On Loan an error message is displayed asking the staff member to discharge the book before re-issuing. The procedure then comes to an end. If there had not been a previous transaction for that item, or if the previous transaction has a ReturnDate value, the code continues to create a new record in jnkLoan.

The following block of code (a section extracted from the main code above) is where the new record is created:

rstLoan.AddNew
rstLoan!StockBarcodeFK = Me!txtIssueBarcode
rstLoan!UserBarcodeFK = Me!UserBarcode
rstLoan!IssueDate = Date
rstLoan!DueDate = DateAdd("d", 30, Date)
rstLoan.Update

Here the AddNew method of the Recordset Object is called to create the new record. Then the StockBarcodeFK field is set to the value of the Item Barcode textbox; the UserBarcodeFK field is set to the value of the UserBarcode on the User Account Form; the IssueDate field is set to the current date via the Date function; and the DueDate field is set to 30 days from the current date via the DateAdd function. The changes made to the rstLoan Recordset are then saved back to tblLoan via the recordset object's Update method. Finally the code re-queries the OnLoan subform, puts the focus back on the Item Barcode textbox, and closes the recordset.

Discharging an Item

The sub procedure which runs when a book is discharged is very similar. The main difference is that rather than creating a new loan transaction record, the last loan transaction record is found, and then edited by putting the current date in the ReturnDate field. Here is the discharge code:

Private Sub cmdDischarge_Click()

On Error GoTo trapError

Dim rstLoan As DAO.Recordset

If IsNull(DLookup("StockBarcode", "tblLibraryStock", "StockBarcode = " & Me!txtIssueBarcode)) = True Then

MsgBox "Sorry - Barcode Does Not Exist", vbInformation, "Invalid Barcode"

GoTo leave

Else

Set rstLoan = CurrentDb.OpenRecordset("jnkLoan", dbOpenDynaset)

rstLoan.FindLast "StockBarcodeFK = " & Me!txtIssueBarcode

If rstLoan.NoMatch = False Then

If IsNull(rstLoan!returndate) = False Then

MsgBox "Book Not On Loan.", vbExclamation, "Already On Loan"

GoTo leave

End If

rstLoan.Edit

rstLoan!returndate = Date

rstLoan.Update

Me!subOnLoan.Requery

Me!txtIssueBarcode = Null

DoCmd.GoToControl "txtissuebarcode"

End If

leave:

If Not rstLoan Is Nothing Then

rstLoan.Close: Set rstLoan = Nothing

End If

Exit Sub

trapError:

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

Resume leave

End Sub

If you would like to try all this out, please feel free to download the sample Lending Library Database. You can view a list of library item barcodes to work with by clicking the LIBRARY STOCK command button at the top of the User Account Form.

Accessing Data with VBA Code - Introducing the DAO Recordset

2012-03-16T09:54:00.000-07:00

This post is all about how we can use VBA code to access the data stored in our database. By access, I mean, being able to add, find, edit or delete records. In so doing we are effectively automating data access thereby enabling us to create some very powerful database applications.

Creating a Reference

To do this we are going to make use of Data Access Objects (DAO) which is contained in the Microsoft Office 12 Access database engine Object Library. The very first thing we must do is create a reference to this library if it does not already exist. The procedure for doing this is really quite simple:

Open the VBA Editor.
Open the TOOLS menu.
Select REFERENCES from the TOOLS menu.
When the REFERENCES dialog form opens, check to see whether Microsoft Office 12 Access database engine Object Library has a tick against it. If it has it should be towards the top of the list (all checked references appear above the others, and those that are not, appear in alphabetical order below).
If the library mentioned in stage 4 is not ticked, you will need to scroll down the list to find it, and then tick the check box.
Click OK to close the dialog form.

The reference has been created and you are now ready to start using Data Access Objects.

Introducing the DAO Recordset

The DAO Recordset is basically a VBA object which enables us to gain access to information stored in database tables or queries via code. It is VBA's representation of the table or query, and we make use of the methods and properties of the DAO Recordset to manipulate the data that it contains.

The first coding task we have to perform is to create an instance of the recordset object. This is then stored as an object variable. We do this with the following line of code:

Dim rstNameList as DAO.Recordset

This creates an object variable called rstNameList. Our the next task is to set the value of this object variable. This effectively means that we are setting rstNameList to represent a particular table or query in our database. This is done as follows:

Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

This line of code sets the value of object variable rstNameList to a DAO Recordset based on a table called tblNames. This is done by using the OpenRecordset method of the Recordset Object. This is applied against an object reference to the current database - hence our use of the CurrentDb method of the Application object preceding OpenRecordset. Don't worry if this sounds confusing, just remember to enter the name of your table or query as the first parameter of the OpenRecordset method. The second parameter, dbOpenDynaset simply opens the recordset as a Dynaset type (which is a particularly versatile type).

We can now apply the methods and properties of the Recordset class against the object variable rstNameList. As we do so, remember that they are acting on a virtual representation of the database table or query being used. Lets look at how we can add a record to our table via rstNameList:

Adding Records

Imagine our table is comprised of four fields: Id, FirstName, Surname, and DOB (ie date of birth). This is the data our table currently holds:

Figure 1: Table data from tblNames.
The rstNameList Recordset Object is based on this table.

The following code is designed to add a new record for a person called Graham Bell:

Private Sub cmdAdd_Click()

Dim rstNameList As DAO.Recordset

Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

rstNameList.AddNew

rstNameList!FirstName = "Graham"

rstNameList!Surname = "Bell"

rstNameList!DOB = #7/10/1970#

rstNameList.Update

If Not rstNameList Is Nothing Then

rstNameList.Close: Set rstNameList = Nothing

End If

End Sub

Lets go through this:

This code is triggered when a command button called cmdAdd is clicked by the user. The code then declares an object variable called rstNameList which is subsequently set to a Recordset based on tblNames.

The central block of code calls the AddNew method of the Recordset object. This basically signals that a new record is going to be added to the recordset. The next three lines reference the FirstName, Surname and DOB fields, setting their values respectively. As such the FirstName field value is set to "Graham", Surname to "Bell", and DOB to 7/10/1970. Note the FirstName and Surname fields are of the STRING data type, so are consequently enclosed within quotation marks ie ""; whereas the DOB field is of the DATE data type so is enclosed between hash marks using the American Date Format ie #mm/dd/yyyy#. (Had we used the number data type this would not have needed to be enclosed by anything). Finally the central block of code saves the new record by calling the Update method of the Recordset object.

The final block of code simply closes the recordset object and destroys the object variable reference so as not to use unnecessary memory.

You can now go back to the original table tblNames, and click Refresh to see the new record:

Figure 2: The tblNames table after new record is added via VBA Code.

As you can see, the new record has been added at the bottom of the table. Note that we did not need to set the value of the ID field as this is of the AUTONUMBER data type and is added by Access automatically.

Editing Records

The code for updating a particular record is very similar to the central block of code in the in the previous example. The main difference is, however, that we must find the record prior to making any changes. To do this we are going to use the FindFirst method of the Recordset object. The syntax for this is as follows:

rstNameList.FindFirst "Id = 5"

Notice that this method passes the parameter "Id = 5". This is basically an SQL WHERE clause without the WHERE keyword. It tells access to find the first record in the recordset where the value of the ID field is 5. There is more information on SQL Where in my Gentle Introduction to Access SQL. Just remember that the parameter must be enclosed within quotation marks, so if the value searched for is itself a string, you must not only enclose the parameter within quotation marks ie "", but you must also enclose the actual value to be found within single quotation marks ie ' '. For example if you wanted to search for the surname Lang, the syntax would be:

rstNameList.FindFirst "Surname = 'Lang' "

It is also possible to test whether a record has actually been found using the NoMatch property of the Recordset object as follows:

If rstNameList.NoMatch = false Then

.... code to run if record is found.

Else

....code to run if no record is found.

End if

Once we have found the record, we can then go on to edit any, or all, of its fields. This is done with the following code:

rstNameList.Edit

rstNameList!DOB = #8/10/1970#

rstNameList.Update

Notice how we have used the Edit method of the Recordset object instead of AddNew. The rule is use Edit if you want to edit an existing record, and use AddNew if you want to create a new record. Once the record has been edited it needs to be saved using the Update method as before. So lets look at the whole code snippet:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

rstNameList.FindFirst "Id=5"
If rstNameList.NoMatch = False Then
rstNameList.Edit
rstNameList!dob = #8/10/1970#
rstNameList.Update
End If

If Not rstNameList Is Nothing Then
rstNameList.Close: Set rstNameList = Nothing
End If

If we refresh the data in tblNames, you can see that our code has put a DOB value in Sally Lang's record:

Figure 3: The record of Sally Lang (ID 5) now has a DOB value
after her record was edited with our code.

Deleting Records

When it comes to deleting a record, we start by finding the particular record that we want to delete. Again this is achieved using the FindFirst method of the Recordset Object. So suppose we wanted to delete Graham Bell's record, we could do a FindFirst search on his record ID which is 8.

rstNameList.FindFirst "Id = 8"

All we do then is apply the Delete method of the Recordset Object as follows:

rstNameList.Delete

Again, it is a good idea to nest the above line in an If ...Then... Else statement to test that a matching record has been found. As such the full code to delete record 8 is as follows:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

rstNameList.FindFirst "Id=8"
If rstNameList.NoMatch = False Then
rstNameList.Delete
Else
MsgBox "Record Not Found"
End If

If Not rstNameList Is Nothing Then
rstNameList.Close: Set rstNameList = Nothing
End If

The record has now been removed from the table:

Figure 4: Our code has deleted the intended record.

These have all been basic examples to demonstrate how we can access and manipulate data using VBA code. How we use the Recordset object in a real database application depends entirely on what needs doing. I have found the possible uses of the DAO Recordset to be virtually endless. It is an extremely powerful tool to draw upon when needed. As such, I hope to show some applied examples of its possible uses in a future post.