Avoid duplicate value warning

[giedrius]

I have a simple bound form where one of the controls is bound to a field with No duplicates property. When user enters a duplicate value in the control when entering a new record, MS Access 2000 gives a warning message. How do I avoid this warning message and check if value entered is unique?

Thanks for any tips.
giedrius

 

[Oldsoftboss]

I use

Private Sub Tag_No_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Tag_No_BeforeUpdate
Dim counter As Integer

counter = DCount("*", "ClubEquipment", "TagNo = Forms![frmClubEquipment]![sfmClubEquipment]![TagNo]")

If counter > 0 Then
MsgBox "This Tag No already exists, Please enter a unique Tag No. ", vbExclamation
Me.Undo
Exit Sub
End If

Exit_Tag_No_BeforeUpdate:
Exit Sub

Err_Tag_No_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Tag_No_BeforeUpdate

End Sub

I dont have the No duplicates set, therefore that error is not generated.

HTH
Dave

 

[giedrius]

I found the following code in MS Access help, however it produces run-time error 3426 "This action was cancelled by an associated object".

Private Sub partnerKodas_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.Recordset
rst.FindFirst "partnerKodas = " & Me!partnerKodas
If Not rst.NoMatch Then
MsgBox "This code already exists, enter new one."
Me.partnerKodas.SetFocus
End If
rst.Close
End Sub


Any ideas who this "associated object" might be?

giedrius

 

[giedrius]

Could it be because I am invoking the form in record add mode with acFormAdd in another form:

DoCmd.OpenForm "Supplier", , , , acFormAdd, acDialog, NewData

Any ideas?
Thanks, giedrius

 

[Mile-O]

Catch it in the form's Error event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = ???? Then
        MsgBox "Bla bla bla bla", vbExclamation
        Response = acDataErrContinue
    End If
End Sub

I don't know the error so initially put: MsgBox DataErr in the form's Error event to get the error number.

 

[giedrius]

OK folks, thank you very much. I'll use a combination of both tips. Is there some point system to award for accepted answers? Please advise as I am new to this forum.

giedrius

 

[Oldsoftboss]

Awards will be presented soon
See page 2 of this thread.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58804&pagenumber=2