non-duplicating

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a form that is bound to a table. In the form is a
txtbox (txtClientCode) that is a an alphanumeric code.
This code is non-duplicate. The table field is set as
non-duplicating. If the user should accidently duplicate
the client code, the notice does not occur until either
the form is requeried or the user closes the form. I
would like to have the user get notice when they leave
the client code field so they can redo the client code.
How does one go about that and how what is used in code
to provide notice of the duplication?
Thanks to anyone who responds.
*** John
 
Use the BeforeUpdate or AfterUpdate event procedure of the text box where
this is entered, to lookup the table and see if the entry already exists
(unless the entry was changed back to same thing.

Private Sub txtClientCode_BeforeUpdate()
Dim strWhere As String
With Me.txtClientCode
If .Value = .OldValue Then
'do nothing.
Else
strWhere = "[ClientCode] = """ & .Value & """"
If Not IsNull(DLookup("ClientCode", "ClientTable", strWhere))
Then
MsgBox "Duplicate"
'Cancel = True
End If
End If
End With
End Sub
 
Allen, thanks. That is doing it. Is there a way to
eliminate the system warning if I put a message in that
it is a duplicate and needs to be changed?
*** John

-----Original Message-----
Use the BeforeUpdate or AfterUpdate event procedure of the text box where
this is entered, to lookup the table and see if the entry already exists
(unless the entry was changed back to same thing.

Private Sub txtClientCode_BeforeUpdate()
Dim strWhere As String
With Me.txtClientCode
If .Value = .OldValue Then
'do nothing.
Else
strWhere = "[ClientCode] = """ & .Value & """"
If Not IsNull(DLookup
Click to expand...
("ClientCode", "ClientTable", strWhere))
 
Not sure exactly which error this is, but I think you are talking about one
of the errors generated by JET (not code), so you would need to use the
Error event of the form to catch and suppress it.

In Form_Error, DataErr will be 3022 for a duplicate index error.
Set Response to acDataErrContinue to suppress the built-in error message.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JohnE said:
Allen, thanks. That is doing it. Is there a way to
eliminate the system warning if I put a message in that
it is a duplicate and needs to be changed?
*** John

-----Original Message-----
Use the BeforeUpdate or AfterUpdate event procedure of the text box where
this is entered, to lookup the table and see if the entry already exists
(unless the entry was changed back to same thing.

Private Sub txtClientCode_BeforeUpdate()
Dim strWhere As String
With Me.txtClientCode
If .Value = .OldValue Then
'do nothing.
Else
strWhere = "[ClientCode] = """ & .Value & """"
If Not IsNull(DLookup
Click to expand...
("ClientCode", "ClientTable", strWhere))
Then
MsgBox "Duplicate"
'Cancel = True
End If
End If
End With
End Sub
Click to expand...
Click to expand...
 
Back
Top