Simple Duplicate Record Check 1

[redtoad]

I am looking for a simple duplicate check function. User should not be allowed to add record with same first name, last name and address. They are allowed to add record with same first name and last name and a different address. Thanks.

 

[TerpFan2001]

I assumed the recordset was not currently available somewhere and it had to be created.

Public Function ClientExists(sFName As String, sLName as String, sAddress as String, sConnectionString as String) As Boolean

'Determine if the specified Client already exists in the database

Dim sSQL as String
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

'Set ClientExists to False

ClientExists = False

'Open Connection

Set cn = New ADODB.Connection
cn.ConnectionString = sConnectionString

cn.Open


'Create SQL Statement

sSQL = "SELECT * FROM [tblTableName] WHERE [LastName] = '" & sLName & "' AND [FirstName] = '" & sFName & "' AND [Address] = '" & sAddress & "'"


'Open recordset

Set rs = New ADODB.Recordset
With rs

.ActiveConnection = cn
.Source = sSQL
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open

'If a match is found the recordsets recordcount will be greater than 0

If .RecordCount > 0 Then

ClientExists = True

End If

.Close

End With

'Close recordset and connection

Set rs = Nothing

cn.Close

set cn = Nothing

End Function

 

[redtoad]

I think that's going to work, but I'm having trouble calling the function from a sub. I keep getting Argument not optional error message. Any thoughts would be appreciated. Thanks

 

[TerpFan2001]

It sounds like you are leaving out one of the variables that are required by the function. Make sure you passing all four(First Name, Last Name, Address and the Connection String for the database) variables to the function.

 

[redtoad]

I figured it out. I'd explain what worked exactly but I've been working on this project non stop for a week and my brain is fried. The dupe check function was exactly what I was looking for. Thanks for all your help!

Fear the Turtle!