Carry Id from one Form to another

[bbach via AccessMonster.com]

Hello..

I am somewhat new to MS Access and need some help with a data entry db. I
have two tables each with a Primary key of FRMID_PK. Table # 1 - Recipient
Table #2 - Response ( one to one relationship ).
I want to be able to place the FRMID_PK in the Recipient table and have it
carry over to the Response table.

I have created seven forms. The first form has all the Recipient information
and the six other forms are a breakdown of the Response information . There
is no repeating values in any of the tables so I didn't bother with a foreign
key.

I want to be able to place a FRMID in the Recipient frm and on exit and/or
open of first Response form have that same FRMID show. I want to be able to
prevent data entry error or wrong record source from occurring.

Any suggestions will be helpful.

P.S. I am NOT well versed in Visual Basic.
Thanks..

 

[John Vinson]

Hello..

I am somewhat new to MS Access and need some help with a data entry db. I
have two tables each with a Primary key of FRMID_PK. Table # 1 - Recipient
Table #2 - Response ( one to one relationship ).

One to one relationships are VERY rare. Why not just include the
response information in Table1? Is it correct to say that each
Recipient gets one, and only one, response for the life of the system?

I want to be able to place the FRMID_PK in the Recipient table and have it
carry over to the Response table.

The simplest way to do this is to use a Form for the Recipients table,
with a Subform for the Response table. Use FRMID_PK as the
master/child link field and it will carry over automatically.

I have created seven forms. The first form has all the Recipient information
and the six other forms are a breakdown of the Response information . There
is no repeating values in any of the tables so I didn't bother with a foreign
key.

But with a one to one relationship, the Response table's Primary Key
*IS ALSO* a foreign key. What on Earth is the structure of the
Response table that it needs *SIX FORMS* to enter the data!? I must
respectfully disagree that you don't have any repeating data; or you
have some very strange fields. How many fields does the Response table
include?

I want to be able to place a FRMID in the Recipient frm and on exit and/or
open of first Response form have that same FRMID show. I want to be able to
prevent data entry error or wrong record source from occurring.

It will be rather difficult to do so - but I suspect that with a
rethinking of your table design it will be unnecessary to do so.

That said... in the master table form, you can create a command button
to open each of the other forms. Pass the FRMID_PK value in the
OpenArgs property of the OpenForm method, and use code in the second
form's Open event to set the DefaultValue property of the
corresponding field in the child table; also use the WhereCondition
argument to open the form to the desired record if there already is
one. This is NOT a good design, but it is what you asked for... :-{(

John W. Vinson[MVP]

 

[B Brown via AccessMonster.com]

thanks..

I realize the table design is not the best. I placed the data in two
different tables because of the number of variables. I'm not sure how easy
analysis of the data will be if all the variables are in one table. There are
60+ variables in this survey. Al the questions are mutually exclusive. Each
person will only go into the system once.
I will take your suggestion and cobine all the data into one table.

Thanks again..

 

[John Vinson]

thanks..

I realize the table design is not the best. I placed the data in two
different tables because of the number of variables. I'm not sure how easy
analysis of the data will be if all the variables are in one table. There are
60+ variables in this survey. Al the questions are mutually exclusive. Each
person will only go into the system once.
I will take your suggestion and cobine all the data into one table.

That's what I was guessing might be the case: surveys are notoriously
easy to fall into the "wide-flat" design trap.

For a normalized alternative (no more than ten fields in any table
IIRC) see Duane Hookum's excellent _At Your Survey_ sample database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

John W. Vinson[MVP]