Preventing duplicates in certain fields of a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wmdmurphy - 26 Mar 2008 14:54 GMT

I would like to prevent a user from entering a duplicate company name in a
form. I know this can be done by setting a unique index on company name in
the company table and I have done this. But I would also like to give the
user a message when they attempt this. I've tried adding code in before
update and the after update but so far this does not seem to work. What is
the best coding approach for this?

Bill

Reply to this Message

Allen Browne - 26 Mar 2008 17:11 GMT

Try something like this in the BeforeUpate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim strWhere As String
Dim varResult As Variant

With Me.Company
If .Value = .OldValue Then
'do nothing if it is unchanged.
Else
strWhere = "[Company] = """ & .Value & """"
varResult = DLookup("CompanyID", "tblCompany", strWhere)
If Not IsNull(varResult) Then
strMsg = "Company " & varResult & " has the same name." & _
vbCrLf & vbCrLf & "Continue anyway?
If MsgBox(strMsg, vbYesNo+vbDefaultButton2+vbQuestion, _
"Possible duplicate") <> vbYes Then
Cancel = True
End If
End If
End If
End With
End Sub

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I would like to prevent a user from entering a duplicate company name in a
>form. I know this can be done by setting a unique index on company name in
>the company table and I have done this. But I would also like to give the
>user a message when they attempt this. I've tried adding code in before
>update and the after update but so far this does not seem to work. What is
>the best coding approach for this?

Reply to this Message

wmdmurphy - 26 Mar 2008 22:47 GMT

Thanks Allen, this worked fine.

Bill

> Try something like this in the BeforeUpate event procedure of the form:
>
[quoted text clipped - 27 lines]
>>before update and the after update but so far this does not seem to work.
>>What is the best coding approach for this?

Reply to this Message

fredg - 26 Mar 2008 17:12 GMT

> I would like to prevent a user from entering a duplicate company name in a
> form. I know this can be done by setting a unique index on company name in
[quoted text clipped - 4 lines]
>
> Bill

It really would have been nice if you had posted the actual code you
used.
Which BeforeUpdate? Which AfterUpdate? The Form's or the Control's?

Anyway, code the Company Name Control's BeforeUpdate event:

If DCount("*","TableName","[FieldName] = """ & Me.[ControlName] &
"""") >0 then
MsgBox "This company name has already been entered."
Cancel = True
End If

You will receive the message and focus will return to the control.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message