Hide Duplicates Selectively

[SpaceEd]

Hi

I'm trying to make a report that hides duplicates. I have several categories such as "Function," "Failure" and "Action." They have something similar to a one-to-many relationship in that there are several Actions for each Fail, and several Failsto each Function.

I have the report in block format. It hides the duplicates fine, the problem is the same values disappear although they may be in different groups.

For example, I want to see something like this.

Function A....Fail 1....Action 1
.............................Action 2
.............................Action 3
..................Fail 2....Action 3
.............................Action 4
Function B....Fail 3....Action 5

The Hide Duplicates will hide Action 3 for Fail 2 even though it is not necessarily a duplicate. Is there any way to fix this? Any help would be appreciated. I've been going crazy for more than a week

 

[vbaInet]

In your report create a group for Failure and see if that fixes the problem. I don't think it will but worth a try. I'm guessing you are using the Hide Duplicates property?

I believe you need code to do exactly what you want.

 

[SpaceEd]

Thanks for the response . Yes. I grouped each one of the fields (there are actually more than three fields) with the text in the detail section and the labels in the header. Each group section header is minimized to zero. I experimented with vba code on the input form creating a numbering system using if statements, dmins, and dlookups, but my current code is somewhat long, tedious, and inefficient. Do you have any suggestions or can you point me in the right direction of a more efficient code?

 

[vbaInet]

I can suggest some ways but I don't thing the sample data you provided above is fully representative of what you actually have.

From your sample data, it looks as if Action is never duplicated and if this is the case there's no need of hiding duplicates. Clarify the position here.

What is actually causing the duplicates? A join between a couple of tables?

 

[SpaceEd]

Well it is actually a string of maybe 8 fields. They are all on one table. The reason I didn't use queries is because it was difficult to create a form to append the query that referenced several tables. I can provide a sample database. It is attached.

If you go to "search", then click "15" and "Go to Form", then "View Report" you'll see what I'm talking about. There are some fields missing that shouldn't be in the third column. Do you have any ideas as to a vba code that could work? Setting up a numbering system kind of worked, but there are still a few bugs...

 

[vbaInet]

I don't use Access 2010 Save it As an older version.

Yes, code is not a problem. I just want to see the records to know what kind of code to use (if necessary).

 

[SpaceEd]

hmmmm... I've been trying to save it into Access 2002, but for some reason it removed my report and won't save it. The pdf attached is the report output that Access 2010 provides. the "tester.mdb" file has the tables that the report pulls data from.

 

[SpaceEd]

so as you may see in the report:

for Fun1, FailB, "Effect C" disappears when it shouldn't
for Fun2, FailD, "Effect F" disappears when it shouldn't

 

[vbaInet]

Have a look at the attached.

 

[SpaceEd]

Hi vba,

This is almost the perfect solution! Except when there are several more fields, there the same problem occurs (in report highlighted numbers indicate missing fields when they should appear). Is there just a few morel lines of code to completely solve this issue?

It seems like this is very close to being solved. See the attached database and report (Added some if statements to fix bug if field was blank).

 

[vbaInet]

I would advise that for next time you include ALL the possibilities in your initial post. That is, include and mention ALL the fields that you're dealing with.

What you highlighted doesn't make much sense to me. Care to elaborate?

 

[SpaceEd]

I apologize. That report should show all the levels that are required.

So for Fun 1, Fail A, Effect C, there should be a "Cause" in the next column, and a "Control" in the following column after 6.

Below that between the 3 and the 2 there should be another "Control."

Between Effect F and 4, there should be a "Cause" that is missing.

 

[vbaInet]

Alright, that makes sense.

Well you've got the code that does Cause, so duplicate that code and use it for the other fields. And remember to set the Hide Duplicates for those controls to No.

 

[SpaceEd]

Correct, which is what I did in the attached database. I've been playing around with the order, but it still doesn't seem to work with many fields. Any ideas?

 

[vbaInet]

The code is based on the Failure Mode Header so it's displaying exactly what it was told. It has to be based on a group. You decide what group to use.

Also, remove the IF lines and trap for Null using Nz(). E.g.:

        Me.txtFunction.Visible = ([COLOR=Red]Nz([/COLOR]Me.txtFunction[COLOR=Red], vbNullString)[/COLOR] <> strFunction)
        strFunction = Nz(Me.txtFunction, vbNullString)