Recordset blows up with error 3077 1

[MacroAlan]

I need to find out if a name exists in the table before inserting it. I do not want to have duplicates.

I wrote a piece of codde to put the field into a RecordSet and look for the name:

Sub FindDSname(setNum) [red]pass value to search[/red]
    Dim striXer As String, sroad As Long
    Dim RS      As Recordset
    Dim DB      As Database
    Dim strSQL  As String
    
    strSQL = "SELECT DataSetName FROM DataSets"   [red] 'Table[/red]
    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not RS.EOF
        RS.FindFirst setNum  [red]blows up here with 3077 Missing operator in expression[/red]
    
    If RS.NoMatch Then
        sqlString = InsertString & ", DateStamp, Userid ,SetName,CompanyName)" & ValueString & " #" & Now() & "# AS DateStamp, " _
            & Chr(34) & Environ("USERNAME") & Chr(34) & " AS Userid, " & DSN & " AS SetName, " _
            & Company & " AS CompanyName" _
            & " FROM tempImport;" [red]'write to table if don't exist[/red]
        DoCmd.RunSQL sqlString
        Exit Sub
    Else
        'Record Found
        MsgBox "That name already exists"
        Exit Sub
    End If
    Loop
    
    RS.Close

Am I even on the right path to the answer??

Alan

 

[PHV]

Why not simply this ?
If IsNull(DLookUp("DataSetName", "DataSets", "DataSetName='" & setNum & "'")) Then

Note: if DataSetName is defined as numeric in DataSets then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MacroAlan]

PERFECT!

Alan