Warning Msgbox, OK/Cancel

  • Thread starter Thread starter Kirstie Adam
  • Start date Start date
K

Kirstie Adam

All,

I have a client info form, and the customers name is entered into two
fields, [FirstName] and [Surname].
I also have a expression [Title]&" "&[FirstName]&" "&[LastName] and the top
of the form to show their complete name, eg. "Miss Jemima Lane".

However, we have been getting lots of duplicates entered, some of them
intentionally (some clients have the same name) and some of them not
(putting the same client in twice)

The only solution i can think of for this is to pop up a msg box saying
"There is already a client with this name. Do you wish to continue?" after
the last name has been entered, then if the person says ok, they cary on
filling out the form, and if they cancel, it cancels out that record.

Can someone help me with the code for this?

Also, if anyone knows of a better way of avoiding these unintentional
duplicates, i would love to know.

Thanks,

Kirstie
 
If you do want to allow some dups than you are correct in your solution.

You could use code like this in the BeforeUpdate event of your form.
(I partial to using ADO - you will need to set a reference to ADO to use
this code)


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As New ADODB.Recordset
Dim txtSQL As String

txtSQL = "Select * from YourTable Where [Last Name] = '" &
Me.LastNameTextBox & "'"
rst.Open txtSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

If Not rst.eof then
Msgbox "This name already exists do you want to continue",
vbExclamation+vbYesNo) =vbNO then Exit Sub
End if

Set rst =Nothing
end sub

Hope this helps

Bill
 
Back
Top