AccessInsider

Cascading List Boxes: Part 1

2012-02-05T21:32:00.002-06:00

This article will demonstrate how to create cascading list boxes between tables with one-to-many relationships. The example provided here has been created by the author using Microsoft Access 2007. This method also modified to work with combo boxes and can easily be adapted for use with many-to-many relationships.

Our example will include four (4) list boxes based on geographic location: Continent, Country, State, City.

We will begin by creating a table for each.

Tables

tblContinents - ContinentID, ContinentName

tblCountries - CountryID, CountryName, ContinentID

tblStates - StateID, StateName, CountryID

tblCities - CityID, CityName, StateID

Relationships

Next, we will establish single-to-many relationships between the tables.

Each continent may be related to many countries, each country may be related to many states, each state may be related to many cities.

Form Controls

Now that the table relationships are in place, it's time to add the list box controls to a form.

We'll add one list box for each of our tables; lstContinent, lstCountry, lstState, lstCity.

Tip: The column count property for each should be 2. Column width should be 0";2" in order to hide the bound ID column and show the name column.

The row source of each child list box will be dependent on the parent list box(es). While we could set the row sources of our list boxes in VBA, this example will accomplish the task by setting a query as the rowsource.

lstContinent.RowSource

The row source for the continent list box is pretty straight forward as it is the top level of our cascade and isn't dependent on any of the other list boxes.

SELECT tblContinents.ContinentID, tblContinents.ContinentName
FROM tblContinents
ORDER BY tblContinents.ContinentName;

lstCountry.RowSource

The country list box row source is dependent on the continent list box control.

SELECT tblCountries.CountryID, tblCountries.CountryName, tblCountries.ContinentID
FROM tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID
WHERE ((tblCountries.ContinentID)=[Forms]![frmLocation]![lstContinent]);

For the remaining levels of our cascade, we must not only reference the list box directly above it, but all parent list boxes back to the top level. This will be explained when we configure the list boxes to be updated in VBA.

lstState.RowSource

SELECT tblStates.StateID, tblStates.StateName, tblStates.CountryID, tblContinents.ContinentID
FROM (tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID) INNER JOIN tblStates ON tblCountries.CountryID = tblStates.CountryID
WHERE (((tblStates.CountryID)=[Forms]![frmLocation]![lstCountry]) AND ((tblContinents.ContinentID)=[Forms]![frmLocation]![lstContinent]))
ORDER BY tblStates.StateName;

lstCity.RowSource

SELECT tblCities.CityID, tblCities.CityName, tblCities.StateID, tblCountries.CountryID, tblContinents.ContinentID
FROM ((tblContinents INNER JOIN tblCountries ON tblContinents.ContinentID = tblCountries.ContinentID) INNER JOIN tblStates ON tblCountries.CountryID = tblStates.CountryID) INNER JOIN tblCities ON tblStates.StateID = tblCities.StateID
WHERE (((tblCities.StateID)=[Forms]![frmLocation]![lstState]) AND ((tblCountries.CountryID)=[Forms]![frmLocation]![lstCountry]) AND ((tblContinents.ContinentID)=[Forms]![frmLocation]![lstContinent]))
ORDER BY tblCities.CityName;

Below is the lstCity.RowSource query for those of you more comfortable building queries in design view.

Updating the Row Sources

Finally, we'll add VBA code to our form to requery our list boxes to update the row source(s) based on the user's selection.

Private Sub lstContinent_AfterUpdate()
'   requery child list boxes.
    Me.lstCountry.Requery
    Me.lstState.Requery
    Me.lstCity.Requery
End Sub

Private Sub lstCountry_AfterUpdate()
'   requery child list boxes.
    Me.lstState.Requery
    Me.lstCity.Requery
End Sub

Private Sub lstState_AfterUpdate()
'   requery child list boxes.
    Me.lstCity.Requery
End Sub

As you can see, the continent list box requeries all three child list boxes. This is to ensure that all of the list boxes are updated when a new continent is selected. Each level requeries all of it's child controls. By including all of the parent controls as criteria in in our row source queries (set up earlier), we ensured that only related child records are displayed when the selected parent records are updated.

This article will be updated to include a functional example for download in the near future.

Custom User Permissions: Part 1

2012-01-23T20:46:00.000-06:00

When developing a full scale database application for Microsoft Access, one of the hurdles you're bound to run into sooner or later is the need for user permissions. Managing who can see which forms, edit which records, run which reports, etc can seem like a daunting task for developers. This series is focused on provided the knowledge and resources necessary in preparation for developing a user permissions management solution in your own database applications.

Introduction

The "Creating Login Security using Access VBA" article in the UtterAccess Wiki serves as a good introduction to custom user permissions and for this series.

The UA wiki article covers table design and hard code methods of managing form access, record add/edit/delete permissions, and record filtering in a single-to-many/hierarchical permissions system. I recommend giving it a look as it lays foundations for a few of the methods discussed in this series.

Part 1

In the first part of our series on custom user permissions, we will begin by designing the user and permissions tables and relationships. The example in this series will be designed to allow users to be members of multiple permissions groups giving administrators increased permissions management flexibility.

First, create tblUsers as shown below.

Next, create tblPermissions.

Now, create tblUserPermissions to serve as a junction table between tblUsers and tblPermissions. This will allow us to establish a many-to-many relationship.

Notice that both the UserID and PermissionID are set as the primary key for the junction table.

Lastly, establish the relationships between the tables.

Part 1 Summary

In this post we created tables for users, permissions groups, and a junction table to allow each user to relate to multiple permissions groups and each permissions group to relate to multiple users.

Function to Close All Forms

2012-01-17T23:25:00.001-06:00

The CloseAllForms Function article posted in the UA Wiki is a handy tool to close all active forms opened with the DoCmd.Open method.

The example code provided in the article includes optional arguments for excluding selected forms from being closed which is an added flexibility bonus.

This function can be modified to close only the forms named in the arguments instead by altering the following line of code found in the example:

If InStr(1, strExcluded, cDelimiter & Forms(intX).Name & cDelimiter, vbTextCompare) = 0 Then

The above If statement checks for and excludes any forms named as arguments when calling the form. To close only the forms named as arguments instead change the = 0 to <>0 as follows:

If InStr(1, strExcluded, cDelimiter & Forms(intX).Name & cDelimiter, vbTextCompare) <> 0 Then

See the AccessInsider blog for additional tips or visit the examples page for free functional example downloads.

Lebans: An excellent developer's resource ...

2012-01-15T15:54:00.000-06:00

Looking for functional examples of advanced access development techniques?

While he is no longer active in the Microsoft Access community, Stephen Lebans left a treasure trove of numerous tips and examples for aspiring Access database developers.

For anyone interested in access development that hasn't been to his website, you should give it a look.

Referencing Access Objects in a Query or Control

2012-01-13T22:53:00.000-06:00

Referencing data in objects (tables, forms, queries, reports) is a basic skill in the Microsoft Access developer's toolkit, but what if you need to reference the objects themselves as part of a custom administrative interface (for example)?

Answer:

By referencing the [Name] field in the hidden Access system table "MSysObjects" and using the [Type] field as a filter criteria you can use Access objects such as tables, forms, queries, reports, and even modules as values in a queries, form controls, reports, etc. This can be especially useful when developing a full scale custom MS Access database application.

Object Type	Type ID
Tables	1
Forms	-32768
Queries	5
Reports	-32764
Modules	-32761

Below is a sample query for listing Access form objects.

SELECT MSysObjects.Name

FROM MSysObjects

WHERE (((Left$([Name],1))<>"~") AND ((MSysObjects.Type)=-32768))

ORDER BY MSysObjects.Name;

This can easily be modified for each object type by change the type criteria in the query.

A downloadable example, including queries for each object type and a demonstration of populating a list box with Access system objects, can be found on the AccessInsider Examples Page.

Guide to Closing Forms with VBA

2012-01-08T00:06:00.000-06:00

Because it may seem like a mundane task to many, it can be easy to overlook a few opportunities for improving our applications' overall performance with something as simple as closing a form. AccessInsider explores one such opportunity by providing an example for utilizing a public function call to close forms in order to reduce the amount of code required to load each time a form is launched.

While any actual performance benefit will likely be undetectable using this particular example, it still provides an adequate demonstration of how a developer can streamline an application's code for repetitive procedures to optimize performance.

The common way closing a Microsoft Access for using VBA is by utilizing the DoCmd.Close method.

DoCmd.Close(ObjectType, ObjectName, Save)

Parameters

Name	Required/Optional	Data Type	Description
ObjectType	Optional	AcObjectType	A AcObjectType constant that represents the type of object to close.
ObjectName	Optional	Variant	A string expression that's the valid name of an object of the type selected by the objecttype argument.
Save	Optional	AcCloseSave	A AcCloseSave constant tha specifies whether or not to save changes to the object. The default value isacSavePrompt.

Table Source: MSDN Office 2007: Access Developer's Reference

Example 1:

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Result:

Closes the active window.

Example 2:

Private Sub cmdClose_Click()
DoCmd.Close , , acSaveYes
End Sub

Result:

Closes and saves active window object.

Note: Notice that even though we didn't include the object type or object name parameters, we still had to include commas for those parameters when using the save parameter. This can be avoided by identifying the parameters used as demonstrated below:

Private Sub cmdClose_Click()

DoCmd.Close Save:=acSaveYes

End Sub

Tip: Naming the parameters in your code, as opposed to just using commas, will also make it more readable for other users/developers.

Example 3: Close a specified form.

Private Sub cmdCloseFunction_Click()

DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name

End Sub

Tip: Use quotation marks ("") around the form name for the ObjectName parameter when closing other forms.

Example 4: Closing a specified form using a public function call.

Module: modPublicFunctions

Public Function CloseForm(FormName As String)

'// Author: Jonathon Carrell
'// Purpose: Public function to close forms.
'// History: 7 Jan 2012 Function created.

'// Close form.
DoCmd.Close ObjectType:=acForm, ObjectName:=FormName

End Function

Form: frmCloseExample

Private Sub cmdCloseFunction_Click()

'// Call CloseForm function from modPublicFunctions to close this form.
CloseForm Me.Name

End Sub

A functional example database can be found at this link: DoCmd.Close Method, or on theAccessInsider Examples Page.

Comments have been included with the example code as to provide the readers with a better understanding of the methods used.

Open Applications using Shell Method

2012-01-07T01:22:00.000-06:00

In this post, we will go over one of the common methods for launching external programs from within Microsoft Access by utilizing the shell method in VBA. A couple of other features also demonstrated in the example found at the end of this post are setting the external application as a child window within access and giving the shelled program a custom window caption by using API calls.

To launch an application from within Access, use the following code in a function or sub:

Shell ("C:\YourProgramPath.exe")

A functional example database can be found at this link: Shell Method, or on the AccessInsider Examples Page.

Comments have been included with the example code as to provide the readers with a better understanding of the methods used.

Concatenating Fields in an Access Query

2012-01-05T23:37:00.000-06:00

For those of you that have wrestled with concatenating fields and the use of the & and + symbols, this post will demonstrate methods of building a SQL query to combine multiple fields.

We will use one table for this example as shown below.

Let's start with a simple SQL query to combine the first and last names as PersonName.

SELECT tblPerson.PersonID, [FName] & [LName] AS PersonName FROM tblPerson;

Here we have selected the tblPerson primary key and the combined first and last name. Below is the resulting record set.

While we have successfully combined the first and last name, there is no separation between them. We will add a space between them by altering our SQL as follows:

SELECT tblPerson.PersonID, [FName] & " " & [LName] AS PersonName
FROM tblPerson;

This will return the person name as "John Doe", but what if your project needs to return the person name as Last, First MI?

To accomplish this we could write the query below.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " " & [MInitial] AS PersonName
FROM tblPerson;

The drawback to this method is that even if no middle initial exists for a record, an unnecessary space is placed after the first name. This may not seem all that important in this example, but we will explore this issue further momentarily.

A better method would be as follows (assuming the first and last names are required fields):

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " " + [MInitial] AS PersonName
FROM tblPerson
ORDER BY tblPerson.LName;

By changing the & to a +, the space after the first name becomes dependent on there being a middle initial present for the record. It may have caught your eye that an additional line has been added to the above query; this is to order the records by last name.

For those readers that are more comfortable with building access queries in "Design View", the below image is provided as reference.

Now let's look at another example of how the + can be used in concatenated fields. Below we have added a PhoneNumbers concatenated field to our query.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " "+[MInitial] AS PersonName, "Home: " & [PhoneHome] & "/" & "Cell: " & [PhoneCell] & "/" & "Business: " & [PhoneBusiness] AS PhoneNumbers FROM tblPerson ORDER BY tblPerson.LName;

Here is the result:

You may notice that Susie's record returned unnecessary "/" separators and descriptions for phone numbers she doesn't have. We will improve the query by building in some dependencies.

SELECT tblPerson.PersonID, [LName] & ", " & [FName] & " "+[MInitial] AS PersonName, "Home: " + [PhoneHome] & "/" + "Cell: " + [PhoneCell] & "/" + "Business: " + [PhoneBusiness] AS PhoneNumbers FROM tblPerson ORDER BY tblPerson.LName;

Below is the final result.

Visit AccessInsider for other Microsoft Access tutorials and examples.

Setting Parent/Child Windows

2012-01-05T01:26:00.000-06:00

Here is a simple method for forcing a form window to open inside another form's window when the parent form is loaded. This can be useful for access applications utilizing a custom user interface.

Note: The below code and provided example use two(2) forms (frmParentWindow and frmChildWindow). You will need to reference your own form names when utilizing the provided code in your projects.

modSetParent (Module Name)

Option Compare Database

Public Const GW_HWNDNEXT = 2

Public Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, _

ByVal hWndNewParent As Long) As Long

To open a form window as a child of of the parent form, place the following code in the On Open event of the child form:

Dim frm As Form

Set frm = Form_frmChildWindow

'// If parent form is open ...

If CurrentProject.AllForms("frmParentWindow").IsLoaded Then

'// Set from as child of parent form window (call SetParent function from modSetParent).

hWndChild = frm.hWnd

Call SetParent(hWndChild, Forms!frmParentWindow.hWnd)

End If

frm.Visible = True

A functional example database can be found at this link: Setting Parent/Child Windows, or on the AccessInsider Examples Page.

Comments have been included with the example code as to provide the readers with a better understanding of the methods used.

Managing Multiple Form Instances

2012-01-04T23:01:00.000-06:00

Need: Multiple Form Instances

Have you ever needed to create/compare/edit multiple records at the same time? If so, you've probably noticed that the default form behavior in access is to allow one instance of each form to be open at any given time. This limits the user to working with one record at a time in "Single Form" view. The good news is that there are a few different ways of adding and managing multiple instances of a form and this article will cover the method most commonly utilized for my projects.

Solution: Collection Method

This example will use two forms (frmMain and frmPersonsDetail) and three(3) modules outlined below.

modInstanceCollection

This module contains functions necessary for managing instances of the person form.

Function OpenNewRecordFormInstance() - Create "New Record" form instance.
Function OpenFormInstance(Optional lngID As Long) - Create form instance.
Function CloseInstanceCollection() - Close all instances in form collection
Function CloseInstanceHwnd(lngHwnd As Long) As Boolean - Close specified instance based on form's window handle.
Function CloseInstanceID(lngID As Long) As Boolean - Alternative method to close specified instance based on record's ID.

modSetParent

This module is used to set each new instance of the person form as a child of the main form. The purpose of demonstrating this functionality is to prevent the person forms from disappearing behind the main form when the lose focus. Utilizing this method can be especially beneficial for applications using a form as the basis of a custom user interface.

modInstancePosition

This module contains functions for managing the opening position of form instances. This is used to prevent each concurrent form instance from being opened directly on top of to previous instance.

A functional example database can be found at this link: Multi-Instance Form Management, or on the AccessInsider Examples Page.

Comments have been included with the example code as to provide the readers with a better understanding of the methods used.