Puzzled Form/subform issue

[vaiamo]

Hello-

I'm puzzled. I'm trying to have a data entry form with a simple form and subform layout. The table that the main form is based on has one field, an autonumber field (call it animalid) (it serves as a cataloging table- assigning a unique id and is related to several tables) and its related to the table that the subform is based on in a one to many relationship with the animalid field as the foreign key- referential integrity established.

The problem: When I go to enter data, I tab through the animalid field (main form) to enter data into the subform expecting that as soon as I enter data in the subform and create a related record in the subform table a record will be created in the main form table. This is not happening. My question is why and how do I fix it? There are no other fields that would go into the mainform table.

I feel like I've done a poor job explaining this but hopefully you get the drift.

 

[jgc31]

If understand you correctly what you are trying to do is create a record that relates back to another record that doesn't yet exist which clearly is a bit of a problem. The record in the table related to the main form must be created first before the records in the sub form can be created.

 

[wazz]

"The table that the main form is based on has one field, an autonumber field (call it animalid) (it serves as a cataloging table- assigning a unique id and is related to several tables)..."

You're relating things back to only a number. Why? You should be relating your data back to something meaningful. Include some data in the main table that are pertinent to the animal (i presume).

 

[vaiamo]

Yes I see how its not really puzzling at all that this problem is occurring.

I guess it goes back to my design but I have gone over it and over it a million times and even posted questions regarding it on this forum and this design seems like the only way to work it.

Basically the MainForm table is a linking table which links three tables and tracks an animal through 3 tables which hold disparate pieces of information about each animal.

The only animals that get i.d.'s are those that are caught and tagged. One animal may be caught several times and it may be tagged several times but it may not be re-tagged every time it is caught. So this mainform table containing animalid is linked to a table containing capture info and a table containing tag and tattoo numbers. The information collected on the animal when caught that is recorded in the capture table changes over time except for the sex of the animal.

The third table this mainform table is linked to is a kill table and obviously an animal can only be killed once so this might seem an excellent place to store an animalid. However we do not have (and there is no way to have) mortality records for all animals that are caught and tagged. And since not every animal in the database gets an animalid it does not make sense to store any information about the animal in the mainform table.

But now that I have just gone through all that I believe both of you are saying that my design has to change -- that there is no way to just have a table of tracking tags to follow and link an animal through several more meaningful tables. Dangit.

Thanks for your help anyway!

stephanie

 

[wazz]

The information collected on the animal when caught that is recorded in the capture table changes over time except for the sex of the animal.

your main table might include the sex of the animal, the name (if you give the animals names), and the species won't change. there might not be much, but it does seem (to me) that the animal is the main table. (a tattoo won't change will it? that could be included here. same with the tag unless they change with each capture).

The only animals that get i.d.'s are those that are caught and tagged. One animal may be caught several times and it may be tagged several times but it may not be re-tagged every time it is caught.

Animal to Capture (1:N) you might do it differently since they're not always tagged but it might do.

The third table this mainform table is linked to is a kill table and obviously an animal can only be killed once so this might seem an excellent place to store an animalid. However we do not have (and there is no way to have) mortality records for all animals that are caught and tagged. And since not every animal in the database gets an animalid it does not make sense to store any information about the animal in the mainform table.

you could do a 1:1 between animal and kill (death) if there are special fields you must add about the animal's death. otherwise a date of death in the animal table and a perhaps a 'cause of death' lookup.

good luck. i hope there's something new here for you.