Linked forms

[cptNemo]

Hello,

I am designing a form to enter data collected with a questionnaire and I am trying to do it with Access 2010.

The database is divided in 8 tables, one for each section of the questionnaire. The first field of each table is the ID that, when the table will start to be filled, is suppose to be the same across all the tables. The ID field of each table is related (one-to-one) to the ID field of the first table.

So far so good.



I have now designed 8 forms, one for each table, and I am trying to link them. Ideally, I would like to have the user to open the blank Form 1, enter the ID which he/she chooses, enter the data in the form, move to Form 2, and find automatically in the ID field (from table 2) the exact value enter as ID in the first form, and so on with all the forms.

How can I do it?

Thanks!

[June7]

Use form/subform arrangement.

Why split out to 8 tables? Are the responses T/F, number, essay?

Consider:

tblRespondents
ID (primary key)
Name
etc

tblQuestions
QuestionID (primary key)
Question

tblResponses
RespondentID (foreign key)
QuestionID (foreign key)
Response

[cptNemo]

I have 150 questions in the questionnaire, keeping one table I had continuously the error "Too many field defined". The tables are big and I am not really up to change them again. Is there any easy way to have the value being copied from you form to the other?

[June7]

If you encountered 'too many fields' error then structure probably not normalized. My suggested structure allows for any number of questions.

Regardless of the structure, form/subform arrangement needed. Use Master/Child links properties of subform container control to synchronize association of records.

[cptNemo]

I was thinking about write few lines of code associated with the button to move to the next form. Something like:

1) Open the next form
2) Check if the table 2 has already a record with the same ID of form 1
3) If yes, open that record from table 2 in the form;
4) if not, create a new record in table 2 with the same ID.

[June7]

You can, but gets complicated. Form/Subform functionality is much easier to set up. No code involved.