How to fill in only the required subform ?

[Bhat59]

I have a Database that has a Main form and three subforms Video, Audio and Photos. Main form and the other three Subforms are all linked with the autonumber in the Main form. Whenever I have all the three media, Video, Audio and Photos I fill in their data with their numbers. But if I have to fill in only the required data for either one or two of them how avoid the auto number being generated on the subform that doesn’t need to be filled. We used to do this earlier in our DOS database with a YES or NO field. Only a YES will prompt the data to be filled in the other related tables. I do have one each YES / NO field for Video, Audio and Photos in the main form in Access but will it do the same or which is the other way to enter data to the only required subform? Thank you.

[June7]

Why do you have 3 subforms? Three tables? Entry in one subform should not create a record in the other subforms.

[Bhat59]

Now that you asked me why I need so many tables, let me give the entire picture. This is for an archive of Audio Visual documentation centre. Video, Audio, Photos are collected along with other data directly from the field (mainly folk dances and rituals) and entered into this database. So far one centre here has successfully done this on a DOS based program. I am trying to do similarly in Access. I give below the tables I created. Tables are similar to DOS based program’s panels.

All tables are related to the main table. Performer, Collector and Project tables are related with 1-to-many type. Other tables are related with 1-to-1 type.

When Collectors go out to field they collect through Video, Audio or Photos(Sometimes simultaneously)

Eg. Genre term: Folk Dance; Item: Jogi ; Item type: Dance. Based on these all other data is collected and preserved in CD/DVD or Blu-ray Disks and entered in a database.

Here are the tables with their fields

Filed Card Main Table:
Accession ID(auto incrementing field); Accession Year; Project ID; Collector’s Name; Genre Term; Item name; item type; Performer’s Name; his Place; Accompanying performers(yes/No); Rec. Language; Rec. Context; Simultaneous collection; Video(Yes/No); Audio(Yes/No); Photos(Yes/No); Notes(yes/No)

Project Table:
Project ID; Date from, Date to; Project title; Project Director; Budget;
Project Description

Performer Table:
Performer; sex; Birth year; Caste; Education; Occupation; Birthplace; Mother Tongue; Address; taluk; District; State

Accompanying Performers Table:
Accession ID; No. Accompanied; Acc. Performers’ names

Collector’s table:
Collector’s name; Occupation; Address

Topography Table:
Accession ID; Recording Spot; Village Name; Taluk; District; State; Pin code; Date, time From; Date, time to

Video Table:
Accession ID; Disk No. From; Disk No. to; Disk type; Disk Rec. format; 2D/3D; Disk content

Audio Table:
Accession ID; Disk No. From; Disk No. To; Disk type; Disk Content

Photo Table:
Accession ID; Photo No. From; Photo No. To; Total Photos; Disk No.; Disk Type; Rec. Format; Resolution; Photo Description

Notes Table:
Accession ID; Notes Disk No.; Disk type; Notes Format; Language of Notes

[Bhat59]

I am sorry, I failed to put my question in the above thread. My question is how many forms can I have? I made a form for the FieldCard Main table and pulled in the Video, Audio, Photos & Notes into the same form and I see it is working fine. I don't know whether it is normal to have such a form as none of the sample databases I see are like this. I welcome advises and suggestions. Thankyou.

[June7]

Your first post indicated that unnecessary record was created but latest post says all works fine? What is the issue?

You can have as many subforms as you want, can even have subsubforms (up to 7 nested, I think).

Can a record have only one of the Yes/No fields selected? If yes, should be text field with choices.

Caste and Occupation seem to be totally irrelevant data. But if you need them why is Caste not in the Collectors table? What is taluk?

Also, recommend against spaces and special characters and punctuation in names. Instead of Collector's use Collectors.

[Bhat59]

Thank you for your suggestions. Sorry for the confusion. I am still trying to do the form/Subform arrangement. I will get back to you when I need.