Hide Duplicates on a form instead of a report

Posted on 2012-09-05
Medium Priority
Last Modified: 2012-09-05
I need to create a continuous form. One of the fields identifies the asset being considered and so what I would like is to have the first time a new asset appears in the list to have it highlight (different colour, non-blank, whatever) and all the records below it associated with that asset to be a different fromat. I know I can do it easily with a report but this needs to be a form view because the user needs to be able to select a record and I don't think you can detect which record has been selected on a report.

The form's data source is a Union query that knocks out duplicate records automatically. The first component of my union query will always contain the first record in the list to be displayed but if I include an indicator field in that query then the union will not automatically exclude duplicate records because the indicator field in the first query will make the record no longer a duplicate.

I don't think it is going to be possible to do what I am asking but I have to ask in case.
Question by:Rob4077
  • 3
  • 2

Author Comment

ID: 38367281
BTW, even if I can do this on a report, my preference is to use a form because I can more easily, and dynamically change other values on the form like column headings etc
LVL 85
ID: 38367304
The form's data source is a Union query that knocks out duplicate records automatically
If this is true, then how could you possibly hide duplicates (since there would be none)?

It would be best is you could provide some sample data to show us what you mean.

Author Comment

ID: 38367346
I try to explain what I mean but it doesn't always work.

Source1 is a query that selects every asset in the Asset table AND the most recently entered record in the linked Messages table, whether the message has been actioned or not. If there are no records in the linked messages table it still includes the record.  
UNIONed with
Source 2 is a query that selects all the records in the table that have not been actioned.

Thus it is almost inevitable that at least one record in Source 2 also exists in Source 1. By grouping them in a UNION query it gets rid of those duplicate records.

What I need to display is all the records in the resultant output. However it will often happen that there will be more than one record there for each asset. On the form I want to highlight somehow when the next asset listing begins.

Asset       RecordNum
0001        12345
0002        12456
0003        12567

The application is going to mean that I am going to be adding more and more fields in the Queue table as time goes by, hence I want to stick with Queue.* rather than redefining the query every time I add a field. And the user wants to have the data display in datasheet view or one big wide continuous form.

As I say, I don't think what I want to do is going to be easily possible but I am looking for ideas.

The actual union query follows:
SELECT Queue.*, tblAssets.*, tblMessageStatus.MessageStatus
FROM (qryLatestMessageByAsset LEFT JOIN (Queue LEFT JOIN tblMessageStatus ON Queue.FolderId = tblMessageStatus.pkFolderid) ON qryLatestMessageByAsset.MaxOfMessageId = Queue.MessageId) LEFT JOIN tblAssets ON qryLatestMessageByAsset.pkCellNo = tblAssets.pkCellNo
SELECT Queue.*, tblAssets.*, tblMessageStatus.MessageStatus
FROM tblAssets RIGHT JOIN (Queue LEFT JOIN tblMessageStatus ON Queue.FolderId = tblMessageStatus.pkFolderid) ON tblAssets.pkCellNo = Queue.CellNo
WHERE  tblMessageStatus.pkFolderid Between 20 And 21 AND Queue.InAlert=True
ORDER BY Description, pkCellNo, MsgDate DESC;
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 38367416
You'd have to do this with some sort of Temporary table, I'd suspect. You could use your Union query to build that temp table, and then loop through it to remove the Asset duplicates, then present that data to the user.

Author Comment

ID: 38367909
Thanks for confirming. I want to explore an alternative before I take that route. I will raise another question to see if I can somehow modify one of my queries. Thanks for your help.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question