referential integrity.....using code?

[JCross]

hi all - i can't use the 'enforce referential integrity' feature of the relationships in this case, but i need to check and see if there are 'details' for an item before allowing the user to delete it. the item info is on my mainform, the details are listed on the subform. so - if there are details for an item, i want a message box.

any ideas?

Thank you!

Jennifer

 

[JCross]

(is this awful?) because i am using one details table to hold the details from three items tables.

Jennifer

[This message has been edited by JCross (edited 03-27-2002).]

 

[JCross]

That's the way I had it - but 'they' insist on three levels of this item ...and my queries are set up to run through each of 6 different types of items in a specific order to perform calculations. I didn't think I could apply the order if I had P1, P2, P3 all in the same items table. can i? have a query run through all the P1's first, then P2's....etc?

thank you Pat

Jennifer

[This message has been edited by JCross (edited 03-27-2002).]

 

[JCross]

Pat - should I just make two more details tables to deal with the two extra levels of that item? it's seeming like the simplest way to go..........

Jennifer

 

[JCross]

Thanks Pat -

The application I'm developing (or trying to) is for a fast food chain. It allows them to keep track of their recipes, vendors, invoices, inventory, and daily sales. The table problems I've been having are to do with the levels of 'ingredients' they use, and calculating the cost of those ingredients. There are:

Ingredients
Level 1 Prep
Level 2 Prep
Level 3 Prep
Recipe
Menu

and the hierarchy is in that order. thus, Level 2 Prep Items may be created from Ingredients and Level 1 Prep Items.....etc.

Unfortunately, they didn't decide what Items they needed until recently when I thought all my table design was already complete. Each Item above has an Item table and a Details table.

THE QUESTION

Is there a good reason to have fewer tables? To use the least Items and Details tables possible, though each having their own seems to work fine?

Thank you! I know this was long.....

Jennifer

 

[ZanyJanie]

Jennifer,

To expand on Pat's idea ... after counting the number of records, if the count is greater than 0, display a message box with Yes No buttons. In the message text, tell the user that there are child records and ask if he/she wants to delete them now.

If the user answers "yes" execute a delete query to delete the child records. If "No", just cancel the delete event.

Jane

[This message has been edited by ZanyJanie (edited 03-28-2002).]