Preventing duplicates in certain fields of a form

  • Thread starter Thread starter wmdmurphy
  • Start date Start date
W

wmdmurphy

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
 
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
 
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
Click to expand...

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.
 
Thanks Allen, this worked fine.

Bill

Allen Browne said:
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

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

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

wmdmurphy said:
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?
Click to expand...
Click to expand...
 
Back
Top