Recordset Operation (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Hi all,
I got following issue:

There are two tables called Result and Duplicates.

Now I want to mark all duplicated in the Result table.

Here the content:
RESULT TABLE (my goal):
Booking Costs Period Dupl edited
BC 2089 22018
BC 2089 22018 x
AZ 1301,51 102018
AZ 1301,51 102018 x
MK 1000 22018
MK 1000 22018 x
MK 1000 22018 x
MK 1000 22018 x


Duplicates:
PROVISION Costs Period COUNT Booking
gdcd 2089 22018 2 BC
awe 1301,51 22018 2 AZ
gr 1000 22018 4 MK


I tried followig code without any success:

Dim a, b, strSQL As String
Dim rs, rs2 As Recordset
Dim i, c As Integer

Set rs = CurrentDb.OpenRecordset("DUPLICATES_2018")
Set rs2 = CurrentDb.OpenRecordset("RESULT_TABLE")

rs.MoveFirst

Do While Not rs.EOF
a = rs("Booking")
b = rs("Period")

rs2.MoveFirst
Do While Not rs2.EOF

If rs2("Booking") = a And rs2("Period") = b Then

i = DCount("[Booking]", "Result_Table", "([Booking] = '" & a & "') and ([Period] = '" & b & "')")

rs2.MoveNext
For c = 1 To i - 1
rs2.MoveNext

rs2.Edit
rs2("Dupl edited") = "x"
rs2.Update

Next c

Else

End If

rs2.MoveNext

Loop

rs.MoveNext

Loop
rs2.Close
rs.Close

End Sub


It tells me no current dataset found.

I just want to mark all duplicated besides the first record of each one.

Thank you in advance for your help.

Kind regards,
Ben
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:46
Joined
May 7, 2009
Messages
19,175
Code:
    Dim rRsl As DAO.Recordset
    Dim dbs As DAO.Database
    
    Set dbs = CurrentDb
    
    With dbs.OpenRecordset("DUPLICATES_2018", dbOpenSnapshot)
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Set rRsl = dbs.OpenRecordset("SELECT [edited] FROM RESULT_TABLE WHERE " & _
                                        "[booking]=" & Chr(34) & ![booking] & Chr(34) & " AND " & _
                                        "[Period] & ''= " & Chr(34) & ![Period] & Chr(34))
            If Not (rRsl.BOF And rRsl.EOF) Then rRsl.MoveNext
            Do While Not rRsl.EOF
                .MoveNext
                If rRsl.EOF Then Exit Do
                rRsl.Edit
                rRsl!edited = "x"
                rRsl.Update
            Loop
            Set rRsl = Nothing
            .MoveNext
        Wend
        .Close
    End With
    Set dbs = Nothing
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Thanks for the post.

Unfortunately it doesn't run through. Run-time error 3061: Too few parameters. Expected 1.

Can you advise?

Thank you.
 

Cronk

Registered User.
Local time
Today, 20:46
Joined
Jul 4, 2013
Messages
2,770
Try
Code:
Dim rRsl As DAO.Recordset
    Dim dbs As DAO.Database
    
    Set dbs = CurrentDb
    
    With dbs.OpenRecordset("DUPLICATES_2018", dbOpenSnapshot)
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Set rRsl = dbs.OpenRecordset("SELECT [B][COLOR=Red][Dupl edited] [/COLOR][/B]FROM RESULT_TABLE WHERE " & _
                                        "[booking]=" & Chr(34) & ![booking] & Chr(34) & " AND " & _
                                        "[Period] & ''= " & Chr(34) & ![Period] & Chr(34))
            If Not (rRsl.BOF And rRsl.EOF) Then [B][COLOR=red]rRsl.MoveFirst[/COLOR][/B]
            Do While Not rRsl.EOF
                .MoveNext
                If rRsl.EOF Then Exit Do
                rRsl.Edit
                rRsl![B][COLOR=red][Dupl edited][/COLOR][/B] = "x"
                rRsl.Update
            Loop
            Set rRsl = Nothing
            .MoveNext
        Wend
        .Close
    End With
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Unfortunately ıt still doesn't work out.

Booking Costs Period Dupl edited
BC 222 22018
BC 76867 22018
AZ 34132 102018
AZ 41234 102018
MK 43214 32018 x
MK 523 32018
MK 54134 32018


It gıves the error message:

Run tıme error 3021: No current record
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:46
Joined
May 7, 2009
Messages
19,175
what is Period field, text of numeric?
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Period is a text field

TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:46
Joined
May 7, 2009
Messages
19,175
Code:
    Dim rRsl As DAO.Recordset
    Dim dbs As DAO.Database
    
    Set dbs = CurrentDb
    
    With dbs.OpenRecordset("DUPLICATES_2018", dbOpenSnapshot)
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Set rRsl = dbs.OpenRecordset("SELECT [Dupl edited] FROM RESULT_TABLE WHERE " & _
                                        "[booking]=" & Chr(34) & ![booking] & Chr(34) & " AND " & _
                                        "[Period]=" & Chr(34) & ![Period] & Chr(34), dbOpenDynaset)

            If Not (rRsl.BOF And rRsl.EOF) Then rRsl.MoveFirst

            Do While Not rRsl.EOF
                .MoveNext
                If rRsl.EOF Then Exit Do
                rRsl.Edit
                rRsl![Dupl edited] = "x"
                rRsl.Update
            Loop
            Set rRsl = Nothing
            .MoveNext
        Wend
        .Close
    End With
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Thank you but it still doesn't work.

Booking Costs Period Dupl edited
BC 222 22018
BC 76867 22018
AZ 34132 102018
AZ 41234 102018
MK 43214 32018 x
MK 523 32018
MK 54134 32018

no current record error message jumping at .Movenext
 

Mark_

Longboard on the internet
Local time
Today, 02:46
Joined
Sep 12, 2017
Messages
2,111
Have you tried using the built in "Find Duplicate" wizard?
Do you need something more than what that does?
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
Hi Mark,

Thanks for the hint. But I need it in a program at the end. It has to run automatically every month.

TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:46
Joined
May 7, 2009
Messages
19,175
if there is no current record, it will not go through (.bof and .eof).
maybe on the recordset name, replace "rRsl" with just "r".
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:46
Joined
Feb 19, 2002
Messages
42,981
What is the point of flagging the duplicates? i.e. what will you do with that information? Have you tried using the query wizard to build a find duplicates query? That may be a simple solution.
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
As a next step I have to take the period information and replace.it with the above record. Afterwards I could delete the marked recordset.

TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
 

Mark_

Longboard on the internet
Local time
Today, 02:46
Joined
Sep 12, 2017
Messages
2,111
Taking a couple steps back, how are you getting duplicate data into your system? Would it work better to notify the user they are entering a duplicate record PRIOR to entry?
 

Ben_Entrew

Registered User.
Local time
Today, 02:46
Joined
Dec 3, 2013
Messages
177
I was handling with this for two weeks. A suitable matching key is missing. On the one hand I got a data based on reporting months on the other hand data based on time periods. Can't avoid duplicates, at least I could convince a colleague that's not possible. At the end 2 weeks waste of time [emoji3] . Anyhow thank you all for supporting.

Kind regards,
Ben

TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
 

Users who are viewing this thread

Top Bottom