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