Access VBA - Input Form Detect Duplicate and then Display Duplicate Record

Question

What I Have:

I have an Access input form, the user fills in data in the form, pushes a button which triggers a VBA script which checks if the input telephone number in the [Telephone] control matches an entry in the table. If there's a duplicate it triggers a MsgBox, if not it commits the data to the table. This works and it's great. Here is the script:

Option Compare Database

Private Sub buttonNewRecord_Click()

Dim ErrorInt As Integer
Dim TeleCheck As Variant

TeleCheck = DLookup("[Telephone]", "tblLog", "[Telephone] = '" & Me.Telephone & "'")
If Not IsNull(TeleCheck) Then
MsgBox "Telephone number already exists in the database!"
ErrorInt = ErrorInt + 1
End If

If ErrorInt < 1 Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Record Added!"
End If

End Sub

What I Am Trying To Do:

In the table, along with [Telephone], there is a [Date] and [Customer_Name] field. When a duplicate telephone number is detected, I would like the triggered MsgBox to display the [Date] and [Customer_Name] of the record where the duplicate was found. Like:

MsgBox "Telephone number already exists for [Customer_Name] added on [Date]!"

What I Have Tried:

Not a lot because I'm not sure what to try. Googling provides a lot of different ways to detect a duplicate but I have found no one trying to return any data from the record of the duplicate found.

Why not add a unique constraint for that field in the table? That'll prevent duplicates without having to check for it explicitly. You can catch the error when a duplicate is attempted to be added, and just display a message box. — Ryan Wildry
– Ryan Wildry, Commented Mar 10, 2017 at 14:47
Because I found capturing the error to be far more trouble than it was worth. I also have additional error handling and constraints in the VBA that I have no listed here because they are not relevant to the issue. — The Ramen Within
– The Ramen Within, Commented Mar 13, 2017 at 2:22
Sorry but this is really not relevant to the issue... I have no problem detecting the error, the issue is including additional information about the duplicate record in the error message. If you really want to know, you can read the thread I made about it. stackoverflow.com/questions/42220010/… — The Ramen Within
– The Ramen Within, Commented Mar 13, 2017 at 2:41
Use a recordset to return the fields you are interested in when the telephone number already exists — Ryan Wildry
– Ryan Wildry, Commented Mar 13, 2017 at 3:25

Rick · Accepted Answer · 2017-03-10 07:41:06Z

1

Dim cstmerName as String
Dim dateAdded as String

Let cstmerName = DLookup("[Customer_Name]", "tblLog", "[Telephone] = '" & Me.Telephone & "'")
Let dateAdded = CStr(DLookup("[Date]", "tblLog", "[Telephone] = '" & Me.Telephone & "'"))

MsgBox "Telephone number already exists for " & cstmerName & " added on " & dateAdded & "!"

answered Mar 10, 2017 at 7:41

Rick

3222 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access VBA - Input Form Detect Duplicate and then Display Duplicate Record

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related