The Cleaner

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:
<pre>
'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"
Else
MsgBox "Not a very common name"
End If



</pre>

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!

<pre>


Function Duplicate(SForm As Form, STable As String, SCol As String, SBox As Control) As Boolean

'FUNCTION OPERATION:
'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.GoToControl SBoxName
DoCmd.FindRecord Name, acEntire



If Me!SCol = Name Then
Duplicate = True
Else
Duplicate = False
End If


SForm.RecordSource = strOriginalRecordSource
SBox.ControlSource = strOriginalControlSource

End Function
</pre>

Thinker

Instead of Me!SCol try Me.Recordset.Fields(SCol).Value

I think therefore I am... sometimes right. [img]images/icons/wink.gif[/img]

anhmytran

Dim strVariable As String
strVariable = frmYourForm.txtYourText.Text

Create an instance of the Recordset object.
Set its source property to "SELECT Count(yourField) AS Counter FROM YourTable " & _
WHERE yourField = '" & strVariable & "';"
Open it.

bolDuplicate = False
If YourRecordset!Counter > 1 Then bolDuplicate = True

AnhMy_Tran<P ID="edit"><FONT class="small"><EM>Edited by anhmytran on 08/16/01 12:39 PM.</EM></FONT></P>

The Cleaner

Could you show me how to actually program those few lines that you just mentioned. I'm having some probs with it. I don't change recordsets like this often