Hello brains trust!
I'm just starting to dip my toe into VBA (huge learning curve!) to sort a couple of problems with my db and went looking for a way to prevent our team entering a duplicate client (it's happening a lot!). I found this: http://www.iaccessworld.com/prevent-...ry-two-fields/ and have had a go at trying to set this up without luck.
The two fields in my 'add new client' form are First name and Surname. I have the 'afterupdate' action on the Surname field as I assumed it would trigger after adding first name then surname.
This is where I've got to - nothing happens at all - no error, just nothing. No doubt I've mucked up some crucial component!
Ideally all I want is for the db to check the provided first name and surname combination and send a msg when it finds a repeat. We often have clients with the same name so I don't want it to do anything else other than warn the person that they could be entereing a duplicate client and to double check. I could combine with birth date to help with this but sometimes dates go in wrong so I thikn I'd just prefer that peeps get a msg when its a repeat name so they can check.
Any help would be greatly appreciated! I need newbie speak - sorry in advance! lol
Private Sub Surname_AfterUpdate()
Dim NewCustomer, NewFirstName, NewSurname As String
Dim stLinkCriteria As String
Dim custNo As Integer
NewFirstName = Me.[First Name].Value
NewSurname = Me.Surname.Value
stLinkCriteria = "[First name] = " & "'" & NewCustomer & "' and [Surname] = " & "'" & NewSurname & "'"
If Me.[First Name] = DLookup("[First name]", "Clients", stLinkCriteria) Then
MsgBox "This client name, " & NewCustomer & ", has already been entered in database." _
& vbCr & vbCr & "with that first name " & NewSurname & "" _
& vbCr & vbCr & "Please check client is not already recorded.", vbInformation, "Possible duplicate information"
custNo = DLookup("[Client Id]", "Clients", stLinkCriteria)
Me.DataEntry = False
DoCmd.FindRecord custNo, , , , , acCurrent