Duplicate Values : forcing an issue when one is discovered

[Donna Tunstall]

Hi There,

I need to do a check in my database to make sure a duplicate value does not exist and force the user back to that field to re-enter if it does. I have a mainform and a subform and this is my code:

Private Sub PROJNUM_BeforeUpdate(Cancel As Integer)

If (DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]" = Forms!frmRegistrationForm!PROJNUM) Is Not Null) Then
Beep
MsgBox "The Bizman Number you entered already exists. Enter a unique Bizman Number", vbInformation, "Duplicate Bizman"
Me.PROJNUM.SetFocus

End If

End Sub

In testing when I enter a duplicate ID, the message box shows but the code completely ignores my setfocus command and moves the cursor to the next field anyway AND (which is worse) populates my subform with all the data relating to that duplicate ID.

How can I stop this? If a duplicate is detected I want the whole bus-load to stop and force the user to enter a unique ID. I've been playing with this for a few days now and am at my wits end. Any help would be greatly appreciated.

TIA.

 

[Randomblink]

What you want to do is...
On the BeforeUpdate event, check for Duplicate ID...
Then if the ID is a Duplicate, set the field to Null...
Since you can't set Focus to a Field that hasn't lost Focus, you would need to SetFocus to another field and then back again...
I believe that would do it...

(am I right oh great ones?...lol)

 

[Donna Tunstall]

You know what ALL of my code is being ignored. Stupidly I did not realise that my error message line was the same as my msgbox. So now when I just ask for the error message to read "Error", that is the message I see. What am I doing wrong?? Help! TIA.


Private Sub PROJNUM_BeforeUpdate(Cancel As Integer)
On Error GoTo PROJNUM_Err

If (DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]" = Forms!frmRegistrationForm!PROJNUM) Is Not Null)
Then
MsgBox "The Bizman Number you entered already exists. Enter a unique Bizman Number", vbInformation, "Duplicate Bizman"
Me.PROJNUM = Null
Me.DESC.SetFocus
Me.PROJNUM.SetFocus
DoEvents
End If

'Debug.Print

'Me![order subform].SetFocus
'Me![order subform]![qty].SetFocus

PROJNUM_Exit:
Exit Sub

PROJNUM_Err:
MsgBox "Error Exists"
Resume PROJNUM_Exit

End Sub

 

[Carol]

Try the following:

Private Sub PROJNUM_BeforeUpdate(Cancel As Integer)
On Error GoTo PROJNUM_BeforeUpdate_Err

If (DLookup("[ProjNum]", "tblProjectHeader", "[ProjNum]=Forms![frmRegistrationForm]![PROJNUM]")) Then
Beep
MsgBox "The Bizman Number you entered already exists. Enter a unique Bizman Number", vbInformation, "Duplicate Bizman"

End If


PROJNUM_BeforeUpdate_Exit:
Exit Sub

PROJNUM_BeforeUpdate_Err:
MsgBox Error$
Resume PROJNUM_BeforeUpdate_Exit

End Sub