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 |
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.
- acDialog causes the form to be opened in Dialog mode,
- 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 |
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.