08-16-2001, 11:00 AM
Check for duplicate values Dynamically?
Hi, I'm working with Access and I want a function that will check a specific column in a specific table for duplicates. Is there one built into Visual Basic? If so, what is it and how do you use it?
I have made my own, but the only part that doesn't work is the part in bold below. I want to be able to make a call to it by using a Form, Table, Specific Column in the table, and a control on the Form as arguements.
The column name is the part in bold below and is the only thing that doesn't work. If I manually type the Column name into the below function, i.e. replace the bold part below with Me!LastName (where "LastName" is the column to be searched for in the specified table) then everything works fine. I just don't want to have to type it in everytime into the function itself. I want to be able to pass the desired search column as a function arguement.
An example of how I want the function call to work would be:
'Arguement Discription Type
'Me - Form to be searched ('Me' for current) (form)
'"CommonNames" - Actual name of table to be searched (string)
'"LNAME" - Name of column in field to be searched (string)
'txtCommon - Name of control on form that holds the text to check (control)
If Duplicate(Me, "CommonNames", "LNAME", txtCommon) Then
MsgBox "Common Name"
MsgBox "Not a very common name"
I have tried to comment everything as well as possible.
I attached the original function for anybody who wants to edit it and return it to me.
Any and all help is appreciated!
Function Duplicate(SForm As Form, STable As String, SCol As String, SBox As Control) As Boolean
'Search any table field, in any table, in any form, for any duplicates. Return True if one is found, otherwise return False
'ARUGEMENTS for Duplicate Function:
'SForm As Form - Name of form to be searched (use 'Me' for current form)
'STable As String - Name of the Table to search (form's record source)
'SCol As String - Name of the column in the table to search for duplicate
'SBox As Control - Name of field on form that holds possible duplicate
If IsNull(SBox.Value) Then: Duplicate = False: Exit Function
Dim Name As String: Name = SBox.Value
Dim SBoxName As String: SBoxName = SBox.Name
Dim strOriginalRecordSource As String 'store the original RS of form
strOriginalRecordSource = SForm.RecordSource
Dim strOriginalControlSource As String 'store the original RS of control
strOriginalControlSource = SBox.ControlSource
SForm.RecordSource = STable 'change desired table
SBox.ControlSource = SCol 'change record sources
DoCmd.FindRecord Name, acEntire
If Me!SCol = Name Then
Duplicate = True
Duplicate = False
SForm.RecordSource = strOriginalRecordSource
SBox.ControlSource = strOriginalControlSource