db.execute problem

[kimsMTC]

I'm using db.execute to insert a record into a table. However, if there is a duplicate record, how do I check it? I have the dbFailOnError, but I don't know how to check it. Here's my code:

Set db = CurrentDb

strSQL = "Insert into [Time] (EmployeeID, ProjectID, WorkDate, [Hours]) values (" & EmployeeID.Value & "," & ProjectID.Value & ",#" & WorkDate.Value & "#," & Hours.Value & ")"

db.Execute strSQL, dbFailOnError

If db.RecordsAffected = 0 Then
MsgBox ("Record could not added to the Timesheet")
Exit Sub
End If

Set db = Nothing

Thanks!

Kim

 

[jrbarnett]

What's the primary key of this table?

The easiest way is to check for a duplicate value on the primary key before running the insert, something like:

If DCount ("*", "[time]", "Employeeid=" & Employeeid & " AND ProjectID=" & ProjectID & " AND WorkDate=#" & WorkDate & "#") = 0 Then
 ' Value for employee / project /workdate not present so insert
Else
 ' data already present, display error message
End If

John

 

[kimsMTC]

So there's not a way to capture the duplicate error message before it is displayed to the user in order to make the message a "friendly" one?

Thanks,

Kim

 

[PHV]

The goal, as John tried to explain you, is to NOT get a duplicate error message ...

 

[kimsMTC]

I understand what he was describing, but that's an extra hit to the database. I've always coded where you capture the messages and display a friendly message to the user, then rollback the problem for the user without them knowing.

Thanks for the tip.

Kim

 

[maxhugen]

A duplicate value should raise Err 3022. If you trap for that err after "db.Execute strSQL, dbFailOnError", you can take appropriate action.

Max Hugen
Australia

 

[kimsMTC]

Thank you maxhugen, this is what I'm wanting to do. But I'm not finding code that shows me how to trap that err. Can you please help with that?

Thanks,

Kim

 

[VRoscioli]

For information on error handling in general, see: faq707-5253

In your error handler, simply check if Err.Number = 3022. If so, take appropriate action, and otherwise fail.


-V

 

[kimsMTC]

Thank you VRoscioli! You have all been very helpful! This will fix my problem.

Have a great day!

Kim