Check for duplicate company names

  • Thread starter Thread starter Denise
  • Start date Start date
D

Denise

I have a button on my form to add a new company. When
they click on this button, I would like to search to see
if the company is already entered. I would like to do a
message "The company name already exists" with a VBYesNo.
If yes, enter the company name anyway. If no cancel
adding.

I can't figure this code out myself...any help would be
great.Thank you.
 
Denise,

To do this using a command button, add the following code:
Private Sub cmdMyButton_Click()
If Not IsNull(DLookup("[CompanyName]", "tblMyTable", _
"[CompanyName] = """ & Me!txtCompanyName & """"))
Then
DoCmd.Beep
If vbYes = MsgBox("The company name already exists." & vbCrLf &
_
"Do you want to add it anyway?",
vbYesNo+vbQuestion, _
"Duplicate Company Name") Then
'The user clicked 'Yes' - do whatever you want
Else
'The user clicked 'No' - cancel the operation.
Me.txtCompanyName = Me.CompanyName.Undo
End If
End If
End Sub

I would, however, recommend doing this in the textbox's BeforeUpdate event,
and forget about the button:
Private Sub txtCompanyName_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CompanyName]", "tblMyTable", _
"[CompanyName] = """ & Me!txtCompanyName & """"))
Then
DoCmd.Beep
If vbNo = MsgBox("The company name already exists." & vbCrLf & _
"Do you want to add it anyway?",
vbYesNo+vbQuestion, _
"Duplicate Company Name") Then
'The user clicked 'No' - cancel the operation.
Me.txtCompanyName = Me.CompanyName.Undo
Cancel = True
End If
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top