New entry in one table, creates entry in another

[Travis]

I am doing a database which keeps track of assets owned by people,
companies and trusts.

Each person has an autonumber Person ID.
Each company has an autnumber Company ID
Each trust has an autonumber Trust ID

The information I require about these three types of "entity" are
different, so I've got a person table, a company table and a trust
table.

There is another table which has the asset information.

Every asset is owned by an entity and hence the asset table has Asset
ID owned by Entity ID.

Another table has the primary key Entity ID and the second column is
"Type" which could be "Person" "Company" or "Trust" and a third column
which has the Person ID, Company ID or Trust ID.

Obviously I can assign a person, company or trust an Entity ID with
autonumber and manually enter the information in the second and third
columns, but that's a waste of time.

How could I set it up so that every time I create a new person, company
or trust these get added to the other table and assigned an Entity ID
automatically?

Thanks in advance for your help.

Travis

 

[Katrina]

Run an append query in the afterinsert event procedure of the form(s) you
are using to add the Person, Company and Trust. Append the type(Person,
Company, or trust) and the ID of the newly added record.

HTH
Katrina

 

[Travis]

Run an append query in the afterinsert event procedure of the form(s) you
are using to add the Person, Company and Trust. Append the type(Person,
Company, or trust) and the ID of the newly added record.

Thanks Katrina,

Now at risk of sounding dumb, how do I implement this?

I've created an append query for each of the entity types. It seems to
do what its supposed to when I run query (e.g. the entities get added
to the EntityIndex table). Then I go to the form, right click, select
properties, after insert [event procedure] and then I click on the
three dots. Then a VBA window opens up saying:

"Option Compare Database

Private Sub Form_AfterInsert()

End Sub"

I've been doing Access for not much more than a fortnight and my VBA
skills at this point go not very far beyond cutting and pasting, though
I can understand the logic of code and follow it when I see it. So
bearing in mind my desperate newbie status...

What do I do in the VBA window to tell it to run the query?

Thanks.

Travis

 

[Travis]

I think I've figured out the VBA,

DoCmd.OpenQuery "queryname"

But the records being appended to my tblEntityIndex table are all
wrong.

I'm only talking about the results of adding a new trust below, but the
same thing happens with other entities. (Except there are hundreds of
people, so huge numbers of records get created. I'm using the trusts
as an example because there aren't many of them.)

There are six trusts in the Trust table, TrustIDs are 1,2,4,5,6,7
(Three was deleted)

But the records appended to tblEntityIndex are as follows:

EntityID Type SubtypeID
628 Trust 1
629 Trust 1
630 Trust 1
631 Trust 1
632 Trust 2
633 Trust 2
634 Trust 2
635 Trust 2
636 Trust 4
637 Trust 4
638 Trust 4
639 Trust 5
640 Trust 5
641 Trust 5
642 Trust 5
643 Trust 6
644 Trust 6
645 Trust 6
646 Trust 6
647 Trust 7
648 Trust 7
649 Trust 7

That's completely wrong, first of all obviously there is no reason to
give each trust 3 or 4 EntityIDs, secondly I don't want to repeat all
of the trusts, I only want to give the new one an entity ID.

How do I set up an append query so it only adds the newly entered
record?

Travis