Highlight and Message for duplicate Record

[smtazulislam]

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

SCID	Year	Month	CreateD	ID
2​	2021	01	13/04/2021​	1​
7​	2021	01	13/04/2021​	2​
10​	2021	01	13/04/2021​	3​
11​	2021	01	13/04/2021​	2​
12​	2021	01	13/04/2021​	1​
13​	2021	01	13/04/2021​	3​

any help will appreciate...

 

[jdraw]

see this material on composite unique index.

 

[smtazulislam]

see this material on composite unique index.

Thank you so much for your example.
Here I want if Year, Month, ID have a data then dont insert again any record.

SCID	Year	Month	CreateD	ID
2	2021	01	13/04/2021	1

"ID" =1 ... Dont Insert second time.
HERE this data can't insert

SCID	Year	Month	CreateD	ID
3	2021	01	13/04/2021	1

If year Or Month is new record then Same ID can insert. Here change Month then Same ID can Insert

SCID	Year	Month	CreateD	ID
4	2021	02	13/04/2021	1

Or Here change Year then Same ID can Insert

SCID	Year	Month	CreateD	ID
5	2022	01	13/04/2021	1

 

[smtazulislam]

I have upload my dB... Can any one check why my dB not working...

 

[pbaldy]

Based on your description, something like:

  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]

Now that I look at it, you may want to add month and year to the test.

 

[smtazulislam]

Based on your description, something like:

  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]

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]

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]

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.

 

[smtazulislam]

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.

its okay. Thank you so much for your reply...

 

[arnelgp]

please see and test.

 

[smtazulislam]

please see and test.

No, its no work well,

 

[arnelgp]

You have to enter year, month and id.

 

[smtazulislam]

I try to delete my previous INDEXs setting. If delete indexs then duplicate data saved.

 

[smtazulislam]

You have to enter year, month and id.

I enter year with new record and month same record and ID same. But here said, duplicate...

 

[smtazulislam]

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]

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.