checking for duplicates

[krowe]

Hi

I have a form where customers are entered. I want to ensure the same customer is not given 2 references so am divising a bit of code to check. I have created a field on the form that strings together the Surname and UPRN. I have then created a qry which does the same and gives a count of how many exist in the table.

I no want to apply validation to all the aspects. I can get them to work fine individually, but cannot seem to work out how to get all three working together.

I first want to check a surname has been entered. Then I want to check a UPRN has been entered. Finally I want to check the check2 control against the qry to look for duplicates.

I have tried nesting the if statements, and running them one after hte other, but what ever i try the form either stays open when i want to shut it, or saves the new entry when i dont want it to.

This is what i have at the moment running from a close button (I have disabled the native close button on the form):

Private Sub Command40_Click()
'check surname is present
If IsNull(Me![ClientSurname]) Then
If MsgBox("'Client Surname' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is blank") = vbCancel Then
DoCmd.Close

Else
Me![ClientSurname].SetFocus

End If
End If

'check address is present
If IsNull(Me![AddressUPRN]) Then
If MsgBox("'Address' must contain a value." & Chr(13) & Chr(10) & _
"Press 'OK' to return and enter a value." & Chr(13) & Chr(10) & _
"Press 'Cancel' to abort the record.", _
vbOKCancel, "A Required field is blank") = vbCancel Then
DoCmd.Close

Else
Me![Command39].SetFocus

End If

End If

'check no duplicate
If DCount("*", "qryCheckDuplicate", "check='" & Me.check2 & "'") > 0 Then
MsgBox ("this is a duplicate entry and will not be saved")
Me.Undo
DoCmd.Close
Else
DoCmd.Close
End If

End Sub

 

[Mihail]

At table level declare both fields as required.
Also set an index on this two fields with the property unique.
Access will do the rest with no code.

 

[krowe]

Thanks for that, i'd forgotten about table level validation for required fields.

I put required in the table, but not the Unique, becuase it is possible for 2 customers to have the same surname.

This gave rise to some unfriendly messages when the field was left empty, so i put in user defined messages in the on error event, so far so good..

However my close button threw up a different error 2169. So, I got rid of the macro close they give you and put in a normal vba close command, and now the form just closes without notifying the user they havent entered a surname or address. (its not saving the record though at least)

here is my custom error handling:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'If an error occurs because of missing data in a required field
'display our own custom error message
    Const conErrRequiredData = 3314
    If DataErr = conErrRequiredData Then
        MsgBox ("Please ensure that you enter a Surname and Address")
       Response = acDataErrContinue
    Else
        'Display a standard error message
        Response = acDataErrDisplay
    End If
End Sub

any other ideas what i may be doing wrong.

I still have the check for duplicate code running on before update, but without the validation of the 2 fields

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "qryCheckDuplicate", "check='" & Me.check2 & "'") > 0 Then
'duplicate
MsgBox ("this is a duplicate entry and will not be saved")
Me.Undo
Else
'not duplicate
End If

End Sub

Thanks again

Kev

 

[Mihail]

Not sure that I understand exactly what you say and not sure that you understand what I said
An index (only one) can be defined on two fields. So, you can have duplicate values in any field but the pair will be unique. Something like this:
A B c d e f
A C b d e f - is OK
B C a d e f - is OK
A C j k l - is NOT OK because the pair AC already exist.

Hope you understand that every character (A, B, C, D etc) is a value in one field

 

[krowe]

oh, how do you set up an index on 2 fields and link them, I didnt know you could do that.

I have attached a sample DB with my setup (sorry about the code that hopefully going to be redundant)

maybe you can show me in that what i need to do.

Thanks

Kev

 

[Mihail]

Better for you is to explain.
Table design
Design tab
Ribbon: Indexes (an icon like Zeus gun I don't know the word in English)

Now you are in a pop up window. It is possible to see more indexes that Access has been created before.
The PrimaryKey should be there.
In the first empty row, under Index Name tab, type a name for your custom index. Use the general rules for names: No spaces, no reserved words and so on.
On the same row, under Field Name tab, choose one from your fields.
Set Unique to Yes (see the bottom of the pop up window).
On the next row under Field Name choose the second field. DO NOT fill this row under the Index Name tab.
That's all.
Close the window, save the table and good luck !

 

[krowe]

well you learn something new every day!!!

Thanks so much, wish i had posted sooner, and not spent 2 days playing with querys and code to do something so simple.

 

[Mihail]

Glad to help you.
You can use any number of fields (not only 2).
And You haven't say me how is named the Zeus's gun

 

[krowe]

in the words of Queen - Flash! ahhhhh

 

[krowe]

or lightning bolt