Version 1.2           Approx 1.8 MB (zipped)                 First Published 3 Feb 2024                 Last Updated 22 Feb 2024


Section Links (this page):
          Introduction
          Download
          Hide Duplicates - Example 1
          Hide Duplicates - Example 2
          How does the feature work?
          More Continuous Forms Examples
          Feedback


Introduction                                                                                                                   Return To Top

This is the seventh article in my series showing how functionality can be added to continuous forms.

Access provides a very useful feature for hiding duplicate values in reports.


To do this, go to Design view, select the required control(s) and set Hide Repeated Values = Yes in the property sheet


Unfortunately, there is no equivalent feature for Access forms. However, the functionality can be added to continuous forms by modifying the form record source.



Download                                                                                                                   Return To Top

The example app includes two examples that can be selected from a startup form.


Both example forms are included as part of my Database Analyzer Pro app. In each case, the form is read only.

Click to download:   HideDuplicateColumnValues_v1.2     ACCDB file     Approx 1.6 MB     (zipped)



Hide Duplicates Example 1 - Object Summary                                                           Return To Top

Duplicate values in the Object Type column are hidden




Hide Duplicates Example 2 - List of VBA Procedures                                               Return To Top

In this case, duplicate values are hidden in the Object Type and Object Names columns.


Although not related to this article, I have included the feature from my Database Analyzer Pro app allowing you to view the VBA code for any procedure.
To do so, double click any object or procedure name.




How Does the Feature Work?                                                                                         Return To Top

The data for the second example in in the table tblProcInfo


This is combined with an aggregate query to determine the first record for each ObjectType & ObjectName.


The query SQL is:

SELECT First(tblProcInfo.ProcID) AS FirstOfProcID, tblProcInfo.ObjType, tblProcInfo.ObjName
FROM tblProcInfo
GROUP BY tblProcInfo.ObjType, tblProcInfo.ObjName;

A new query combines the table data with the aggregate query using a left join. The query design is:


The query SQL is:

SELECT qryFirstProcInfoGroup.ObjType AS ObjectType, qryFirstProcInfoGroup.ObjName AS ObjectName, tblProcInfo.ObjType AS ObjType,
tblProcInfo.ObjName AS ObjName, tblProcInfo.ProcName, tblProcInfo.ProcType, tblProcInfo.ProcScope,
tblProcInfo.ProcCountLines, tblProcInfo.ProcID
FROM tblProcInfo
LEFT JOIN qryFirstProcInfoGroup ON tblProcInfo.ProcID = qryFirstProcInfoGroup.FirstOfProcID
ORDER BY tblProcInfo.ObjType, tblProcInfo.ObjName, tblProcInfo.ProcID;

The query results are as shown below and are used as the record source for the form fsubProcInfo


NOTE:
1.   The form is READ ONLY due to the use of an aggregate query as part of its record source.

2.   There are workarounds if you want to use this approach and edit the data. For example:
      a)   save the aggregate query data to a temp table and use that in the form's record source in place of the aggregate query.
      b)   modify the query to use a domain function such as DMin or DLookup together with IIf values. As this eliminates the aggregate query, part of the form is editable
      c)   use a separate popup form for editng the data
      Each of these methods is discussed in the second part of this article.

The approach is identical in the first form but the record source is more complex involving both a union query and an aggregate query. Union queries are also read only.

The highlighting effect for the first record in each group is done using conditional formatting. All the textbox controls containing data are transparent.
An unbound textbox txtShading arranged behind the form controls is shaded blue when the ObjectType column is not null


The approach used in this example app should be easy to adapt for other applications. I hope you find it useful.

NOTE:
I strongly recommend that you do NOT allow users to sort columns when using this approach as the results may be VERY unpredictable.
To prevent this, set the form property: Records Locks = All Records. This will disable both the right click Sort/Filter context menu and the Ribbon menu items.


You may also want to set the form property Shortcut Menu = No so the context menu isn't diaplayed

The same approach also works for datasheet forms but with the same caveats.
I would again strongly recommend locking down the datasheet form using Records Locks = All Records. and Shortcut Menu = No as in the screenshot below.




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)



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 22 Feb 2024



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