Add multiple records into one table in just 1 form

[Birrel88]

Hello there,

After searching for a while and couldn’t get the correct answer, im posting this question here.

To explain how my current database looks now, here is the picture:
h t t p://i67.tinypic.com/339kaqd.jpg

The text is in dutch, but that doesnt really matter now.

What i am creating is a contact database within a larger program.
The customers are in the ‘Klanten’ table.
There are 2 links to ‘Soort klanten’ and ‘Klant afspraken’
The one-on-one relationship to ‘klant afspraken’ i made because its a lot of information and i just splitted the 2 tables to make it a little clearer for me

The problem is the ‘contactpersonen’ table.
This table holds contact information like name and telephone number.
Every customer in the ‘klanten’ table got 2 contacs assigned. They could be the same person or 2 different persons. One of thhem is the main contact, and the other (Penningmeester) is the one that manages the money.

I want all
Those contacts located in 1 table named ‘contactpersonen’ and when adding a new customer id like to pick from
That one table the contactperson and the ‘penningmeester’

As you can see i made the 2 relationships. Acces makes a ‘ghost’ second table named ‘Contactpersonen_1’
As far so good

But ow i make a form, to add everything
I created this first:
H t t p://i66.tinypic.com/rsa6hd.jpg

No problems with the first field, now i want to add th fields to put in a contactperson (not the penningmeester), so i add fields from the ‘contactpersonen’ table, and i get this message:


h t t p://i67.tinypic.com/2zog5mt.jpg

So as i thought would be the best, since the message says exactly what i want to do, i select the first option and press ok, now i add all the fields from the contacts to the form, and want to do exactly the same for the fields that add a new ‘penningmeester’ .

TThe problem is now, when i add another set of fields from the ‘contactpersonen’ table, i dont get that message i got earlier, and when i add them it are just duplicates from the first set of fields.

What i want to do is have two seperate sets of record fields from the same table, but in just a single
Form. Basicly the popup message I got, i wantto have both options.

Is this even possible in access? Or is my thinking not
Ok and is there a better way to do it?

Gr
Borrel

Thanks in advance

 

[Ranman256]

you would run queries. (or sql)
you can code to cycle thru a list to append multiple records.

 

[Birrel88]

Thx for the response. The word query triggered me.

I made a query, added some fields from the ‘Klanten’ table, and added 2 instances of the ‘Contactpersonen’ table.
Made a quick form from that query, and the thing worked. I was able to add multiple record to ‘contactpersonen’ table. Just like i wanted.

But now a second problem raised, in the form, i couldn’t add text to the normal fields in de ‘klanten’ table. It gave me this error:

Cannt add record(s); join key of table ‘klanten’ not in recordset

What can I do about that??

 

[Birrel88]

Ok guys,

So I thought I worked it out.
But it seems I used a select query, and couldn’t add new records.
I then proceeded with making an append query, to add new records.
But he problem is, i can’t get it working.

The append query only let’s me select 1 table to append new records to.
But, my form has to append/add records to multiple table’s at once.

How can I do this?

 

[Pat Hartman]

The pictures you posted are a pain to view and difficult to read. Please use the site tool to upload .jpg files.

Storing the same data multiple times is a problem waiting to happen. What happens if someone updates 1 version but not the others?

Each append query can append to only a single table. If you want the data appended to three tables, you would need to run three append queries.