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.

 

[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!!

 

[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?

 

[lojain]

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

 

[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

 

[Gigitty]

Thanks guys!! that fixed the error. Now if I want to take the user to the record that already exists as part of this process?

 

[arnelgp]

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.RecordsetClone
    .FindFirst "UMRN = '" & Me!UMRN & "'"
    Cancel = (.NoMatch = False)
    If Cancel Then
        MsgBox "UMRN already exists"
        Me.UMRN.Undo
        Me.Bookmark = .Bookmark
    End If
End With
End Sub

 

[Gigitty]

Thanks for your prompt reply Arnel. Placed the code in but absolutely nothing happens when trying to trigger the code..

 

[arnelgp]

ooh, you have UMRN_BeforeUpdate.

copy the code from the Form's BeforeUpdate and paste it in UMRN_BeforeUpdate
replacing All the code (of UMRN_BeforeUpdate).

delete the Form's BeforeUpdate event.