Checking if values already exist in table

[doobybug]

I have to fields refNo (String) and Volume (Integer) and would like to check if the combination of these 2 fields are already inputted in a table, before I actually save them. If they are already there I don't want to duplicate the record.



This is giving me a type mismatch. Any ideas why?

If IsNull(DLookup(("[NotaryRefNo]" And [Volume]), "[tblNotaryIndex]", ("[NotaryRefNo]='" & refNo & "'" And "[Volume] = " & Volume & ""))) Then                MsgBox "Record exists"
            End If
            
            sql = "INSERT INTO tblNotaryIndex([NotaryRefNo], [Volume], [Date Start], [Date End], [Condition], [Publication], [Publication Link], [ShelfId]) " & _
            "VALUES('" & refNo & "'," & Volume & ",#" & dateStart & "#,#" & dateEnd & "#, '" & condition & "', '" & publication & "','" & filePath & "','" & shelf & "')"

[aytee111]

DLookup("anyfieldname on table","tblNotaryIndex","NotaryRefNo='" & refno & "' AND Volume=" & volume)

Watch the use of parentheses - ( - too many
Square brackets are optional - only required when field name has spaces or special characters
Remove the two quotes at the end. Because Volume is a number field the ending quote is not required

I add a variable to the sub and do: Variable=Dlookup(...). It makes it easier to debug. The result of the DLookup is not always null, sometimes it is blank, sometimes it is zero. I can check it using debug before adding it in to the IF statement.

[doobybug]

thanks solved!