How to test if delete successfull

[jpg250]

I have some code to delete a record from a table, which does not delete the record, presumably because of referential integrity settings. That is fine of course, that's what should happen. However I want to be able to notify the user if a delete did not succeed. How can I check this? I tried this code, but it did not return an error.

            strsql = "DELETE * FROM " & tblAddress & " WHERE GenAddressID = " & Me.tb_edtID
            Err.Clear
            CurrentDb.Execute (strsql)
            If Err.Number <> 0 Then
                MsgBox (Err.Description)
            End If

[pbaldy]

Look at RecordsAffected; you may need to use a variable for the CurrentDb.

[jpg250]

Thanks pbaldy, that was exactly what I was looking for. That works now. Do you also know where I might pull the appropriate message from if the delete could not be done - for example if I try to delete that same record by opening the table in datasheet view, Access gives me a nice message "The record cannot be deleted or changed because table 'Client' includes related records".

[pbaldy]

Well, what you have will probably fail silently on that type of thing. add the bit at the end

CurrentDb.Execute strsql, dbFailOnError

If that just gives you a call failed error, this may help:

http://support.microsoft.com/kb/161288

I haven't used that myself yet, but I was intending to try it.

[jpg250]

Thank you very much. I'll try it out and let you know how it went - maybe tomorrow as I'm busy the rest of today.

[jpg250]

I tried it. Adding the dbFailOnError gives me a run-time error, which I can catch and display it's message in a messagebox. Perfect!

[pbaldy]

Excellent! Sometimes you want it to fail silently, which I guess is why it's an optional parameter.