Check record exists code needed

[cath_hopes]

hi there,

I want to put some code behind a 'Delete' button on my Client form that will check whether an associated Booking record exists or not. If it does then then form will return the message:
"Cannot delete Client as associated Booking records exist."
The two tables Client and Booking both contain the linking field ClientID. My Client form is based on the Client table only. Can someone help me with the line(s) of code I'm looking for please?
Many Thanks in advance!

Catherine

 

[Rabbie]

Just have a query that gives you a count of bookings for a particular client. If the count is not 0 then you can display your error message

 

[dkinley]

Another alternative is DLookup. Suppose that your user presses the 'Delete' button you have fixed up. Behind that ...

   If Not IsNull(DLookup("[TableIDField]", "[tTableNameToLookup]", _
        "[FieldNameToLookup]= '" & Me.txtControlNameOnFormToCompare& "'")
        MsgBox "Cannot delete client as associated booking records exist.", _
        vbCritical, "Delete Disabled"
    End If

Note, the ' " .... ' " used in the comparison data is for strings. If you are looking up a number field it will be ...] = " & Me.txt....)

-dK

 

[Pat Hartman]

You should be using referential integrity to prevent the delete. You can then trap the error message that occurs if the user attempts to delete a record that has dependent records and so is not deletable.

 

[cath_hopes]

Thanks Pat Hartman, dkinley and Rabbie for your replies.

- I've used the DLookup solution and it works a treat.
- I do have referential integrity in place (Client has a one to may relationship with the Booking table) but using DLookup means I can use my own msgbox.
- Regarding the query solution - with this I could add the number of bookings into the msgbox giving the user more helpful information ....

Thanks again!
Catherine