check table for certain records before deleting record

[qwertybob]

check table for records before deleting record from second table

i hate trying to think of titles that make sence

Anyway i have two tables tblCustomer and tblRental using a one to many relationship - no referential integrity.

i want to delete a record from tblCustomer (which i can do fine) but my problem is i want to first check there are no records in tblRental with the same Customer ID as the tblCustomer record i wish to delete and that a 2nd field in tblRental isnt equal to "Open"

i have no idea how to go about this, any help would be greatly appreciated.

 

[The_Doc_Man]

Look up function DCount.

The criteria string (3rd arg of DCount) looks just like a WHERE clause with the word WHERE removed.

If you get the DCount right, you would only allow the delete when the count returned by the function = 0.

By the way, is there a reason you DON'T want to turn on Referential Integrity? 'cause it would do the same thing but faster...

 

[qwertybob]

i want to keep 'Closed' rentals in tblRentals because other parts of the database use both 'Open' and 'Closed' and its easier if old and new rentals are in the same table.

I will give your suggestion a shot - sounds alot simpler than my botch job - using a hidden subform based on a select query, and using RecordsetClone.RecordCount

cheers

edit: yes works great thanx

 

[neileg]

Is there any reason you need to delete the customer record? I would have thought that flagging the record as obsolete would have been better.

 

[qwertybob]

in order comply with the DPA - not keeping personal data longer than is neccesary.

 

[neileg]

Mmm, OK. I would have thought you would need to keep the customer data for 6 years anyway to satisfy the tax authorities (in the UK anyway).

 

[Rich]

Isn't it longer for vat records?