one-to-one relationship

[drolette]

I am implementing a database based on a research questionnaire. The database
will be used for data-entry of the paper questionnaire. The data is then
downloaded, cleaned and used for analysis in a statistical sofware package.
I did not set this up properly as a relational database but I know now for
the future that I must do that. So please forgive this poorly designed
database.

Since one of the questionnaires has beyond 255 variables, I generated 2
tables with 2 forms. The 1st form is opened and the data entry personel
enters the fields. The record is saved and then I have a command which opens
the 2nd form for continued data-entry of the same questionnaire. The 2
tables have a 1 to 1 relationship are are connected via subject ID. How can
I get this ID to automatically populate on the 2nd form so that data-entry
doesn't have to enter the ID again? I'm trying to prevent them from entering
the wrong ID on the 2nd form.

Also, I don't know visual basic so everything I do uses macros.

Thank you for considering my question.
Linda Drolette

 

[boblarson]

Have you tried using 2 subforms on a main form to be able to tie the
information together?

Here's some info that might help:
http://www.functionx.com/access/Lesson27.htm

--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[drolette]

Bob, thank you. I will check this out. If I do use 2 subforms on a main
form, there would still be 2 table, correct?

 

[boblarson]

Yes, but there should be some common field if you want to synch between the
two.
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[drolette]

Thanks for the link. I have to say that I'm still confused as to how to
implement this. My 2 forms have already been developed, as well as my 2
tables. The 2nd form would be the subform? The primary key of the 1st table
is connected to the primary key of the 2nd table (by subject ID). I don't
understand the foreign key so I don't know if I'm setting up the relationship
correctly. After the data-entry person completes the 1st form, is that when
the subform is evoked?
Once the subform is called, I don't want to see the parent form. The data
entry would then continue for the same questionnaire part 2. Is the record
saved at the end when all the fields have been data entered? Does this mean
there would be a record in the 1st table and a related record in the 2nd
table with the same ID?

Can you give me some simple steps on how to do this? I am really new at
this stuff. I'm using 2007.

Thank you so much for any insight you could provide.
Linda

 

[boblarson]

If you don't want to see the main form when having the 2nd form open, then a
master form/subform is not what you want. You would just want to open the
2nd form filtered by the PK of the first.

DoCmd.OpenForm "YourFormNameHere", acViewNormal,,"[YourKeyFieldInForm2]=" &
YourFormKeyIDInForm1

And if it is text then:

DoCmd.OpenForm "YourFormNameHere", acViewNormal,,"[YourKeyFieldInForm2]='" &
YourFormKeyIDInForm1 & "'"


--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista