Referential Integrity - Catching the error (1 Viewer)

[dom_donald]

Hi,

I've searched the forums but can't find the specific answer I'm looking for - so i apologise if someone else can find it!

I've got a db with various relationships that enforce referential integrity. It works fine.. until the point where I try to delete a record from a table using VB code (Recordset.Delete method).

Quite rightly i'm prevented from deleting a particular record, and the default error box is displayed, but this is (a) not meaningful to the average user and (b) the options on the box by default are 'end' and 'debug'.

I would like to trap the error and display a programmer-defined msgbox instead.

I've tried using the 'on error' block to trap the error number, but the automatic error seems to occur before this point. The last thing I want to have to do is trawl around the database checking for related records in the code, before attempting to delete a record.

Help!

 

[Mile-O]

Forms have an OnError event which you can use to trap this sort of message.

 

[dom_donald]

Forms have an OnError event which you can use to trap this sort of message.

thanks.. not sure entirely how to use this in my case.

I have a sub-form in datasheet view. The main form has a button which deletes the selected record from the subform when pressed.

I've tried putting the onError event for both the form and the subform, but this makes no difference apparently. Any further ideas/clarification happily received.

cheers

 

[Mile-O]

In the form's OnError, put the following VBA code:

MsgBox DataErr

Now run it, you should get a number (probably four digits).

Now, delete the line above and replace it with:

If DataErr = ???? Then
    MsgBox "Put your message here", vbExclamation
    Response = acDataErrContinue
End If

Replace ???? with the error number given by the original messagebox.

 

[dom_donald]

yeah, I understand what you're saying, but I've put this code (or similar) in the OnError event for both the form and the subform. However, it's the VB code that's deleting a record from a recordset derived from the appropriate table.

Set rst = dbs.OpenRecordset("SELECT * FROM Translators WHERE Id = " & Selected_Translator_Id & ";")
rst.Delete

fails on the delete method but doesn't jump to the error label, nor does it jump to the onError event in either form

maybe there's a better way of doing this anyway *sniff*

 

[Mile-O]

Put Selected_Translator_Id within square brackets: [Selected_Translator_Id]

 

[dom_donald]

Could do.. it's only a string, not a field. Don't think this affects the original problem

 

[Mile-O]

I know, it's just that dodgy names like that can affect certain things.

Anyway, do you have an small example of your problem to look at?

 

[dom_donald]

hi,

open the FT_Summaries form and go to the 'Translators' tab. Try selecting a row using the record selector then clicking the trashcan button. The system displays an error (which is correct) but i want to catch this error myself to display something sensible.

Admittedly my code's a bit dodgy because i only started teaching myself a few days ago and i don't have much time

thanks

 

[Mile-O]

Open the relationships window, find the relationship between Translators and Banks. Double click the line and select Cascade Delete. That's all.

 

[dom_donald]

i'd love to But I don't want to permit the deletes under those circumstances i.e. I don't want to cascade deletes.

Sorry for being a PITA

 

[Mile-O]

In that case, why not do what I do.

In each table I put an extra field called DateExpired. I set it's default to #31/12/2200#

I base all my forms on queries, with the DateExpired field's criteria set to >Date()

Rather than delete a record I update the DateExpired field to Date() and requery. Therefore it's all hidden from the user but kept for other purposes.

You can't delete fields unless they have no related records due to enforcing referential integrity.

 

[dom_donald]

that would certainly be a workaround I'll consider, so thanks

BUt I can't help being slightly surprised there's not an obvious way of trapping these expected errors So i'll just go away and cry for a bit

 

[Mile-O]

This code works fine on your delete button:

Private Sub Delete_Btn_Click()
    On Error GoTo Err_Delete_Btn_Click

    Dim Choice As Integer
    
    Choice = MsgBox("Are you sure?", vbYesNo, "Delete Bank Details")

    If G_Selected_Translator_Id <> -1 And Choice = 6 Then
   
      'delete selected row from Bank Table
       Dim dbs As Database
       Set dbs = CurrentDb
       Dim Rst As Recordset
       Set Rst = dbs.OpenRecordset("SELECT * FROM Translators WHERE Id = " & G_Selected_Translator_Id & ";")
       Rst.Delete

       'requery
       Me.SF_Translator_Summary.Requery
   
   Else
      MsgBox "Please select a Row that you wish to delete"
   End If
   
   G_Selected_Translator_Id = -1
   
Exit_Delete_Btn_Click:
   Exit Sub
   
Err_Delete_Btn_Click:
    If Err.Number = 3200 Then
        MsgBox "You can't delete this record.", vbExclamation, "Delete restricted"
    End If
    Resume Exit_Delete_Btn_Click

End Sub

 

[dom_donald]

thanks for looking at this - I've just pasted that code in (in fact, i'd tried exactly that before posting here, expecting that to be the solution) but it didn't work for me. I don't get the programmer-specified msgbox, just the system one.

Does this work on, say, deleting translator 35 "Doogle12" for you?

 

[dom_donald]

This is what i get trying to delete that record with your code pasted in (see attached)