Automatically adding new records in 5 other tables when ID is created in main table

[ahuvas]

Okay I had an idea and I thought I might get ffedback as I am relatively new to this. I went through a period a couple of years ago when I used access alot and was becoming familiar with VBA etc but I havent touched it in 2 years so Im pretty rusty.

I am running a study and need to have to create a database that:

a. collects data about participants
b. Has a number of questionnaires (5), each of which can be filled out by participants.

THe main table has a number of fields that collects info about the participants the most important of which will be the ParticipantID - an automatically generated number which is my primary key.

In table 2/form 2 I will host questionnaire one. This will be linked to Table 1/Form 1 (Particpant Information) by this tables primary key - also the Participant ID. The relationship will be 1:1. Each participant can only have one Participant ID and will only need to fill out questionnaire one once.

Is it possible that when I add a new participant to the Participant Information table/form I also automatically create a record in Table 2/Form 2 (Questionnaire 1), as well as Table 3/Form3 (Questionnaire 2) and so on so that they have the same ParticipantID...?

I was reading a similar query somewhere else and they said to use the Form_AfterInsert Event command? Is this right (see here http://www.pcreview.co.uk/forums/thread-1687644.php)?

I feel a bit stupid but I am willing to learn and try new things Once I get started I think I will be okay. If you could steer me in the right direction it would be much appreciated.

 

[Fuga]

You can do this if you have your participants info in a mainform, and the different questionaries in subforms, linked by the ID field.

Is that what you´re looking for?

Fuga.

 

[ahuvas]

Sorry Can you walk me through this a little more?

If the Participants are in the main form and the questionnaires are in the sub-form. Do I select the question 1 belonging to participant 1 for example by using a combp bpx in the top of the form to select the participant I am interested in?

 

[ahuvas]

I dont really want to use sub-forms but I found this code somewhere else and wonder if its appropriate:

Okay I did this and I think it works:

Using the After Insert I created an event procedure that said:

Private Sub Form_AfterInsert()

DBEngine(0)(0).Execute "INSERT INTO tblAthens (ParticipantID) " & _
" VALUES (" & Me.ParticipantID & ")", dbFailOnError

End Sub

Do you think this is okay? it appears to work but I do not want to run into problems later.

 

[Moniker]

Change this:

DBEngine(0)(0).Execute "INSERT INTO tblAthens (ParticipantID) " & _
" VALUES (" & Me.ParticipantID & ")", dbFailOnError

To This:

CurrentDb.Execute "INSERT INTO tblAthens (ParticipantID) " & _
" VALUES (" & Me.ParticipantID & ")", dbFailOnError

Note that you're just executing a simple SQL satement here. This is the same thing a query would do.

 

[neileg]

Why have 5 tables? If the relationships are 1:1, just use one table.

 

[ahuvas]

THere are 5 tables because there are five different questionnaires that I need participants to fill out.

Thanks everyone for your help.

 

[neileg]

THere are 5 tables because there are five different questionnaires that I need participants to fill out.

Do you mean that there are five questionnaires and particpants need to fill out one of them or all five?

 

[ahuvas]

I used the advice above. But each participant needs to fill out each of the 5 participants.

 

[neileg]

Well I'm happy it worked for you, but you should only have one table in a fully normalised design.

I opt out of the 'rules' sometimes when it makes life easier, but in your case it would have made life simpler to follow the rules. Next time, maybe...

 

[ahuvas]

Do you think I should still do it? I have only made one questionnaire so far so it would be easier enough to fix?

As a medical researcher and not a computer science major Ive always used access to meet my needs not really thinking about normaliation etc I should really think about it more I guess.... I think in terms of exporting data etc it would probably be much easier!

 

[neileg]

And I'm an accountant, not a programmer. It really is worth understanding the principles of normalisation.

Many of my applications are 'dirty fixes' so they don't have a wide distribution or a long life. Even so, I always at least consider normalisation principles because it can avoid unforseen problems when faced with the 'Could you just...' questions.

 

[ahuvas]

It really does make more sense to normalise which is what I did. I now no longer need the function and creates records in each form either. I had just used a combo box to filter the form to the record I want when I open the questionnaire. It looks good so far. It had taken me a while to get back into the swing of things and since I never formally learnt how to use access I probably have tons of holes in my knowledge.

Thanks for your help.