Table Level Validation? (1 Viewer)

[dynamictiger]

I have a client database that records name, address, phone etc.

I want to check for duplicates in the address and suburb field. So I thought I would add validation at table level that Address & suburb <> Address & Suburb, with an appropriate message.

This validation rule appears to be stopping the duplicate entries but the validation message is not firing?

 

[ColinEssex]

Can you post the code you are using?

Col

 

[dynamictiger]

The code I am using on the command button to move to the next record is:

Private Sub cmdWT_Click()

If Not fIsLoaded("frmLists") Then

blnNew = True

End If

If fIsLoaded("frmServiceBoard") Then

DoCmd.OpenForm "frmBookJob"

Else

DoCmd.RunCommand acCmdSave

DoCmd.OpenForm "frmCPDetails", acNormal, , , , , Me.HomePhone

End If

End Sub

 

[David R]

I think what Colin meant was the validation rule you're using.

And can you clarify what you mean by 'stopping the duplicate entries but the validation message is not firing'? It's blocking the new entry without any message at all, or not giving the validation message you expect?

 

[dynamictiger]

The validation is:

[CAddress] & [CSuburb]<>[CAddress] & [CSuburb]

The validation message is:

This entry will cause duplicates in the Address and Suburb. Go back and search by Address and modify the exisitng record.

This is occuring at Table level. Looking at the previous code, we are using acCmdSave so we should be saving the record to the table.

The validation message does not fire at all.

It appears that if I add a new record for 91 Adrian Street and a previous record exists for 91 Adrian Street the entry is not added to the table.

 

[David R]

I've never used table-level validation, so I can't speak to that problem. However there may be a few other ways to attack this problem:

In the BeforeUpdate event of each of these fields on your form, do a DCount of "[CAddress] = '" & Me.CAddressControl & "' AND [CSuburb] = " & Me.SuburbControl & "'". If it exists then there's more than one, and you can leave a message to stop them and Cancel the event. I put it in both fields because they may enter them out of order; you will of course have to make sure both fields are filled in, first.

Similar to what you're doing now, you can do the same thing but at the Form's BeforeUpdate event, instead of the fields. This has the benefit of only having to code it once. It has the drawback that they won't know it's a duplicate record until they go to leave/save the record, so there may be a considerable amount of wasted effort.

A third option is to make CAddress and CSuburb a multipart index, No Duplicates. (Essentially this does the same thing as the 2nd option, just with no code). You can trap the error to give a custom message with code, however. http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=29843

At the end, though, what happens if one person enters 91 Adrian St. and the other enters 91 Adrian Street?