Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-30-2019, 08:09 AM   #1
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Recordset Operation

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

Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 08:42 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Recordset Operation

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-30-2019, 10:12 AM   #3
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

Thanks for the post.

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

Can you advise?

Thank you.

Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 10:33 AM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Recordset Operation

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 [Dupl 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.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
Cronk is offline   Reply With Quote
Old 09-30-2019, 10:56 AM   #5
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

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
Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 11:06 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Recordset Operation

what is Period field, text of numeric?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-30-2019, 11:09 AM   #7
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

Period is a text field

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

Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 11:09 AM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Recordset Operation

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-30-2019, 11:37 AM   #9
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

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
Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 01:28 PM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,045
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Recordset Operation

Have you tried using the built in "Find Duplicate" wizard?
Do you need something more than what that does?
Mark_ is offline   Reply With Quote
Old 09-30-2019, 09:10 PM   #11
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

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
Ben_Entrew is offline   Reply With Quote
Old 09-30-2019, 09:28 PM   #12
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Recordset Operation

if there is no current record, it will not go through (.bof and .eof).
maybe on the recordset name, replace "rRsl" with just "r".
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-30-2019, 10:27 PM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,594 Times in 1,514 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Recordset Operation

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-30-2019, 10:28 PM   #14
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Recordset Operation

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
Ben_Entrew is offline   Reply With Quote
Old 10-01-2019, 07:23 AM   #15
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,045
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Recordset Operation

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?

Mark_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exit from operation sohailcdc Forms 4 06-03-2013 10:46 PM
Complex Operation benkingery Queries 1 10-23-2009 08:22 AM
query operation iboon Queries 2 04-04-2009 04:29 AM
Parameter operation andrewjs Queries 3 10-12-2007 12:15 PM
Invalid Operation paulS30berks General 1 04-21-2006 08:20 AM




All times are GMT -8. The time now is 05:22 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World