DataWright Information Services

Consulting and Resources for Excel and Access

Many-to-many data entry using a popup form

The idea for this example was inspired by someone who wanted to build cascading combo boxes, with a twist. Although the second combo box was to show items based on the selection from the first, there were times when they needed to be able to select from a full list in the second combo and populate the first combo with the related value; in effect, reverse the flow. My first attempt at answering the question did the job but it wasn't entirely satisfactory. Then it occurred to me that we could use a popup form to make the many-to-many selection, and the direction of flow would be irrelevant.

In Australia, many postcodes map to more than one locality, and many localities have more than one postcode and / or are found in more than one State. This means that data entry of Suburb / State / Postcode can be error-prone. In a classic many-to-many database design, the table structure is set up so that a join table resolves the many-to-many issue and a subform is used to enter the "many" side of the relationship. That is an unnecessary complication for entering address information. This tutorial shows how you can use two combo boxes and a pop-up form to simplify the process and reduce errors.

  • Download the sample file (171,073 bytes)

Setup: The tables

In this example there are two tables; tblClients, which holds some basic client details, and tblPostCode, which holds the postcode data. The two tables are set up as follows --

tblClients

Field Data Type
ClientID AutoNumber (Primary Key)
ClientName Text (255)
Suburb Text (50)
State Text (5)
PostCode Text (5)

tblPostCodes

Field Data Type
Locality Text (50)
State Text (5)
PCode Text (5)

The Clients form

  • Create a new form based on tblClients. Save it as frmClients. Add a Command Button called cmdClose; place it below the existing fields.
  • Change Suburb and PostCode from text boxes into combo boxes (right-click each in turn and Change To > Combo Box)
  • The two combo boxes have very simple RowSource properties. In each case the RowSource is a single field (Locality or PCode from tblPostCodes), sorted ascending. The RowSources are:
Control RowSource
Suburb SELECT DISTINCT tblPostcodes.Locality FROM tblPostcodes ORDER BY tblPostcodes.Locality;
PostCode SELECT DISTINCT tblPostcodes.Pcode FROM tblPostcodes ORDER BY tblPostcodes.Pcode;

The DISTINCT keyword ensures that you will get a list of unique values. There is no need to alter the Column Count, Bound Column or Column Widths properties for either control.

The frmClients form looks like this the finished frmClients form

Code for the Clients form

The frmClients module contains the following:

  • A Declarations section with variables and constants that we use to build the SQL filter code
  • A Click event for cmdClose that closes the form
  • AfterUpdate events for both Suburb and PostCode that build SQL statements and pass those to a custom function, Populate, for processing.
  • The custom function, Populate.

1. The SQL statements in the Declarations section

'SQL for pop-up form
Const sSELECT = "SELECT Locality, State, PCode FROM tblPostcodes "
Const sORDER = "ORDER BY Locality, State, PCode;"
Dim sWHERE As String
Dim sSQL As String

When you look at the layout of a SELECT query with criteria and sorting, there are four elements.

SELECT [one or more fields]
FROM [a named table or query]
WHERE [one field] = [some value]
ORDER BY [the fields that you want to sort by]

The list box on fpopPostCodes will have the same three fields, sorted in the same order. What will change is the WHERE clause that sets the criteria, thus changing which records are displayed. So, running the first two elements together on one line gives the first constant, listed as sSELECT, and taking the fourth element gives the second constant, listed as sORDER. Note the trailing space after sSELECT. The sWHERE valriables will also have trailing spaces, which simplifies joining the elements to make a complete SQL statement.

Because the WHERE clause will change, and so will the final SQL statement, these have been declared as String variables instead of constants.

2. The AfterUpdate code for the two combo boxes

The code for these two combo boxes is essentially the same, so I will only describe the first one. 

Private Sub Postcode_AfterUpdate()
  'Write the WHERE clause for the filter, and assemble the SQL
  sWHERE = "WHERE PCode = '" & Me.Postcode & "' "
  sSQL = sSELECT & sWHERE & sORDER

  'Call function
  Call Populate(sSQL)
End Sub

There are three lines of code in this event.

The first line builds the WHERE clause for the SQL statement, using the selected value in the PostCode combo box. Because PostCode is text, we need to use the combination of single and double quotes to define the string. The space before the last quote creates the trailing space at the end of the WHERE clause

  sWHERE = "WHERE PCode = '" & Me.Postcode & "' "

The second line concatenates the three SQL clauses together in the correct sequence to build a valid SQL statement. Because of the trailing spaces that we introduced in the sSELECT and sWHERE strings, concatenating the strings is a simple process.

  sSQL = sSELECT & sWHERE & sORDER

The third line passes the finished SQL statement to the Populate function, which does the main work of the form.

  Call Populate(sSQL)

3. The Populate function does the heavy lifting

With comments removed the code looks like this:

Private Function Populate(strSQL As String)
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim prm As DAO.Parameter
  Dim rst As DAO.Recordset

  Set dbs = CurrentDb()
  Set qdf = dbs.CreateQueryDef("", strSQL)
  For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
  Next prm
  Set rst = qdf.OpenRecordset

  With rst
    .MoveLast
    If .RecordCount > 1 Then
      DoCmd.OpenForm "fpopPostcode", acNormal, , , , acDialog, strSQL
    Else
      Me.Suburb = !locality
      Me.State = !State
      Me.Postcode = !pcode
    End If
    .Close
  End With

  Set rst = Nothing
  qdf.Close
  Set qdf = Nothing
  Set dbs = Nothing

End Function

The function takes one argument, a SQL string passed to it by either of the two combo boxes. Following a number of declarations, the function then does the following:

  • Create a temporary query, using the incoming SQL statement
  • Because the incoming SQL has a WHERE clause, the criteria (parameters) need to be evaluated before you can open the recordset based on the SQL. Failure to do this results in a run-time error
  • Create and open the recordset
  Set dbs = CurrentDb()
  Set qdf = dbs.CreateQueryDef("", strSQL)
  For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
  Next prm
  Set rst = qdf.OpenRecordset
  • Once the recordset is open, we need to know whether there is more than one record. To do that we move to the last record in the recordset and determine the RecordCount.
  • If the RecordCount is 1, we use those values to complete the data entry; otherwise we open the pop-up form. In the DoCmd.OpenForm line, the last two parameters are crucial.
    • acDialog causes the form to be opened in Dialog mode, meaning that the PopUp and Modal properties are set to Yes. This forces the user to interact with the form, and makes the processing on frmClient wait until the pop-up form is closed
    • strSQL is the SQL statement passed to the function by the combo box. It is now passed to the pop-up form in the OpenArgs; when the form loads, the value of the OpenArgs will be used to set the list box to the desired SQL.
  • Finally, the recordset is closed and the references are cleaned up to prevent memory leaks.
  With rst
    .MoveLast
    If .RecordCount > 1 Then
      DoCmd.OpenForm "fpopPostcode", acNormal, , , , acDialog, strSQL
    Else
      Me.Suburb = !locality
      Me.State = !State
      Me.Postcode = !pcode
    End If
    .Close
  End With

  Set rst = Nothing
  qdf.Close
  Set qdf = Nothing
  Set dbs = Nothing

The Pop-up form

fpopPostCode contains two controls; a Cancel button in case the user called the form by mistake, and a list box whose row source consists of 3 fields. The row source is changed as the form is opened, so the user can make a selection from the appropriate options. Once the user clicks a record, the field values are written to frmClients and fpopPostCodes is closed.

Set the properties for lstChoice as follows:

Property Value
Column Count 3
Column Widths 5cm;2cm;2cm
Width 9.5cm
RowSource SELECT Locality, State, PCode FROM tblPostCodes;
The fpopPostcodes form looks like this the finished pop-up form

Code for the pop-up form

1. The Cancel button is just a one-liner, identical to cmdClose_Click mentioned earlier:

Private Sub cmdCancel_Click()
  DoCmd.Close acForm, Me.Name
End Sub

Using Me.Name makes this code generic. It returns a string whose value is the name of the current form.

2. The form's Load event is used to re-set the row source for the listbox, using the OpenArgs parameter.

Private Sub Form_Load()
  'Me.OpenArgs is the SQL for the multiple records, 
  'passed by the Populate function.
  'The row source for the list box is set to the new SQL, 
  'so that the list box displays only the relevant records.
  Me.lstChoice.RowSource = Me.OpenArgs
End Sub

3. The AfterUpdate event for the list box does two things: write the Suburb, State and PostCode values on the main form, based on the selection made by the user, and close the pop-up form. By writing all three fields, there is no need to check whether the user filled in the Suburb or the PostCode field. 

Private Sub lstChoice_AfterUpdate()
  Dim frm As Form
  'Stuff the list box field values back into the fields for frmClients, 
  'then close this form
  Set frm = Forms("frmClients")

  frm!Suburb = Me.lstChoice
  frm!State = Me.lstChoice.Column(1)
  frm!Postcode = Me.lstChoice.Column(2)

  'Clean up references and close the popup form.
  Set frm = Nothing
  DoCmd.Close acForm, Me.Name
End Sub

By using frm to set an object reference, you can point the output from the pop-up to whatever form you need.

So, there you have it. Using this set-up you can speed up data entry; users can select items from either of two combo boxes (you could have more if required), and the data entry either completes without further intervention or after selecting an item from an appropriately filtered pop-up form. It's pretty simple to set up and you should find it to be a flexible addition to your toolbox.