1 Subform feeding into 2 tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a db that tracks several surveys. One particular survey has approx
130 questions. So what I did was I separated the survey or divided it
between 3 tables. The first table is called the tblAdaSurveyMAIN and the
primary key is named SRID. This table consists of items such as client name,
date survey completed, etc. The other two tables (I will refer to these
tables as the question tables) contain the actual questions on the survey and
they each have forgein keys named SRID (and of course they each have their
own respective primary keys)....So i created relationships between the main
tbl and the other two.

Now, as far as the forms go...I created the main form based on
tblAdaSurveyMain. Then, rather than creating two separate subforms, I
created an unbound form that consists of a SELECT statement that pulls all
the fields from the two question tables. I tried doing a double parent/child
link, linking the SRID from the main form to each of the SRID fields on the
two question forms. For some reason the two SRID fields on the subform are
not automatically filled in as I am entering the data on the main form, and
as I try to begin entering data on the subform I get the message: "The object
doesn't contain the Automation object 'tblAdaSurvey Initial', you tried to
run a vb procedure to set a property or method for an object. However, the
component doesn't make the property or method available for Automation
operations. Check the component's documentation for information on the
properties and methods it makes available for Automation operations." and
then again "The object doesn't contain the Automation object
'tblAdaSurveyLong',......".

I hoped I have explained this well enough.
 
I have a db that tracks several surveys. One particular survey has approx
130 questions. So what I did was I separated the survey or divided it
between 3 tables. The first table is called the tblAdaSurveyMAIN and the
primary key is named SRID. This table consists of items such as client name,
date survey completed, etc. The other two tables (I will refer to these
tables as the question tables) contain the actual questions on the survey and
they each have forgein keys named SRID (and of course they each have their
own respective primary keys)....So i created relationships between the main
tbl and the other two.
Click to expand...

This is a very common first approach to a survey database... and is
fraught with all of the problems you're seeing and more. You're
storing data (questions) in fieldnames; this will pose no end of
problems (e.g. when you need to add a 132nd question, do you redesign
all your tables and all your forms?)

Consider a normalized design instead with tables such as:

tblAdaSurveyMain
SRID
<and all the same fields>

Questions
QuestionNo
Question Text
<other info, such as perhaps the datatype of the answer>

Responses
SRID <<< which survey had this answer
QuestionNo <<< for which question
AnswerText

This gives one *RECORD* per answer rather than one *FIELD* per answer.

For a worked out example, see Duane Hookum's excellent At Your Survey
sample database:

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


If you're stuck with the current design, bear in mind that a popped-up
form *IS NOT A SUBFORM* and will not automatically fill in anything.
You'll need to write code to pass the SRID value in the OpenArgs
argument of the OpenForm method, and use it in the Form's Open event
to set the Default property of the SRID field.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you for your response John. Yes, I remember being told the same as
you're telling me now about survey db's and how they should be normalized. I
just had a hard time wrapping my mind around the whole concept. I tried to
find the sample database with the link that you supplied, but for some reason
it is not working. Your continued help in pointing me in the right direction
is much appreciated!

Oh, also in response to the latter part of your post. No it is a subform,
it is not a pop-up. And yes, I began trying to figure out some code to work
through this but have had a difficult time figuring out the whole
recordsetclone and bookmark concepts. Yes, i have minimal understanding of
vb programming, but I know that I am very much a beginner is several respects.
 
Thank you for your response John. Yes, I remember being told the same as
you're telling me now about survey db's and how they should be normalized. I
just had a hard time wrapping my mind around the whole concept. I tried to
find the sample database with the link that you supplied, but for some reason
it is not working. Your continued help in pointing me in the right direction
is much appreciated!

Oh, also in response to the latter part of your post. No it is a subform,
it is not a pop-up. And yes, I began trying to figure out some code to work
through this but have had a difficult time figuring out the whole
recordsetclone and bookmark concepts. Yes, i have minimal understanding of
vb programming, but I know that I am very much a beginner is several respects.
Click to expand...

A Subform works using its Master/CHild Link Field. It requires *NO*
VBA code at all. What is the structure of your form? What are the
Recordsource queries of the three forms? What are the Master/Child
Link Fields?

As for Duane's demo database, the link works for me; I connected to it
and copied the link directly to my message. What problem are you
having with it?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
As far as the link, this is the error message that comes up:

ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
/Otherdownload.asp, line 32

As far as the rest goes. Yes I know that a subform works using a
master/child link field and I know that it requires No vb code. I'll try
explaining how i have this set up.

tblMain
pk-SRID
DateSurveycompleted
etc.

tblInitial
pk-SRIID
fk-SRID
Q1-50

tblLong
pk-SRLID
fk-SRID
Q51-130

Then here is the form situation:

frmMain (includes all fields from tblMain)

frmLong (using a select statement in the recordsource property I include ALL
fields from both tblInitial and tblLong, including the SRID from both of
those tables). So what I tried to do was do a double master/child link where
the SRID from tblMain was linked to both SRID fields (on the form). I hope
that I have explained this well enough.

Thanks again for all your help on this.....
 
Back
Top