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.