UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount In Detailpaint Function, Access 2013    
 
   
SomekindaVB
post Nov 26 2017, 10:49 PM
Post#1



Posts: 160
Joined: 15-December 16



Hi all,

I have a need of performing a DCount inside the Details_Paint function. This is used to display duplicates. It works well, however, there are issue with this.

CODE
MyCount = DCount("[Customer Name] AND [Summary] AND [MyDate]", Me.Recordset.Name, strCriteria)


Where the criteria is the search string for each of the three fields.

two problems here.
1. because of my network it's very slow
2. The paint function seems to run through several times.

Is there a faster method than DCount to get the duplicate count for each record?

Cheers
Go to the top of the page
 
theDBguy
post Nov 26 2017, 10:52 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,229
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Is this for a Report? If so, you might try grouping, if possible.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
SomekindaVB
post Nov 26 2017, 11:16 PM
Post#3



Posts: 160
Joined: 15-December 16



No. it's not for a report. It's to display duplicate records on a subform using the paint function
Go to the top of the page
 
SomekindaVB
post Nov 27 2017, 05:18 PM
Post#4



Posts: 160
Joined: 15-December 16



I was using a textfield with conditional formatting. This worked ok, but it was still very slow, and I was looking for a faster way.

So then I tried using paint. Initially this worked just fine - albeit very slowly. then It ceased to work and I don't know why.

I have an unbound checkbox on the main form that triggers the paint event, but the paint event is extraordinarily lazy. Its so horribly slow. Surely there has to be a faster way.

CODE
Private Sub Detail_Paint()
Dim DuplicateCount As Integer, fnFormat As Integer
Dim strCriteria As String
Dim strClientName As String, StrSummaryCombined As String, StrTeamMember As String
Dim dDueDate As Date
Dim StrSQL As String
Dim rsWorkItems As DAO.Recordset

    strCustomerName = Me.TxtCustomertName
    StrSummaryCombined = Nz(Me.TxtDMSEmailSummary, vbNullString)
    StrTeamMember = Me.[Team Member]
    dDueDate = Me.[Due Date]
    On Error GoTo QuitSub
    
    '
    'Paint back to white
    If Not Me.Parent.Form.ChkHighlightDuplicates.Value = -1 Then
        Me.Detail.BackColor = RGB(255, 255, 255)
        Me.Detail.AlternateBackColor = RGB(255, 255, 255)
    Else
        'Pupolate variables
        strClientName = Replace(strClientName, "'", "''") ' remove the single quotes
        strCriteria = "[CustomerName] =" & Chr$(39) & strCustomerName & Chr$(39) & " AND [SummaryCombined]='" & StrSummaryCombined & "' AND [Due Date] = #" & dDueDate & "# AND [Team Member]= '" & StrTeamMember & "'"
        'Count Duplicates
        
        'method 1
        DuplicateCount = DCount("*", Me.Recordset.Name, strCriteria) 'Original
        'method 2
        Set rsWorkItemsForPaint = Forms![FrmMyFormt].NavigationSubform.Form.Recordset
        rsWorkItemsForPaint.Filter = strCriteria ' Filter the criteria
        Set rsWorkItems = rsWorkItemsForPaint.OpenRecordset ' Pass the filtered recordset to the new recordset
        If Not rsWorkItems.RecordCount = 0 Then
            rsWorkItems.MoveLast: rsWorkItems.MoveFirst
            DuplicateCount = rsWorkItems.RecordCount ' Count
        End If
        
        'Paint the duplicates
        Debug.Print DuplicateCount
        Select Case DuplicateCount
            Case Is <= 1
                Me.Detail.BackColor = RGB(255, 255, 255)
                Me.Detail.AlternateBackColor = RGB(255, 255, 255)
                DoEvents
            Case Is > 1
                Me.Detail.BackColor = RGB(255, 205, 75) ' Orange
                Me.Detail.AlternateBackColor = RGB(255, 205, 75)
                
                DoEvents
        End Select
    End If
QuitSub:


End Sub
Go to the top of the page
 
PhilS
post Nov 28 2017, 05:42 AM
Post#5



Posts: 404
Joined: 26-May 15
From: The middle of Germany


DCount is notoriously slow. A custom function using a stored query and a recordset will probably be faster.

But that is not your core problem here. You need to get the number of executions of that query way down.
The Paint-Event is run countless of times. You should not do anything in there that takes more than a few milliseconds.

Can't you incorporate the duplicate check into the main query of the form? This should be possible using a sub-select inside the query, but it might make the whole query read-only.

If that is not an option, think about caching the results of the duplicate check. Just run it once in the Load-/Open-Event of the form and store the results in an array (or dictionary) containing the key of the record and the result of the duplicate check.
In the paint event you would then just access that array instead of running a query against the db.

--------------------
Go to the top of the page
 
SomekindaVB
post Nov 28 2017, 05:10 PM
Post#6



Posts: 160
Joined: 15-December 16



Ok thanks for the help. I had an idea that I could load the form with the duplicate count as the recordset - for example, something like this

CODE
SELECT FieldName1, FieldName2, Fieldname3
COUNT(*) count
FROM TableName
GROUP BY FieldName1, FieldName2, Fieldname3
Having
COUNT(*) > 1


However, I need all records to be displayed, rather than reduced to display as groups, such that the count column will display True or false to indicate that the record has a corresponding duplicate in the list.

It would look something like:

CODE
FieldName1 | FieldName2 | Fieldname3 | DuplicateRecord
Joe Bloggs | 123 Address Street |  Capital Town | True


Also, Ridders. why do I have duplicates? Well, these records are originally posted in an outlook email from another SQL db every day. I've written a procedure that imports these records into Access. The records contain duplicates, which I am trying to highlight, and possibly create a report from.
I know it seem convoluted, but this is the tech I must work with.
Further you said : Could you just check for / 'highlight' duplicates using CF when the form opens?" How is this done?
Go to the top of the page
 
SomekindaVB
post Nov 28 2017, 05:23 PM
Post#7



Posts: 160
Joined: 15-December 16



It's a continuous form. I went into CF and couldn't find any like show duplicates. this is why i'm trying a code solution, or paint solution, but it's all bit nonsensical.

So i'm now looking for a solution in a query that loads the data where there is a duplicate matching the record. with this i could use CF against a True/False field.

Or if you have an answer, I'd love to know it.

Cheers
Go to the top of the page
 
SomekindaVB
post Nov 28 2017, 05:54 PM
Post#8



Posts: 160
Joined: 15-December 16



Awesome!

So how is it done?

I need all records that includes all duplicates, with a field that displays whether a given record is a duplicate or not (Keeping in mind that the duplicate is somewhere in the list). Which is to say, I do not need to display only the duplicates. I need all records: Duplicates, warts, and funny looking noses

Which is to say not this

CODE
SELECT Field, COUNT(Field) AS DuplicateCount
FROM Table
GROUP BY Field
HAVING ( COUNT(Field) > 1 )
Go to the top of the page
 
SomekindaVB
post Nov 29 2017, 12:07 AM
Post#9



Posts: 160
Joined: 15-December 16



So in a desperate bid to try answer my own question I devised this

CODE
SELECT TblTable.Field1, TblTable.Field2, TblTable.Field3, TblTable.Field4, TblTable.Field5, Count([Field1] & [Field2] & [Field3] & [Field4]) AS DupCount, "Dups" as RecType
FROM TblTable
GROUP BY TblTable.Field1, TblTable.Field2, TblTable.Field3, TblTable.Field4, TblTable.Field5
HAVING TblTable.Field5="Condition" AND COUNT([Field1] & [Field2] & [Field3] & [Field4]) > 1
Union
SELECT TblTable.Field1, TblTable.Field2, TblTable.Field3, TblTable.Field4, TblTable.Field5, Count([Field1] & [Field2] & [Field3] & [Field4]) AS DupCount, "Dups" as RecType
FROM TblTable
GROUP BY TblTable.Field1, TblTable.Field2, TblTable.Field3, TblTable.Field4, TblTable.Field5
HAVING TblTable.Field5="Condition" AND COUNT([Field1] & [Field2] & [Field3] & [Field4]) = 1


The big problem that I have is that TblTable contains over 2000 records with a condition, but the aggregate total of this query is only 1800 records, so I'm screwed if I know where records are vanishing to.

Surely somebody has done something like this before now. I've searched all over google and all anybody knows is how to repeat the same [censored] over and over. I need the count of duplicates and all the records together in one tidy place.
Go to the top of the page
 
PhilS
post Nov 29 2017, 04:12 AM
Post#10



Posts: 404
Joined: 26-May 15
From: The middle of Germany


QUOTE
The big problem that I have is that TblTable contains over 2000 records with a condition, but the aggregate total of this query is only 1800 records

UNION removes duplicates in the same way as DISTINCT. Use UNION ALL to include those duplicate records.

The results of your query will be read-only because of the UNION. If you need them to be writeable you could use the DCount-Function in your query to get the duplicate count.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    15th December 2017 - 08:58 AM