Advice & Form Design Issues

[mapster]

Greetings All, just started out leaning relational DB and access a few weeks ago, and seems that i need some pointers as i can not find any obvious answers (obvious to me).

If any one can answer any of the following questions i would be exceedingly happy. =)

Background: i am designing a TV episode database, basically as a learning exercise..

I have on the main form 7 sub forms as part of an overview/edit mode style. So i can have all the details on the screen at once.

Building them manually i have Master/child control links set correctly, and one sub form when "on_current" .requery the next (via VB), across the 7 subforms. I am *guess* this is how u should do it if u want to go no deeper than one sub form?? any advise on this tack?

Doing this i have ran into an issue... i have each table represented in a subform off the main form, i am having issues setting up one subform, like the rest, and it will not work without adding VB code.

Because the table AVSerialFileSpecifications has two other tables connecting to it with many relationships, i seem to be having trouble with connecting the Disk subfom behaving with the AVSeriesFileSpecificationQuery subform. Setting the Master/child links seem not to be having the desired affect. I kinda understand why the Disk form is not being updated with new records etc etc, and this can all be fixed with VB, checking the Disk table for a match, if non then create a new rec and drop in the value.

Is this the way designing the form i want should be done??? Seems like i am missing something in access that i have to code checks in for this.

AVSeriesEpisodeDetailsQuery form control,
Child Link SeasonKey
Master Link [AVSeriesSeasonDetailsQuery].form![SeriesKey]

on_current: Me.Parent![AVSeriesFileSpecificationQuery].Requery


AVSeriesFileSpecificationQuery form control,
Child link EpisodeKey
Master link [AVSeriesEpisodeDetailsQuery].form![episodeKey]

form on_current: Me.Parent![AVSeriesAudioDetails].Requery
Me.Parent![EditDisk].Requery


Disk **this way it will at least bring up the details of the Disk, but wont allow changes or additions**
Child Link DiskIdent
Master Link [AVSeriesFileSpecificationQuery].form![DiskIdent]


EditAVSeriesAudioDetails
Child Link SeriesKey
Master Link [EditAVSeriesFileSpecificationQuery].form![AVFileKey]

[Stewart Ross]

...Doing this i have ran into an issue... i have each table represented in a subform off the main form, i am having issues setting up one subform, like the rest, and it will not work without adding VB code. .... i seem to be having trouble with connecting the Disk subfom behaving with the AVSeriesFileSpecificationQuery subform.

Hi Mapster. You won't be able to do it the way you are trying to do so - with Disk as a subform - because as your relationship diagram shows the relationship as you have deifned it is the other way about. Your table called Disk is the master table on the one side of the one-to-many relationship with AVSpec. Subforms are always used for the detail (many side) tables linked from the master table.

If your relationship is correctly defined you will need to rethink your form structure (which might not be a bad idea with so many subforms).

As an aside, Access is generally pretty good at linking master-child fields between main forms and subforms - not sure why you would need to ripple updates in VBA code as you mention.

Hope this helps you in moving your project forward.

-Stewart

[mapster]

Thanks for the reply, initially i had the many side of the relationship in a subform but in doing this the form ended up being 6 deep, which seemed like like a bad idea, (maybe not??) and i found putting any code in on_current in the deepest subform ran multiple times.

Also i wanted to use a continuous forms and placing subforms in them didn't seem right.

As i mentioned i am rather a new to this, but not nesting the (many) Disk in a subform inside the AVFileSpec subform meant that it didn't link correctly, as reading the Master link from another subform worked ok, but when u modify/create an entry that the master is pointing to and the record does not exist in the child, then i have to create code to enter this record into the Disk subform as a new record.

Perhaps keeping the form one level deep is a bad idea for what i am doing, so 6 levels is the way to go if i want everything on the main form at once?

M.

[Stewart Ross]

HI again Mapster.

Subforms have a place; if you were designing a DVD rental system you could use a subform to show all rentals for each disk, for example. I have a feeling that you are using them when there are other, simpler solutions available.

I am wondering if you are using the subforms simply to display the linked data from the other tables, adding new data to these as you go? If you are using them to display the fields there is a far, far easier way to do so - which is to create a query that includes the fields from the master table and all relevant fields from the linked tables, being careful to take the key fields from the one-side tables and not the many-side tables (to avoid the query becoming read-only). Your edit form can then show fields from the many-side linked tables as well without using any subforms at all.

If you do go this route be careful in your edit form to set the properties of any control fields displaying values from your linked tables to enabled=no and locked =yes, to avoid accidentally changing values in the linked tables.

On the Disk relationship there is no getting away from the fact that this table is not on the many side of the relationship you are trying to link it to using a subform. When you specify parent and child field links think of these as one and many side links and you will see what I mean - Disk is being linked parent to child (one to many) to a table for which it is the parent (and for which the relationship is actually many to one). One way or another a re-think is necessary here.

I think it is a good thing that you have thought through the design of your database and identified and set proper relationships between the tables. The database will itself enforce the relationships you yourself have defined. It cannot update the Disk table when used in a subform the way you have tried, because it would break referential integrity rules if it did.

Many things can be done in code to get round restrictions that are built-in, but in my view this is not the answer here - you need to define what you want to see on your forms, design appropriate queries on the underlying tables to let you do so, and knit these together using what is likely to be a minimal amount of coding.

Hope your development goes well.

-Stewart