cascading values?

[Guest]

I have a form with several subform tabs in it (and a couple of these have
subforms within them). Each goes to a separate table, but the main forms and
subforms are linked by DATE (PK in the main form). The 'main' subforms all
have a field in common, SESSION. Is there a way to link all of these fields
'horizontally' in the subforms so that if a value is entered in the first
subform, it's populated through the others? This field SESSION is not in the
main form (there are usually 8 sessions per DATE). Would I have to make the
SESSION a PK in one of the tables and link it to the others?

Just looking for a way to reduce errors if folks have to enter the same
value over and over on each tab.

Thanks!
C

 

[Amy Blankenship]

You should probably, instead, have a session subform embedded in the main
form, then have the tab control in that form. You should never use Date as
a field name, as it is a reserved word. I think there may be issues with
having a date data type as a primary key, but off the top of my head I
couldn't say what they are. If I were you, though, I'd add an autonumber to
serve as the primary key instead.

If nothing else, if you make a mistake entering the date, your current data
structure will not allow you to fix it!

HTH;

Amy

 

[Guest]

Hi Amy

Hmmm... I understand your point. But if we want the date to cascade down to
the subforms also (to prevent data entry error) is there a way to do that
without it being the PK?

Also, what do you mean by the tab control? (sorry, rookie)

Thanks!
Christine

 

[Amy Blankenship]

Hi Amy

Hmmm... I understand your point. But if we want the date to cascade down
to
the subforms also (to prevent data entry error) is there a way to do that
without it being the PK?

If you use an autonumber PK that is associated with the date, then there
won't be any data entry errors, because the date will only ever exist in the
main table and will simply be referenced in the other table (if you know the
key, you know the date). This is what data normalization is all about.

Also, what do you mean by the tab control? (sorry, rookie)

You said you had a form with several subform tabs in it. I assumed you were
using a tab control to make it easy to show and hide the subforms. So I
guess it depends on what YOU meant by "subform tabs." So what DID you mean?

-Amy

 

[Guest]

Yep that is what I meant, but maybe I'm thinking about this the wrong way. I
was looking to have a field in each subform that was populated when its
related field was entered in the mainform (ie enter it once). But I guess
having the autonumber PK would solve having to have multiples of the same
fields (like date or session).

Is there any reason to link tables with the same field that are not in a
PK/FK relationship, or should you never duplicate a field (even if it's only
entered once)? Hope that's not too vague a question...

Thanks!
Christine

 

[Amy Blankenship]

Yep that is what I meant, but maybe I'm thinking about this the wrong way.
I
was looking to have a field in each subform that was populated when its
related field was entered in the mainform (ie enter it once). But I guess
having the autonumber PK would solve having to have multiples of the same
fields (like date or session).

Is there any reason to link tables with the same field that are not in a
PK/FK relationship, or should you never duplicate a field (even if it's
only
entered once)? Hope that's not too vague a question...

Sometimes it is useful to link tables on a field that is not the primary
key. This is probably not one of those times .

The issue I have with using meaningful data to form a link is that once
you've done that the information can never be corrected if you make a
mistake (as long as you define the relationship properly in the
relationships window). I did this once early in my career and it was a
complete mess!

HTH;

Amy