Check for Record before INSERT 2

[MacroAlan]

I need to check to see if a record exists in a table before INSERT. Want to give the user a friendly message instead of cyrptic error.

Anybody got a scrap of code to do this?

Alan

 

[Golom]

There are several ways but I find the easiest is just to attempt the insert and then note the return values to see if it succeeded.

SQL = "INSERT INTO myTable (fld1, fld2, fld3) " & _
      "VALUES ('a', 2, 'x') "
db.Execute SQL
If db.RecordsAffected = 0 Then
   [COLOR=black cyan]' Duplicate Record Message[/color]
End If

 

[PWise]

dim mydb as Database
dim rst as recordset
set mydb = currentdb
set rst mydb.openrecordset("select count(*) from mytable where myfield =" & myvar & " myfield2 =" & myvar2... )

if not (rst.bof and rst.eof) then
   msgbox "record exist"

end if