Ben_Entrew
Registered User.
- Local time
- Yesterday, 23:30
- 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
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