Visual Basic Help

[ryan_kos]

I am trying to run a report that will highlight the duplicates in my table. I have already tried a find duplicates query and that is not what I want. I need somebody to tell me how to use code todo this procedure:

look at 1st item
look at next item
if they are the same highlight them both

The table is already sorted so that if there are duplicates, then they will be one after the other. Thanks for the help.

 

[Fornatian]

The easiest way to do this is to output the recordset to excel. Then in row 3 of a new column put this:

=If(B3=B2,"X","")

This will show where the records are duplicated,assuming that column B holds the search item. Then run edit-Paste-Special-Values to convert this column into real values, then run a sort on the column to sort the wheat from the chaff.

If this isn't an appropriate solution, please come back with a more detailed response

 

[ryan_kos]

For this project, I inport an excel file into access. THen I sort run a report on the inported data. This report sorts the data according to sub headings and is supposed to highlight the duplicate fields in each sub record.eg.
Serial # 123
unit 14
unit 14
the 2 14's should be highlighted. Basically all I need is to add some code that will look at the 1st unit and then compare it to the 2nd unit.

 

[RedSkies]

Here's one way to handle it with a minimal amount of code. It's probably not the most elegantly conceived approach, but it works.

-Use the Find Duplicates query wizard to build a query.

-In design view, change this query into a Make Table query. Add a new column to the query and type in the expression 'Flag: 1'

-Build another query, sorted on the field containing the potential duplicates, that includes all the fields you want to report on from your Original table in addition to the Flag field from the Duplicates table created by the query above. LEFT JOIN the Original table to the Duplicates table on the primary keys. (It will probably be Option 2 in the Join type dialog box, that is, include all records from your Original table and only those from the Duplicates table where the primary keys are equal.)Base your report on this query.

-Include the following code behind your report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Flag = 1 Then
' if this record is included in the duplicates table
Me.Detail.BackColor = vbYellow
' or whatever highlight color you want
Else
Me.Detail.BackColor = vbWhite
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
' shuts off warning messages when table is created
'each time query is run
With Application
.SetOption "Confirm Record Changes", False
.SetOption "Confirm Document Deletions", False
.SetOption "Confirm Action Queries", False
End With

DoCmd.OpenQuery "NameOfYourMakeTableQuery"

'turns warning messages back on
With Application
.SetOption "Confirm Record Changes", True
.SetOption "Confirm Document Deletions", True
.SetOption "Confirm Action Queries", True
End With
End Sub


Note: on the report, you'll probably want to set the Flag control's visible property to 'No'.

Hope that makes sense.

 

[ryan_kos]

Thanks for replying RedSkies, its working great so far! I didn't know you could base anything off of an action query.

 

[RedSkies]

Well, you're really not. You're basing it off a table created by an action query instead. But the table is recreated each time the report is run.

Glad to hear it works though.

 

[ryan_kos]

Sorry to keep bugging you RedSkies, but I have a question. I've got both the queries to work now, but when I go to do the report, there are no fields for me to select. Am I doing something wrong? Thanks again, you've been a great help.

 

[RedSkies]

Sorry, not sure I understand what you mean. If you're using the second query as the record source for your report, the fiels should be there. Might be quickest to email me a sample of your database (i.e. don't include all the records, just enough for me to test with.) My email address is in my profile.