Highlight and Message for duplicate Record (1 Viewer)

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Hello Experts
Ramadhan Kareem to ALL.
I have attached a dB which I what to highlighted and message me when I entry any duplicate "ID".
e.g.
tblSC tblSC
SCIDYearMonthCreateDID
2​
202101
13/04/2021​
1​
7​
202101
13/04/2021​
2​
10​
202101
13/04/2021​
3​
11​
202101
13/04/2021​
2​
12​
202101
13/04/2021​
1​
13​
202101
13/04/2021​
3​

any help will appreciate...
 

Attachments

  • Capture.PNG
    Capture.PNG
    20 KB · Views: 78

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Thank you so much for your example.
Here I want if Year, Month, ID have a data then dont insert again any record.
SCIDYearMonthCreateDID
220210113/04/20211
"ID" =1 ... Dont Insert second time.
HERE this data can't insert
SCIDYearMonthCreateDID
320210113/04/20211

If year Or Month is new record then Same ID can insert. Here change Month then Same ID can Insert
SCIDYearMonthCreateDID
420210213/04/20211

Or Here change Year then Same ID can Insert
SCIDYearMonthCreateDID
520220113/04/20211
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
I have upload my dB... Can any one check why my dB not working...
 

Attachments

  • TEST SL.accdb
    536 KB · Views: 83

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:39
Joined
Aug 30, 2003
Messages
36,118
Based on your description, something like:

Code:
  If DCount("*", "tblSC", "ID = " & Me.ID) = 0 Then
    DoCmd.RunCommand acCmdSave
  Else
    MsgBox "You enter a duplicate Data"
    Cancel = True
  End If

I don't understand the test you have in there now.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:39
Joined
Aug 30, 2003
Messages
36,118
Now that I look at it, you may want to add month and year to the test.
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Based on your description, something like:

Code:
  If DCount("*", "tblSC", "ID = " & Me.ID) = 0 Then
    DoCmd.RunCommand acCmdSave
  Else
    MsgBox "You enter a duplicate Data"
    Cancel = True
  End If

I don't understand the test you have in there now.
Hello Paul, Thanks for your reply.
This code based on the ID = "Find Duplicate",
But I want "ID" can duplicate if Year OR Month is new data.
See Msg #3
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:39
Joined
Aug 30, 2003
Messages
36,118
Try

If DCount("*", "tblSC", "ID = " & Me.ID & " AND Month = '" & Me.Month & "' AND Year = '" & Me.Year & "'") = 0 Then

It runs into your "no duplicates" restriction on the ID field.
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Try

If DCount("*", "tblSC", "ID = " & Me.ID & " AND Month = '" & Me.Month & "' AND Year = '" & Me.Year & "'") = 0 Then

It runs into your "no duplicates" restriction on the ID field.
If you add Month & Year. its same thing previous code.
Because, "ID" Based depend on two column, If not same INSERT data, otherwise cancel...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:39
Joined
Aug 30, 2003
Messages
36,118
Sorry, I guess I don't understand the need. I've going into a meeting so I won't be able to reread the thread for a while.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:39
Joined
May 7, 2009
Messages
19,175
please see and test.
 

Attachments

  • TEST SL.accdb
    1.6 MB · Views: 80

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:39
Joined
May 7, 2009
Messages
19,175
You have to enter year, month and id.
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
I try to delete my previous INDEXs setting. If delete indexs then duplicate data saved.
Capture.PNG
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Sir, I want to Previous two columns row field are verified by ID. If same record have then message "Duplicate" and highlight.
If same "ID" but Year have New record OR Month have new record then SAVED date.
 

smtazulislam

Member
Local time
Today, 16:39
Joined
Mar 27, 2020
Messages
806
Maybe it has solve. just little correction.
DCount("1", "qryChecked", replace DCount("*", "qryChecked",

and Delete : Before_Update() code and Delete INDEXs
can you here have little correction plz...
Here after message then data will be save. But I want data dont saved without correction.
 

Users who are viewing this thread

Top Bottom