Warn But Not Prevent Duplicate Data

AdamMVRRS

Registered User.
Local time
Today, 17:04
Joined
Sep 27, 2013
Messages
16
Hi All,

I'm having a bit of trouble with my database and I was hoping you could help me.

We're a training company so we have a Learner Details form. In this, we have the learners unique National Insurance number (as well as a primary key). When our inputters enter a new learner onto the system, I need a message to pop up and warn them that the National Insurance number has already been entered and I need the option to dismiss that message. The reason for this is that when a learner completes programme they can come back and do a higher level and our subforms etc need a new record to document new reviews etc.

The code I am playing with at the moment is:

Private Sub NI_number_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True

Else:
End If
End Sub

(I know the space in the field name is bad - error on my part)

But this code isn't proving much help to me at the moment.

Any help is appreciated! Thanks - Adam
 
what is not working about this?

Any data I enter into the NI Number field at all is saying it's a duplicate - even a 'j'.

If it did work, when you click okay the field will not save unless you change the NI Number, sometimes we will need to have the same number on two records for a learner doing two different levels, so I need to dismiss the message but still be able to save the field.

Thanks.
 
Is the data actually already saved in the record ie something like below to stop the Lookup looking at the current record

DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Me!PKField)

actually that doesnt make much sense does it the data already being saved, cos even if it was you would need to prevent how it actually was saved.
 
Last edited:
Is the data actually already saved in the record ie something like below to stop the Lookup looking at the current record

DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Me!PKField)

I'm not totally sure what you mean, sorry you're talking to a relative novice. Do you mean the function is looking at the same record that's why it's returning the error?
 
I'm not totally sure what you mean, sorry you're talking to a relative novice. Do you mean the function is looking at the same record that's why it's returning the error?

Yes . Add the extra code and see if that move you on a step.

To then allow the update to go through, try taking out the Cancel = True
 
Yes . Add the extra code and see if that move you on a step.

To then allow the update to go through, try taking out the Cancel = True

I just seem to be getting a syntax error. I've tried it as you wrote it and I've changed what you said as primary key to my primary key "MVRRS_Learner_ID" but no luck
 
I just seem to be getting a syntax error. I've tried it as you wrote it and I've changed what you said as primary key to my primary key "MVRRS_Learner_ID" but no luck

Maybe there it hasnt actually saved then. Ie there is no data for PK

Try

DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0))

to see if the syntax error goes at least.

(Ie code puts 0 in if PK is actually null)

Also I am assuming your PK is a number - otherwise if string, you need quotes again!
 
Maybe there it hasnt actually saved then. Ie there is no data for PK

Try

DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0))

to see if the syntax error goes at least.

(Ie code puts 0 in if PK is actually null)

Also I am assuming your PK is a number - otherwise if string, you need quotes again!

Still getting a syntax error :( and yes my PK is an autonumber I don't enter that!
 
Maybe there it hasnt actually saved then. Ie there is no data for PK

Try

DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0))

to see if the syntax error goes at least.

(Ie code puts 0 in if PK is actually null)

Also I am assuming your PK is a number - otherwise if string, you need quotes again!

Am I meant to be changing the bold to MVRRS Learner ID?
 
copy and paste the code as you have it now - and I'll have a look.

Private Sub NI_number_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0))
If Not IsNull(Answer) Then
MsgBox "Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True

Else:
End If
End Sub

Thanks for your help.
 
Am I meant to be changing the bold to MVRRS Learner ID?

I have tried it here and it works ok.

Yes the 2 refernces to PK i put in: the first one should be the name of the PK field as it appears in the table, the second one the name of the PK field on the form.

If its not on the form, put it on and make it invisible.
 
Your msgbox code doesn't check what to do, you can give a OK only and it will be canceled by the next line. Try:
Code:
Private Sub NI_number_BeforeUpdate(Cancel As Integer)
if Dcount ("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0)) > 0 then
    if MsgBox ("Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate") = vbCancel then

         Cancel = True

    End if
End If
End Sub
 
Your msgbox code doesn't check what to do, you can give a OK only and it will be canceled by the next line. Try:
Code:
Private Sub NI_number_BeforeUpdate(Cancel As Integer)
if Dcount ("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0)) > 0 then
    if MsgBox ("Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate") = vbCancel then

         Cancel = True

    End if
End If
End Sub

His message box is fine - should just warn, and not tell user to redo unless they have mande an error.

Its not helpful at this point, either way though.
 
I have tried it here and it works ok.

Yes the 2 refernces to PK i put in: the first one should be the name of the PK field as it appears in the table, the second one the name of the PK field on the form.

If its not on the form, put it on and make it invisible.

Now I'm getting Compile Error Expected list or separator )

Your msgbox code doesn't check what to do, you can give a OK only and it will be canceled by the next line. Try:
Code:
Private Sub NI_number_BeforeUpdate(Cancel As Integer)
if Dcount ("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [PrimaryKeyField] <>" & Nz(Me!PKField,0)) > 0 then
    if MsgBox ("Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate") = vbCancel then

         Cancel = True

    End if
End If
End Sub

This is just highlighting the second line in yellow even when I change the PK to my primary key :(

__________

This is what I have right now:

Private Sub NI_number_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [MVRRS Learner ID] <>" & Nz(Me!MVRRS Learner ID,0))
If Not IsNull(Answer) Then
MsgBox "Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True

Else:
End If
End Sub
 
You need _ for the spaces in the PK field name as in teh other field names.
 
Yeah I've got them in there :(

your missing the

Answer =

ie

Private Sub NI_number_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [MVRRS Learner ID] <>" & Nz(Me!MVRRS Learner ID,0))
If Not IsNull(Answer) Then
MsgBox "Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True

Else:
End If
End Sub
 
your missing the

Answer =

ie

Private Sub NI_number_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[NI_number]", "LearnerDetails", "[NI_number] = '" & Me.NI_Number & "' AND [MVRRS Learner ID] <>" & Nz(Me!MVRRS Learner ID,0))
If Not IsNull(Answer) Then
MsgBox "Duplicate National Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True

Else:
End If
End Sub

Silly me. Now I'm getting access can't find the field MVRRS_Learner_ID referred to in your expression, but the field name is exactly the same?
 

Users who are viewing this thread

Back
Top Bottom