Automatically Transfer Partial Record Information

[Thorson]

I am new to Access and working on setting up a database. I have 2 tables the
first one is tblBirthInformation, when a new animal is born it is entered in
this table; once a new animal is entered into tblBirthInformation I would
like the animal's IDNumber, BirthDate and BirthLocation to automatically be
entered into tblProtocolIndID. When the information is automatically entered
into tblProtocolIndID from tblBirthInformation I would like it to also add a
specified ProtocolNumber (this number will be the same for all new animals
from the tblBirthInformation table).

Is there a simple way to do this?

Thank you!

 

[Thorson]

The table you are suggesting I set up "tblAnimal" already is the same thing I
have set up in "tblBirthInformation". There are only 3 locations for
birthlocation and they already have a birthlocationID which is known by all
Access users and does not need to be listed in a seperate table.

Each animal can have more than one protocol Number, but each animal born
will always have protocol number 6091 assigned to it. Those are listed in a
seperate table: tblProtocolIndID which has the fields:
ProtocolNumber
AnimalID
DateAssigned
AnimalLocation

I would still like to know how I can automatically transfer animal records
from tblBirthInformation to tblProtocolIndID everytime a new animal is added
to tblBirthInformation. I would like each new animal's ID from AnimalID in
tblBirthInformation to transfer to AnimalID in tblProtocolIndID and the
Birthdate to transfer to the DateAssigned and the BirthLocation to transfer
to the AnimalLocation and each individual record that is transfered to
tblProtocolIndID from tblBirthInformation to have the ProtocolNumber 6091
assigned to it.

I would like to know if there is a way to automatically transfer this
information and add a specified protocol number to each record.

Thanks

 

[Thorson]

The Birth Location will never change, there are only 3 options they are in a
combo box, so I think it makes much more sense that they stay on the table
they are on, thanks for the suggestion though. I think I'm not communicating
how the birth location field is set-up very well.

As for creating a sub-form I don't think that is what I want, if I
understand correctly. The reason is that animals receive more than one
protocol number, but it will be at various times throughout their life. The
protocol table is also in a relationship with a table of master protocol
listings, this table lists the important information about each protocol. I
want to be able to access the tblProtocolNumber table independent of the
tblAnimalID or tblBirthInformation (whatever you want to call it).

I will keep trying out some of your suggestions and see if I can get
anything to work. If you or anyone else has any other ideas I appreciate
them. Otherwise, thanks for your time.

 

[Thorson]

I was trying your suggestion, how would you suggest I set up the "On Click
Event" of the button entered into the subform tblProtocolNumber? Use an
expression or a macro? I tried an expression but I couldn't get anything to
work and I have very little experience with Macros.
Thanks.

 

[Larry Linson]

Steve, did you get yourself into a logical quandry by not mentioning, early
on, that it is generally unwise, and unnormalized, to store duplicate
information in two tables? Maybe you ought to start over and find out some
details before you begin proposing answers.

Larry

 

[Thorson]

What I want to do with the tblBirthInformation and tblProtocolIndID doesn't
appear to be storing duplicate information, the only thing that is duplicated
is the Animal ID, which is what creates the relationship between the two
tables. Unless I am missing something here.

Thanks for the help.

 

[Thorson]

Those are actually the exact two tables I have set up for protocol Number.
I will try out your suggestions for the button and see what works. I would
still like it to automatically carry over the animal ID whenever a new animal
is added, but I don't think that is going to work out.

Thanks for your help.

 

[BruceM]

Even if all table relationships are correct, the Link Master and Link Child
properties of the subform control need to be set correctly. In my
experience this cannot be assumed.

 

[BruceM]

It should be apparent, but may not be to somebody who is unfamiliar with
forms/subforms in the first place. That's all I was trying to say.