OK Im stuck

[Jerry Stoner]

Ive got frmOrder with sfrmOrderDet witha sub sub form frmExtras.
Links are OrderID for sfrmOrderDet and OrderDetID for frmExtras. Refrential integrety is enforced with cascade uodate and delete enabled.

I enter data into frmOrder - move to frmOrderDet and enter data - no problem.

Now I go to frmExtras and enter information then click a Finished button(refresh) and the data just entered disappears from the form and the default setting appears.

I go to the Extras table and the data is there with no OrderDetailID, also the default OrderDetailID is there 5 times with no OrderDetailID and once with an OrderDetailID. Now get this, I go back to the form and reenter the data - click refresh and the data correctly goes to the table with the correct OrderDetailID!!!


What the heck is going on here.

Edit, just noticed if I enter 2 records into frmExtras I get 5 orphaned default settings - the first entry is also orphaned but the second record is correctky linked with OrderDetailID???

 

[Jerry Stoner]

Fixed but why

I set the DetailOrderID using code instead of relying on form links which by the way were correct and it works now, can anyone shed some light on why this might have happened?

 

[Alexandre]

This is likely to be something wrong with the way you set up your parent/child fileds. Eventually send me your DB (zipped) and I'll have a look.

 

[Jerry Stoner]

I used the profile feature to ask for youre-mail address so I can send you the copy, this is just a backup. What address should I send to and what version should I send it in?
Thanks for your time.

 

[Alexandre]

Jerry,

This is what I've done: I enforced relational integrity where needed, cleaning the data that did not comply with R.I. where required. I used the lookup feature of Access table fields to re-setup all fields taking their values from lookup takbles (like TblTax). Results in cleaner, more consistent and robust design.

However, this was probably not the origin of your problem. I believe it had to do with the many default values that you left or put in your tables fields. The rule here should be: clear the default value property if none is REALLY needed. If one is really necessary be carefull that those default values do not get committed unwillingly: they are basically like user-entered values. If anything is done that updates the current record, they get committed. This is what happened with your 'finish' button and under a few others occasions, resulting in uncomplete, unconsistent (R.I. was not enforced) records in your DB.

I believe that this particular pb should be over now. I saw a few others, but, well, this is still under construction isn't it?

I'll send you back your db by email.

 

[Jerry Stoner]

Thanks

Yes it is still (obviously) under construction. I see your point about default values and will forgo their use. However I do have a question about RI: I usually enforce RI on tables such as Order to orderdetail but dont on lookup tables such as ingrediants. This was the case in the db I sent. Am I wrong not to use RI on all tables? Guess I assumed RI was essential only when records would be orphaned. Anyone care to comment?

Thanks very much for the second set of eyes Alexandre, you were very helpfull.

 

[Pat Hartman]

I use RI on ALL related tables.

In hierarchical relationships such as Order to OrderDetails, I also specify Cascade Delete. I almost never use Cascade Update since many of my primary keys are autonumbers and can't be changed anyway so Cascade Update would never be triggered. For the non-autonumber keys, I simply don't let the user change them once they are entered.

RI is just as important for lookup table values as it is for hierarchical relationships since those are the code fields that will normally be used for analysis purposes. If you allow bad data to slip into these fields because you have not enforced RI, you are compromising the integrity of the data in the database and could potentially cause your clients to make poor business decisisions based on that bad data.

 

[Alexandre]

Well, Pat would be better than me at explainig this, but:
With very rare exceptions, RI as well as cascading update should be enforced to preserve the consistency of data and of the relations between them.
However, for lookup tables you generally would not activate cascading delete since you would not want the suppression of a category to cause the deletion of all related records.

 

[Jerry Stoner]

Thank you both

While you were both posting I was searching the forum and just now came back to say I get it now, always use RI but cascade delete only when appropriate.Thanks to two heavyweights for helping me understand a flaw in my basic approach to db design.

 

[Alexandre]

I was considering to make use of the adress and phone number that you gently provided to me along with your sample DB (didn't it ever occur to you that I could be a bad guy ? )
But now that you've called both me an Pat 'heavyweights' I am not anymore very much sure about the integrity of our relationships...

 

[Jerry Stoner]

oops

Didnt intend to send that info, its there for my potential client when I deliver the demo. Guess I'd better be more carefull . "Heavyweight" IS a complement BTW!

Signed.
"a humble featherweight"

 

[The_Doc_Man]

Let me add a comment on RI.

I don't always use RI on lookup tables, but that occurs because of my environment. Pat & Alexandre are correct to say you just about always use it. I'll give you an example of a case where I can't use RI when I want it.

I am at a military site. My users (military clerks and some contractors) have to add information to our network tracking tables on the spot. The first time I used RI on a field, they told me flat out that they couldn't afford the time to stop and enter the data used for validation. It was up to me to come back later and fix it. Darned if the government supervisor didn't back them up. (Hence my tag line. Yeah, I know. I've got ugly customers - but it's a living. On the other hand, it's what I get for being good at cleanup of other people's messes.)

Anyway, the issue is that if you use a combo box that is limited to list values, or if you apply RI (same general effect), sometimes customers cannot enter the data they need to enter when they need to enter it. But they often have reams of data to enter and cannot be held back by validation problems (in certain fields only). The primary situation that comes to mind is when a new project stands up, we don't have all the data for the project, including project points of contact and certain charge codes. But the process we use requires that we check in the stuff the moment it hits the machine room floor and is turned on with network connections.

The key is that we can enter the computer data but cannot run the summary reports until the validation data gets entered. Fortunately for us, the summary reports are on a slow cycle. The paperwork for establishment of the new project to the validation table might be en route. And despite the fact that we have e-mail and other ways to share data, the truth is that a combination of obscure government regulations and rice-bowl politics drives our environment. (Keep your chopsticks out of my rice bowl!) So I had to turn off RI for the customer, who wanted the ability to enter something that couldn't be validated yet.

This of course necessitates the creation of a query and report that searches for discrepancies in the validation tables that don't have RI or "limit to list" options. So this is what you face if you DON'T or CAN'T use RI or "limit to list" options. This is WHY you really want to have RI or "limit to list" turn on most of the time.

Pardon me, I hear a mop and bucket calling me...

 

[Jerry Stoner]

Thanks DocMan

I feel for you, having spent 10 years in the Navy I'm real familiar with the type situation you described. Aint politics wonderfull.
Promise Ill be using RI on all tables in the future and am now going back to update all my working DB'S. The good news is Ive found no integrety problems yet. Think I got real lucky.