Version 1.5           Approx 3.5 MB (zipped)                 First Published 22 Feb 2024                 Last Updated 28 Feb 2024


Section Links (this page):
          Introduction
          Download
          The Example App
          Using Domain Functions
          Editing Using A Separate Form
          Summary and Additional Uses
          More Continuous Forms Examples
          Further Reading
          Feedback


Introduction                                                                                                                   Return To Top

This is a follow up to my recent article: Hide Duplicate Column Values in Continuous Forms

In that article, I showed how you could hide duplicate values in specified columns of a continuous form. For example:


To do so, the form record source is modified to use the original table or query data combined with an aggregate query using a left join.
The aggregate query is used to determine the first record for the columns whose duplicates you want to hide.

Although this method works well, the use of an aggregate query makes the form record source read only which can limit its usefulness.

There are several ways of making the data editable. For example:

a)   Create a make table query (or an append query) from the aggregate/left join query. Use the new table for the form record source.

      Pros:
      The form record source is directly editable. No need for a separate form for editing

      Cons:
      As the table needs to be recreated or repopulated each time the form is used, this will cause significant database bloat
      The data will need to be saved back to the original tables when the form is closed
      In a multi-user environment, there is a significant risk of different users overwriting the same data

b)   Update a temp table in a separate 'side-end' database from the aggregate/left join query. Use the new table for the form record source.
      This is similar to method a) but has several improvements over that approach.

      See Creating and using Temporary Tables in Microsoft Access by former MVP, Dale Fye, which discusses this approach in more detail.

      Pros:
      The form record source is directly editable. No need for a separate form for editing
      As the table is in a separate 'temp' database, this will not cause any bloat in the front-end or back-end databases

      Cons:
      The data will still need to be saved back to the original tables when the form is closed
      As before, in a multi-user environment, there is a significant risk of different users overwriting the same data

c)   Use domain functions e.g. DLookup / DMin in place of the fields from the aggregate query which can now be removed

      Pros:
      The form record source is directly editable. No need for a separate form for editing
      No database bloat

      Cons:
      Queries can get complex to write if there are many fields with duplicates
      Using domain functions will be very slow, particularly in a large recordset and with several fields

d)   Use a separate form with an editable record source

      Pros:
      Fast even with large recordsets
      Minimal code required. No database bloat

      Cons:
      Needs a separate form for editing data

Overall method a) has too many disadvantages and I do NOT recommend it.
Method b) is better but I would still avoid it in a multi-user environment.

The example app supplied with this article includes two more examples of continuous forms with hidden duplicates.
For each dataset, forms are supplied using both methods c) and d) for making the data in selected columns editable.



Download                                                                                                                        Return To Top

Click to download:       Editable Read Only Forms_v1.5      ACCDB file       Approx 3.7 MB (zipped)



The Example App                                                                                                           Return To Top

The app opens to a startup form with buttons to open each of the example forms.


Each example uses fictitious data for all students in a secondary school.


Using Domain Functions                                                                                                 Return To Top

The first example lists students together with any known medical conditions.

A standard report set to Hide Duplicates shows unwanted side effects with only partial data showing for some students:


This issue can be fixed by changing the record source of the report. I will explain how to do this in a separate article.
The problem doesn't occur in the form as the record source is used to control what is hidden.


The first 7 columns of student data have been set as read only. However, the medical conditions field is directly editable on the form.


The record source for this form is the query qryDMinStudentMedicalConditions. The query SQL is:

SELECT qryMedicalConditions.ConditionID,
      DMin("ConditionID","qryMedicalConditions","PupilID = '" & [qryMedicalConditions].[PupilID] & "'") AS FirstConditionID,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[PupilID],"") AS PupilID,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Surname],"") AS LastName,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Forename],"") AS FirstName,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[Gender],"") AS Gender,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[DateOfBirth],"") AS DateOfBirth,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[YearGroup],"") AS YearGroup,
      IIf([ConditionID]=[FirstConditionID],[qryMedicalConditions].[TutorGroup],"") AS TutorGroup,
      qryMedicalConditions.Description
FROM qryMedicalConditions
ORDER BY qryMedicalConditions.Surname, qryMedicalConditions.Forename, qryMedicalConditions.Description;

The query results are as follows


The DMin function is used to get the FirstConditionID calculated field for each student.

It is then referenced in each of the 7 IIf statements to show the field value where the ConditionID field equals the FirstConditionID.
Where the values are different, the field value is replaced with an empty string. All 7 derived fields based on IIf functions remain READ ONLY

However, as this query is based on an editable query qryMedicalConditions, the Description field (AKA Medical Conditions) is also editable.
This query is used as the form record source in which only the Description (Medical Condition) field is editable

The form has just over 660 records and the effects of using a domain function are not too detrimental in this case.
There should be no noticeable delay when running the query or opening the form.

The second example lists students together with the contact information for each contact in priority order.

Once again, a standard report set to Hide Duplicates shows unwanted side effects with only partial data showing for some students:


This issue can be fixed by changing the record source of the report. I will also explain how to fix this in a separate article.
The problem doesn't occur in the Student Contacts form as the record source is used to control what is hidden


The first 7 columns of student data are set as read only. All contact info fields are directly editable on the form


The record source for this form is the query qryDLookupStudentContacts . The query SQL is:

SELECT qryStudentContacts.ContactID,
      DLookup("ContactID","qryStudentContacts","PupilID = '" & [qryStudentContacts].[PupilID] & "' AND Priority=1") AS FirstContactID,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[PupilID],"") AS PupilID,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[Surname],"") AS LastName,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[Forename],"") AS FirstName,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[Gender],"") AS Gender,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[DateOfBirth],"") AS DateOfBirth,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[YearGroup],"") AS YearGroup,
      IIf([ContactID]=[FirstContactID],[qryStudentContacts].[TutorGroup],"") AS TutorGroup,
      qryStudentContacts.Priority, qryStudentContacts.Relationship, qryStudentContacts.TheContactsSalutation AS ContactName,
      qryStudentContacts.AddressLine, qryStudentContacts.MainTelephone, qryStudentContacts.HomeEmail
FROM qryStudentContacts
ORDER BY qryStudentContacts.Surname, qryStudentContacts.Forename, qryStudentContacts.Priority;

This is very similar to the previous query. DLookup is used instead of DMin to ensure all fields for the ContactID with Priority = 1 are fully displayed.
The query results are as follows with all the IIf fields READ ONLY and the contact info fields editable:


This query is used as the form record source in which all the contact info fields are editable

However, the effects of using a domain function are far more pronounced for this example as it has over 4600 records.
In this case, there is a noticeable delay when running the query or opening the form

This approach is really only suitable for small datasets with only a few hundred records
For a large dataset with many thousands or millions of records, the delay will become very significant and make this approach unusable



Editing Using A Separate Form                                                                                   Return To Top

My preferred approach is to use the original query with an aggregate query joined to the main query and do all editing on a separate form.

The first example listing students together with any known medical conditions has the following query as its record source:


The query SQL is:

SELECT qryMedicalConditions.ConditionID, qryFirstStudentMedicalConditions.PupilID, qryFirstStudentMedicalConditions.Surname AS LastName,
      qryFirstStudentMedicalConditions.Forename AS FirstName, qryFirstStudentMedicalConditions.Gender,
      qryFirstStudentMedicalConditions.DateOfBirth, qryFirstStudentMedicalConditions.YearGroup,
      qryFirstStudentMedicalConditions.TutorGroup, qryMedicalConditions.Description
FROM qryMedicalConditions LEFT JOIN qryFirstStudentMedicalConditions
      ON qryMedicalConditions.ConditionID = qryFirstStudentMedicalConditions.FirstOfConditionID
ORDER BY qryMedicalConditions.Surname, qryMedicalConditions.Forename, qryMedicalConditions.Description;

This produces exactly the same results as before but, in this case, all fields in the query output are read only due to the use of an aggregate query

This query is used as the form record source making the entire form read only.


Double click on any medical condition to edit it. A new form opens using the original editable queryqryMedicalConditions as its record source.


In this form, the student data in the first seven fields has been locked. Only the medical condition field can be edited
NOTE: The form could easily be modified to allow editing of all fields. Doing so, would require the aggregate query to be recalculated.

Click the Save and Close button to return to the same record on the main form with the edited field updated.


The code to reopen the main form to the same record is very simple. The cmdClose (Save and Close) button has this code:

Private Sub cmdClose_Click()

      'save the data
      If Me.Dirty Then Me.Dirty = False

      'reopen main form with open args based on hidden primary key value
      DoCmd.OpenForm "frmStudentMedicalConditions", , , , , , Me.ConditionID

      DoCmd.Close acForm, Me.Name
End Sub

The Form_Load event code in the main form is:

Private Sub Form_Load()

     'check for an open args value
      If Not IsNull(OpenArgs) Then
            'find the first record with that ConditionID value
            Me.RecordsetClone.FindFirst "ConditionID = " & Val(OpenArgs)
            'move to that record
            Me.Bookmark = Me.RecordsetClone.Bookmark
      End If

End Sub

The second example works in exactly the same way. It again shows a list of students together with information about the contacts listed in priority order.


The form record source uses the query qryGroupedStudentContacts. The query SQL is:

SELECT qryStudentContacts.ContactID, qryFirstContactStudentGroup.PupilID, qryFirstContactStudentGroup.Surname AS LastName,
      qryFirstContactStudentGroup.Forename AS FirstName, qryFirstContactStudentGroup.Gender,
      qryFirstContactStudentGroup.DateOfBirth, qryFirstContactStudentGroup.YearGroup, qryFirstContactStudentGroup.TutorGroup,
      qryStudentContacts.Priority, qryStudentContacts.Relationship, qryStudentContacts.TheContactsSalutation AS ContactName,
      qryStudentContacts.AddressLine, qryStudentContacts.MainTelephone, qryStudentContacts.HomeEmail
FROM qryFirstContactStudentGroup RIGHT JOIN qryStudentContacts
      ON qryFirstContactStudentGroup.FirstOfContactID = qryStudentContacts.ContactID
ORDER BY qryStudentContacts.Surname, qryStudentContacts.Forename, qryStudentContacts.Priority;

In this example, all 6 contact info fields can be edited using another form. Click on any of the contact fields to open a new form


In this case, an address has been added and the phone number edited.
Click the Save and Close button to return to the same record on the main form with the edited fields updated.




Summary and Additional Uses                                                                                    Return To Top

Both of the above approaches have advantages and disadvantages. For most situations, I prefer the second approach.

This can also be used for more complex queries involving multiple tables provided the base query before aggregation is editable.

The second method is also useful for other types of read only forms. For example, calendar event forms based on crosstab queries.

The school calendar data can be entered in a standard editable form where each event is displayed as a separate record.


However, when planning the calendar vents for the academic year, it is easier to visualise the data when it is displayed in a different format using a crosstab query.
For example, this form shows the events for a calendar month


As this uses a crosstab query (together with some complex layout formatting), the form is read only.
To edit or delete events, clicking on the event displays a popup form for editing.


Similarly, clicking on a blank space on the calendar (or the Add New Event button) opens a popup form to create a new event.

The form is also available as a week-to-view or day-to-view format with events editable in exactly the same way. For example:


Editing read only forms using a popup form with an editable record source is usually the easiest way for end users to manage this type of data.

In addition to crosstab and aggregate data, this approach can also be useful for other kinds of read only forms
For example, when working with data displayed in SELECT DISTINCT queries (Unique Values = Yes)

The above calendar examples are taken from my School Data Analyser application.
For further information, please email me using the link on the feedback form at the end of this article.



More Continuous Form Examples                                                                                Return To Top

The following articles provide further examples of functionality added to continuous forms:

      •   Highlight Current Record
      •   Highlight Selected Control or Column
      •   Highlight Filtered Columns
      •   Add Multiselect Filter   (2 pages)
      •   Paint Me Transparent       (Selectively Hide Controls)
      •   Sort columns
      •   Hide & Restore Selected Columns   (2 pages)
      •   Freeze columns   (2 pages)
      •   Move and Resize Columns   (2 pages)



Further Reading                                                                                                                        Return To Top

After I published the first article in this series on 3 February, one of my fellow MVPs, Mike Wolfe was inspired to create his own series of articles on the same topic:
Hiding Duplicate Values in Microsoft Access Forms and Reports

Although Mike inevitably covers similar ground to my two articles, it is well worth reading his approach for a different perspective on the topic

Feedback                                                                                                                        Return To Top

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 28 Feb 2024



Return to Example Databases Page Page 2 of 2 1 2 Return To Top