Data from one form field into multiple Tables.

[Miquel1]

Hi folks!
I'm trying to get Access to enter data from one form field to fields in multiple tables; I have a form in which all the ID information about animals is entered (NewAnimalsEntry) and the information then goes to a number of different tables (MainID, MainSex, MainTransponder, MainPlace, MainColony etc). Each of these tables are related using the field 'AnimalID' on each table. Referential integrity is enforced on them all. At the moment, if I enter all the relevant info into my form, all the relevant fields on all the tables are filled fine, except AnimalID, which is only inputted into the MainID table (this makes sense because the data source for this control is AnimalID in the MainID table). How can I get the AnimalID control in my form to fill out the AnimalID field on all the tables without having to put the AnimalID into the form repeatedly?

Thanks all!

Miquel

[rpeare]

Are you using a bound or unbound form? If you're using a bound form are all your 'sub' items in subforms or are you trying to do this on a single form with no subforms?

Is your animalID an autonumber or is it an internally designated number? if it's an autonumber (or other ID that's not subject to users changing) do you also have an internal animal number that they can see/add/modify?

If you're using an unbound form you could use this internal ID to look up the autonumber and us that in your append queries.

[Miquel1]

Hi,
I'm using a single form (I don't know if it's bound or unbound, although I did make it with the Form Wizard, if that tells you anything - newbie alert!) with no subforms. The AnimalID isn't an autonumber as it has to follow a specific format (XXF001 for female 1 in colony XX, for example).

Thanks!

[June7]

What are the relationships of these tables?

Can each animal have multiple transponders? I would think a transponder can be associated with only one animal? Will you remove a transponder from one animal and place it on another?

Can each animal have multiple colonies? Do animals change colonies?

What are MainSex and MainPlace tables for?

If these tables are correctly related, then use subforms to enter data to the related tables. The subform container control Master/Child links propertis will synchronize records between main and sub forms and will automatically save the AnimalID.

[rpeare]

I agree with june, you are going to have to use subforms (if you're new) to enforce referential integrity while entering data on multiple tables that all involve the same root item (in this case your animal). I would also recommend in your animal table you DO NOT use your internal numbering system (XXF001) as your unique key, make it an autonumber field but also record the internal number. Unique keys (as far as I'm concerned) should never be subject to data entry

[June7]

I do use a custom unique identifier as key but users do not enter the key. It is generated programmatically.

[rpeare]

Me too june but in the absence of evidence to the contrary in his post I'm assuming it's typed in, especially if he's using a form wizard to build his form.

[Miquel1]

Hi folks,
The primary table is MainID; the tables MainTransponder, MainColonyMembership, and MainExperiment have one-to-many relationships with MainID; the tables MainSex and MainPlace (records the area animals originated) are one-to-one. I'll have a play with subforms and I'll get back to you.

Thanks!

[June7]

Don't understand why MainSex and MainPlace are separate from MainID.

[Miquel1]

When I first started to build the database, most of the information was on one large table, so to make things easier (and on someone else's recommendation) I split them into a number of smaller tables related by AnimalID. As the database progressed, in order to keep a better track of where animals were going, how their ID marks were changing etc, many of the reports had to become one-to-many instead of one-to-one. The remaining one-to-one tables are a relic of that, I guess, but as I have a lot of queries and forms based on them it would be a bit of a nightmare to change them...
I tried to have a play with subforms, and I'm still pretty lost. Do I create subforms for secondary tables (ie Not MainID) and embed them in a form for which the control source is (the primary table) MainID and then link the AnimalID fields as child fields in these? I suspect I'm talking gibberish, but I'm really struggling to get my head around it....

[June7]

You are on the right track. Does this help http://office.microsoft.com/en-us/ac...010098674.aspx

[Miquel1]

Thanks guys - job done!