Prevent duplication of records in the entry form

[lojain]

hello,
I'm working on Access 2016,

First, I have a regester table that contains three fields
(ID), (courseNo#) and (memberNo#)
I need to prevent duplecation in (courseNo#) and (memberNo#) in Form wizerd.
I know how to prevent duplection in one field but I don't know for two fields.
The tabe has many (courseNo#) and each (courseNo#) has many (memberNo#) are regestered in it.
It's same attache pic.

Second, I want alert to the user tell him the data is duplicated when entry duplicate data.

I would like to ask for your kind help in this matter.
Thanks.

 

[theDBguy]

Hi. The simplest approach is to create a multi field unique index; but if you want to control the message, you might have to use some VBA.

 

[Pat Hartman]

I would do both. To create a multi-field unique index, you will need to use the indexes dialog. When you open it, you should see the primary key and also any single field indexes. On the first available empty line, add a name for the index in the first field and pick the first field from the field list. Check the unique property. Then move to the next line in the dialog. Leave the index name field blank and choose the second field from the field combo. Indexes may contain up to 10 columns in Jet/ACE. Other RDBMS' support more columns in each index.

Then in the BeforeUpdate event of the form, you could use DCount() to see if the current combination of fields already exists.

If DCount("*", "yourtable", "Field1 = " & Me.Field1 & " AND Field2 = " & Me.Field2) > 0 Then
    Msgbox "some meaningful message"
    Cancel = True
    Me.Field1.SetFocus
    Exit Sub
End If

 

[lojain]

Thank you so much for quick reply,

When I try your code appeared this message,

(This error can occur when an event fails to run because the logic location of this event could not be evaluated. For example, if the form's OnOpen property is set to = [Field], this error occurs because an event or macro name is expected to run when the event starts).

I puted my table name and fields, and I chooes BeforeUpdate()

This is my code:

Private Sub N_BeforeUpdate()

If DCount("*", "Register1", "courseNo = " & Me.courseNo & " AND memberNo = " & Me.memberNo) > 0 Then
    MsgBox "some meaningful message"
    Cancel = True
    Me.courseNo.SetFocus
    Exit Sub
End If
End Sub

Thanks.

 

[Gasman]

That should be

Private Sub Form_BeforeUpdate(Cancel As Integer)

 

[lojain]

It dose not work!!

 

[Uncle Gizmo]

I answered a similar question a while back:-

https://www.niftyaccess.com/prevent-duplicates/

An explanation with YouTube videos.

Sent from Barcelona!

 

[arnelgp]

as gasman suggested, add code to the Form's BeforeUpdate event.
remove your code, and put it in the above event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "Register1", "courseNo = " & Me.courseNo & " AND memberNo = " & Me.memberNo) > 0 Then
    MsgBox "some meaningful message"
    Cancel = True
    Me.courseNo.SetFocus
    Exit Sub
End If
End Sub

 

[Gasman]

It dose not work!!

That statement helps no one.:banghead:

Have you checked you have values for the lookup, and if so is the data correct.?
The logic works, so clearly you are doing something wrong.

If you use the debugger and F8 with a breakpoint at the start of the code DLookup, you can inspect the values and then follow the path of the code.

Are both controls numeric?

 

[Uncle Gizmo]

Also see this blog from Database Solutions:-

http://www.databasedev.co.uk/duplicates.html

Sent from Barcelona!

 

[lojain]

Thank you all for your input. I have definitely used elements from all of the above, all of which are useful.
thank you.

 

[Pat Hartman]

logain,
We are not there. It doesn't work covers an enormous range of possibilities. You're going to have to be specific. Post the code. Post the error or the results and tell us what is wrong with the results. Gasman corrected your procedure header error. What happened after you fixed it? If you hadn't posted the code, he would never have been able to identify the problem with the procedure header.

 

[Gigitty]

Hi guys. Trying to achieve the same result based purely on the UMRN field but keep screwing up my syntax. I'm a VBA novice..

 

[Gasman]

You do not say what the error is?
Is UMRN text at all?, if so you need to surround with single quotes.

 

[Gigitty]

You do not say what the error is?
Is UMRN text at all?, if so you need to surround with single quotes.

Hi Gasman. Yes UMRN is a text field. Which part of the text do I need to place the single quotes around??

 

[Gigitty]

This is the error message..

 

[arnelgp]

If dcount("1","DemographicData", "UMRN = '" & Me!UMRN & "'") <> 0 Then

 

[Gigitty]

Got one step closer, then got this one..

Should there be something in here that then takes you to the record that already exists??

 

[arnelgp]

remove thw UMRN.Setfocus, the focus is already at that control
and you don't Setfocus (on any control) when you are on Validation Event.

 

[Galaxiom]

If you want an expression to read the contents of a control before the focus moves elsewhere then simply use the Text property of the control.

Me.URMN.Text