Form/Subforms

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself. this is
the
point where you want a main form, bound to tblResponses, with a combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and controls
to
enter the date, interviewer (another combo, i think, to choose from the
interviewers table) and the language (possibly another combo, as well?).

then you want a subform, bound to tblResponseDetails...and here is where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires an
Append query and a bit of coding. rather than try to talk you through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and then
set up
your forms the same way.

if you want to see the demo db, post back with your email address. but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so the
spammers don't pick it up. also, let me know what version of Access; i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on to
the
next.


In my mind, I see a main form from which I can select a questionnaire
Name
and another combo from which I can select a pptID (an entire list of
ppts
will be input prior to input of questionnaires). Then, in a subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that lists
the
possible answers for that question. The response combo's source would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer, posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0 and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses fields. I
need
the
question numbers and text so that I can see which question I will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo box
that
looks
to the qstnID field and displays the answers that = the qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting" table,
rather
than a
main data table. in other words, you create a new questionnaire
record
from
time to time, then enter a lot of records of people's responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup. use a
single
form
to add records to tblQuestionnaires, with perhaps a subform for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by itself;
then
tblQuestions in a form with a combobox to select the specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.
you can use the qstnaireID in the mainform to write an Append
query
based on
tblQuestions, to "pre-fill" tblResponses with a record for each
question -
then just move through the records in the subform, entering the
answers
as
you go.

hth


"dee" wrote in message
...
Hi,

I have a database in which some of the tables are already
populated:

tblQuestionnaires
qstnaireID (PK)
qstnaireName

tblQuestions
qstID (PK)
qstnaireID (FK)
qstnNo
qstnText

tblAnswers
answerID (PK)
qstnID (FK)

I now wish to create a form that will allow me to
display/select
questionnaires, input in the corresponding respondent data and
the
responses
of each respondent
answer, with the question number and text displayed on the
form as
well.

The tables that I wish to populate via the form a

tblRespondents
respondentID (PK)
qstnaireID (FK)
responseDateTime
interviewLang

tblResponses
respondentID (PK and FK)
qstnID (PK and FK)
response

I have no problem with the main form that displays the
questionnaire
ID
and
Name and its subform that contains the respondentID,
qstnaireID,
date/time,
language. These are linked master/child via qstnaireID.

My problem is the 2nd subform. I have tried every which way
and
can't
figure out how to accomplish having a form that displays the
question
number
and text, with the respondentID in the subform1 displayed
(linked),
and
the
ability to use a combo box to select the appropriate answer.
(The
combo
record source is tblAnswers)

Even when I finally manage to display all of this, as soon as
I
select
an
answer and try to move to the next question, I get the message
that a
corresponding record is required in the tblRespondents.

Any help would be very, very appreciated.

--
Thanks!

Dee

Hi,

Yes,, got it! Thankss so much. I'm sorry - I am using Access 2003 -
forgot to mention that.

Will post back tomorrow if I have problems. Thanks so much again!


--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself. this is
the
point where you want a main form, bound to tblResponses, with a combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and controls
to
enter the date, interviewer (another combo, i think, to choose from the
interviewers table) and the language (possibly another combo, as well?).

then you want a subform, bound to tblResponseDetails...and here is where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires an
Append query and a bit of coding. rather than try to talk you through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and then
set up
your forms the same way.

if you want to see the demo db, post back with your email address. but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so the
spammers don't pick it up. also, let me know what version of Access; i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on to
the
next.


In my mind, I see a main form from which I can select a questionnaire
Name
and another combo from which I can select a pptID (an entire list of
ppts
will be input prior to input of questionnaires). Then, in a subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that lists
the
possible answers for that question. The response combo's source would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer, posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0 and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses fields. I
need
the
question numbers and text so that I can see which question I will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo box
that
looks
to the qstnID field and displays the answers that = the qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting" table,
rather
than a
main data table. in other words, you create a new questionnaire
record
from
time to time, then enter a lot of records of people's responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup. use a
single
form
to add records to tblQuestionnaires, with perhaps a subform for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by itself;
then
tblQuestions in a form with a combobox to select the specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.
you can use the qstnaireID in the mainform to write an Append
query
based on
tblQuestions, to "pre-fill" tblResponses with a record for each
question -
then just move through the records in the subform, entering the
answers
as
you go.

hth


"dee" wrote in message
...
Hi,

I have a database in which some of the tables are already
populated:

tblQuestionnaires
qstnaireID (PK)
qstnaireName

tblQuestions
qstID (PK)
qstnaireID (FK)
qstnNo
qstnText

tblAnswers
answerID (PK)
qstnID (FK)

I now wish to create a form that will allow me to
display/select
questionnaires, input in the corresponding respondent data and
the
responses
of each respondent
answer, with the question number and text displayed on the
form as
well.

The tables that I wish to populate via the form a

tblRespondents
respondentID (PK)
qstnaireID (FK)
responseDateTime
interviewLang

tblResponses
respondentID (PK and FK)
qstnID (PK and FK)
response

I have no problem with the main form that displays the
questionnaire
ID
and
Name and its subform that contains the respondentID,
qstnaireID,
date/time,
language. These are linked master/child via qstnaireID.

My problem is the 2nd subform. I have tried every which way
and
can't
figure out how to accomplish having a form that displays the
question
number
and text, with the respondentID in the subform1 displayed
(linked),
and
the
ability to use a combo box to select the appropriate answer.
(The
combo
record source is tblAnswers)

Even when I finally manage to display all of this, as soon as
I
select
an
answer and try to move to the next question, I get the message
that a
corresponding record is required in the tblRespondents.

Any help would be very, very appreciated.

--
Thanks!

Dee

Hi Tina,

I have analysed your db and am working on my form/subform. The main form is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet created the
append query - am first making sure that the correct information is displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a link
between the selected questionnaire in the main form and the questionID field
in the subform so that the questions related to a specific questionnaire are
"loaded". I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the headings in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow display the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself. this is
the
point where you want a main form, bound to tblResponses, with a combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and controls
to
enter the date, interviewer (another combo, i think, to choose from the
interviewers table) and the language (possibly another combo, as well?).

then you want a subform, bound to tblResponseDetails...and here is where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires an
Append query and a bit of coding. rather than try to talk you through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and then
set up
your forms the same way.

if you want to see the demo db, post back with your email address. but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so the
spammers don't pick it up. also, let me know what version of Access; i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on to
the
next.


In my mind, I see a main form from which I can select a questionnaire
Name
and another combo from which I can select a pptID (an entire list of
ppts
will be input prior to input of questionnaires). Then, in a subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that lists
the
possible answers for that question. The response combo's source would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer, posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0 and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses fields. I
need
the
question numbers and text so that I can see which question I will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo box
that
looks
to the qstnID field and displays the answers that = the qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting" table,
rather
than a
main data table. in other words, you create a new questionnaire
record
from
time to time, then enter a lot of records of people's responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup. use a
single
form
to add records to tblQuestionnaires, with perhaps a subform for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by itself;
then
tblQuestions in a form with a combobox to select the specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.
you can use the qstnaireID in the mainform to write an Append
query
based on
tblQuestions, to "pre-fill" tblResponses with a record for each
question -
then just move through the records in the subform, entering the
answers
as
you go.

hth


"dee" wrote in message
...
Hi,

I have a database in which some of the tables are already
populated:

tblQuestionnaires
qstnaireID (PK)
qstnaireName

tblQuestions
qstID (PK)
qstnaireID (FK)
qstnNo
qstnText

tblAnswers
answerID (PK)
qstnID (FK)

I now wish to create a form that will allow me to
display/select
questionnaires, input in the corresponding respondent data and
the
responses
of each respondent
answer, with the question number and text displayed on the
form as
well.

The tables that I wish to populate via the form a

tblRespondents
respondentID (PK)
qstnaireID (FK)
responseDateTime
interviewLang

tblResponses
respondentID (PK and FK)
qstnID (PK and FK)
response

I have no problem with the main form that displays the
questionnaire
ID
and
Name and its subform that contains the respondentID,
qstnaireID,
date/time,
language. These are linked master/child via qstnaireID.

My problem is the 2nd subform. I have tried every which way
and
can't
figure out how to accomplish having a form that displays the
question
number
and text, with the respondentID in the subform1 displayed
(linked),
and
the
ability to use a combo box to select the appropriate answer.
(The
combo
record source is tblAnswers)

Even when I finally manage to display all of this, as soon as
I
select
an
answer and try to move to the next question, I get the message
that a
corresponding record is required in the tblRespondents.

Any help would be very, very appreciated.

--
Thanks!

Dee

comments inline.

"dee" wrote in message
news
Hi Tina,

I have analysed your db and am working on my form/subform. The main form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words, is
it possible to go through the paper form from beginning to end, entering the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty, it
just has to be accurate, so focus on that aspect of the setup and don't get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so
the
spammers don't pick it up. also, let me know what version of Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire
Name
and another combo from which I can select a pptID (an entire list
of
ppts
will be input prior to input of questionnaires). Then, in a
subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that
lists
the
possible answers for that question. The response combo's source
would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and
the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer,
posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so
we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select
a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0
and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses
fields. I
need
the
question numbers and text so that I can see which question I
will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo
box
that
looks
to the qstnID field and displays the answers that = the
qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input
for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting"
table,
rather
than a
main data table. in other words, you create a new
questionnaire
record
from
time to time, then enter a lot of records of people's
responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup.
use a
single
form
to add records to tblQuestionnaires, with perhaps a subform
for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by
itself;
then
tblQuestions in a form with a combobox to select the
specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.
you can use the qstnaireID in the mainform to write an
Append
query
based on
tblQuestions, to "pre-fill" tblResponses with a record for
each
question -
then just move through the records in the subform, entering
the
answers
as
you go.

hth


"dee" wrote in message
...
Hi,

I have a database in which some of the tables are already

populated:

tblQuestionnaires
qstnaireID (PK)
qstnaireName

tblQuestions
qstID (PK)
qstnaireID (FK)
qstnNo
qstnText

tblAnswers
answerID (PK)
qstnID (FK)

I now wish to create a form that will allow me to
display/select
questionnaires, input in the corresponding respondent data
and
the
responses
of each respondent
answer, with the question number and text displayed on the
form as
well.

The tables that I wish to populate via the form a

tblRespondents
respondentID (PK)
qstnaireID (FK)
responseDateTime
interviewLang

tblResponses
respondentID (PK and FK)
qstnID (PK and FK)
response

I have no problem with the main form that displays the
questionnaire
ID
and
Name and its subform that contains the respondentID,
qstnaireID,
date/time,
language. These are linked master/child via qstnaireID.

My problem is the 2nd subform. I have tried every which
way
and
can't
figure out how to accomplish having a form that displays
the
question
number
and text, with the respondentID in the subform1 displayed
(linked),
and
the
ability to use a combo box to select the appropriate answe
r.

Hi Tina,

You're right - I don't really need to display the headings and subheadings,
so that takes care of that question.

My only other concern that this point is - can I specify that I wish to
display other fields from the tblQuestions? The reason for this is that I
have fields such as ControlType and LimitToList, that i will need to use in
order to execute code appropriate for those fields.

Thanks again so much!
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news
Hi Tina,

I have analysed your db and am working on my form/subform. The main form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words, is
it possible to go through the paper form from beginning to end, entering the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty, it
just has to be accurate, so focus on that aspect of the setup and don't get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so
the
spammers don't pick it up. also, let me know what version of Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire
Name
and another combo from which I can select a pptID (an entire list
of
ppts
will be input prior to input of questionnaires). Then, in a
subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that
lists
the
possible answers for that question. The response combo's source
would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and
the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer,
posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so
we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select
a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0
and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses
fields. I
need
the
question numbers and text so that I can see which question I
will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo
box
that
looks
to the qstnID field and displays the answers that = the
qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input
for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting"
table,
rather
than a
main data table. in other words, you create a new
questionnaire
record
from
time to time, then enter a lot of records of people's
responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup.
use a
single
form
to add records to tblQuestionnaires, with perhaps a subform
for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by
itself;
then
tblQuestions in a form with a combobox to select the
specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.

Hi again,

Another question... will this show only the questions for the selected
questionnaire in the main form?

Not all ppts will fill in each and every questionnaire, so I need to display
the questions that are relevant to the selected questionnaire only.

Thanks again!
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news
Hi Tina,

I have analysed your db and am working on my form/subform. The main form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words, is
it possible to go through the paper form from beginning to end, entering the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty, it
just has to be accurate, so focus on that aspect of the setup and don't get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so
the
spammers don't pick it up. also, let me know what version of Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire
Name
and another combo from which I can select a pptID (an entire list
of
ppts
will be input prior to input of questionnaires). Then, in a
subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that
lists
the
possible answers for that question. The response combo's source
would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and
the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer,
posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so
we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select
a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0
and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses
fields. I
need
the
question numbers and text so that I can see which question I
will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo
box
that
looks
to the qstnID field and displays the answers that = the
qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input
for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting"
table,
rather
than a
main data table. in other words, you create a new
questionnaire
record
from
time to time, then enter a lot of records of people's
responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup.
use a
single
form
to add records to tblQuestionnaires, with perhaps a subform
for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by
itself;
then
tblQuestions in a form with a combobox to select the
specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.

Hi Tina,

I have worked for the better part of the day trying to get my subform to
work - to no avail.

So, I decided I would go back to your db and try to recreate the forms and
append query you had designed to see if I was missing something there first.

I am definitely missing something. I have checked every property I can
think of, but must be missing something. I can create the forms and query as
you have. When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the subform
- it just sits there and looks at me - no products are automatically filled
in. So, I'm missing how to do this.

So that's the first thing I need to figure out.

The second thing would be this. Let's say I also want to display a list of
prices of the products - maybe 1 price is for high volume clients, 1 price
for medium volume and 1 for low volume. This would be where the amount
column is - but I'd like to populate it via a combo box with the source of a
tblProductsPrice.

This is sort of what I am needing to do with my subform. I want to display
the question numbers and text that are associated with a partcular
questionnaire that I select in the main form. (I think I have done this -
just need it to "auto populate" as discussed above).

I have included the answerID field (fk) from the tblResponseDetails in the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how to have
the correct answer choices display in relation to the question being asked.

I know I'm missing something pretty basic. I keep trying to find it on my
own, but have hit another dead end after many hours.

help! :-)

Thanks again for your patience.
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news
Hi Tina,

I have analysed your db and am working on my form/subform. The main form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words, is
it possible to go through the paper form from beginning to end, entering the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty, it
just has to be accurate, so focus on that aspect of the setup and don't get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the extra
words there (don't worry, the automated spammers won't understand this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter the
answers
to all the questions. this is not so hard to set up, but it requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom" so
the
spammers don't pick it up. also, let me know what version of Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire
Name
and another combo from which I can select a pptID (an entire list
of
ppts
will be input prior to input of questionnaires). Then, in a
subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box that
lists
the
possible answers for that question. The response combo's source
would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers and
the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer,
posted
before i
saw your latest post below. we need to make sure the tables are
right
before
digging into forms too deeply. pls post back to that answer (so
we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to select
a
questionnaire name from a combo box, so had thought to use the
qstnaireID
field in the main for - transform to combo, 2 columns, width 0
and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question headings,
subheadings,
number and text, from tblQuestions plus the tblResponses
fields. I
need
the
question numbers and text so that I can see which question I
will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a combo
box
that
looks
to the qstnID field and displays the answers that = the
qstnID.

When I try to select another questionnaire for a particular
respondentID
in
the main form, it replaces the information that I had input
for
the
previous
form.

I hope I've made this as clear as possible. Your help is very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting"
table,
rather
than a
main data table. in other words, you create a new
questionnaire
record
from
time to time, then enter a lot of records of people's
responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms setup.
use a
single
form
to add records to tblQuestionnaires, with perhaps a subform
for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by
itself;
then
tblQuestions in a form with a combobox to select the
specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound to
tblResponses.

When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

in my demo db, open the mainform in Design view, click on the subform
control *once* to select it, and then take a look at the Enter event
procedure - there's the code that runs the Append query.

I have included the answerID field (fk) from the tblResponseDetails in the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how to
have
the correct answer choices display in relation to the question being
asked.

okay, let's take another look at the fields in tblResponseDetails (per the
structure i posted previously in this thread):

tblResponseDetails
DetailID (primary key, autonumber)
ResponseID (foreign key from tblResponses)
QuestionID (foreign key from tblQuestions)
AnswerID (foreign key from tblAnswers)

so we see that the QuestionID is part of the response detail record, as it
should be. now, as you say, you've bound the AnswerID field to a combobox
control whose RowSource is tblAnswers. and here's a reminder of the makeup
of tblAnswers, as

tblAnswers
answerID (PK)
qstnID (FK)

you just need to change the RowSource to a SELECT statement, as

SELECT answerID, answerText FROM tblAnswers WHERE qstnID =
Forms!MainformName!SubformControlName.Form!Questio nID

the above goes all on one line in the RowSource, regardless of linewrap in
this post. replace MainformName with the correct name of the main form;
replace SubformControlName with the correct name of the subform control
(within the main form) that "contains" the subform. and make sure that
QuestionID is the correct name of the question ID field in the subform.

last, you need to add a requery action to the combobox control's Enter event
procedure, as

Me!AnswerComboboxName.Requery

i'm guessing that the subform is in Datasheet view. with the above solution,
you may find that when you requery the combobox control, answers from
*other* records in the datasheet may seem to "disappear". relax, they have
not. it's just a visual issue, caused by the necessary requery of the
combobox control.

hth


"dee" wrote in message
...
Hi Tina,

I have worked for the better part of the day trying to get my subform to
work - to no avail.

So, I decided I would go back to your db and try to recreate the forms and
append query you had designed to see if I was missing something there
first.

I am definitely missing something. I have checked every property I can
think of, but must be missing something. I can create the forms and query
as
you have. When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

So that's the first thing I need to figure out.

The second thing would be this. Let's say I also want to display a list
of
prices of the products - maybe 1 price is for high volume clients, 1 price
for medium volume and 1 for low volume. This would be where the amount
column is - but I'd like to populate it via a combo box with the source of
a
tblProductsPrice.

This is sort of what I am needing to do with my subform. I want to
display
the question numbers and text that are associated with a partcular
questionnaire that I select in the main form. (I think I have done this -
just need it to "auto populate" as discussed above).

I have included the answerID field (fk) from the tblResponseDetails in the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how to
have
the correct answer choices display in relation to the question being
asked.

I know I'm missing something pretty basic. I keep trying to find it on my
own, but have hit another dead end after many hours.

help! :-)

Thanks again for your patience.
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news
Hi Tina,

I have analysed your db and am working on my form/subform. The main
form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet
created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a
link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific
questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append
query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the
headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow
display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words,
is
it possible to go through the paper form from beginning to end, entering
the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty,
it
just has to be accurate, so focus on that aspect of the setup and don't
get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the
extra
words there (don't worry, the automated spammers won't understand
this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and
another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose
from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here
is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter
the
answers
to all the questions. this is not so hard to set up, but it
requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with
someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email
address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom"
so
the
spammers don't pick it up. also, let me know what version of
Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set
to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and
how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving
on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire
Name
and another combo from which I can select a pptID (an entire
list
of
ppts
will be input prior to input of questionnaires). Then, in a
subform,
I
would
like to see a list of all of the questions related to that
questionnaire
(locked and disabled, but for reference), with a combo box
that
lists
the
possible answers for that question. The response combo's
source
would
be
the
answers from tblAnswers, linking the qstnID in tableAnswers
and
the
qstnID
on
the form.

Does this make sense?

Thanks again, so much. This has been quite a nightmare.
--
Thanks!

Dee


"tina" wrote:

okay, we'll work on this, but first see my previous answer,
posted
before i
saw your latest post below. we need to make sure the tables
are
right
before
digging into forms too deeply. pls post back to that answer
(so
we
don't
have 2 "mini" threads going), and we'll go from there.


"dee" wrote in message
...
Hi Tina,

I printed out your suggestion and have tried it.

I have the main form based upon tblRespondents that works.
Unfortunately,
I'm not sure how to create the append query. I need to
select
a
questionnaire name from a combo box, so had thought to use
the
qstnaireID
field in the main for - transform to combo, 2 columns,
width 0
and
..5.
So
that is my first problem.

The subform is linked to the main form via respondentID
child/master.
I
changed the record source to include the question
headings,
subheadings,
number and text, from tblQuestions plus the tblResponses
fields. I
need
the
question numbers and text so that I can see which question
I
will
be
answering. These will be locked and disabled.

I thought I would transform the response field into a
combo
box
that
looks
to the qstnID field and displays the answers that = the
qstnID.

When I try to select another questionnaire for a
particular
respondentID
in
the main form, it replaces the information that I had
input
for
the
previous
form.

I hope I've made this as clear as possible. Your help is
very
appreciated -
although it would appear I need "baby steps".

Thanks so much, Tina.
--
Thanks!

Dee


"tina" wrote:

it seems to me that tblQuestionnaires is a "supporting"
table,
rather
than a
main data table. in other words, you create a new
questionnaire
record
from
time to time, then enter a lot of records of people's
responses
to
that
questionnaire, correct?

if that's the case, i'd suggest changing the forms
setup.
use a
single
form
to add records to tblQuestionnaires, with perhaps a
subform
for
tblQuestions
and a sub-subform for tblAnswers. if you don't want to
nest
subforms
three
deep, then i'd suggest tblQuestionnaires in a form by
itself;
then
tblQuestions in a form with a combobox to select the
specific
questionnaire,
and a subform to create the answers for each question.

that takes care of the questionnaire setup. to enter the
responses,
suggest
a mainform bound to tblRespondents, with a subform bound
to
tblResponses.

Hi Tina,

I’m really, really sorry about this – but I can seem to get things working.

Main form is working fine. Can’t get the subform to work. If I had both
qstnID and answerID fields, it doesn’t show anything from combo drop-down.

If I only add the qstnID, it displays all questions in the drop-down, but I
need to have them listed one after the other. I would prefer to work in
continuous form view if possible.

Here is what I have, as detailed as I can. (If you would find it easier if
I sent you the db, please let me know):


Main form name: frmResponses
Source: tblResponses
Linking field to sub form: responseID
Name of field that displays questionnaireId - cboQstnaireID

Control name: sfrmResponseDetails
Subform Name: childDetails
Source: tblResponseDetails
Fields: responseID, qstnID, answered
On Enter event:
Private Sub ChildDetails_Enter()

If IsNull(Me!responseID) Then Exit Sub

With Me!childDetails.Form
If .RecordsetClone.RecordCount 1 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryResponseDetails"
DoCmd.SetWarnings True
.Requery
End If
End With
End Sub

Append Query Name: qryResponseDetails
Append to: tblResponseDetails
Table: tblQuestions
Append: responseID from Expr1: Forms!frmResponses!responseID
Append: qstnID from tblQuestions

qstnID field in subform: combo box
Name: cboQstnID
Source:
SELECT tblQuestions.qstnID, tblquestions!qstnNo & ". " &
tblQuestions!qstnText AS Question
FROM tblQuestions
WHERE (((tblQuestions.qstnaireID)=[Forms]![frmResponses]![cboQstnaireID]));

answered field in subform: combo box
Name: cboAnswerID
Source:
SELECT tblAnswers.answerID, tblAnswers.answer FROM tbAnswers WHERE
(((tblAnswers.qstnID)=[Forms]![frmResponses]![childDetails].[Form]![cboQstnID]));

I think that's it! :-)

As always, thank so much for your kind patience and expert advice.



--
Thanks!

Dee


"tina" wrote:

When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

in my demo db, open the mainform in Design view, click on the subform
control *once* to select it, and then take a look at the Enter event
procedure - there's the code that runs the Append query.

I have included the answerID field (fk) from the tblResponseDetails in the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how to
have
the correct answer choices display in relation to the question being
asked.

okay, let's take another look at the fields in tblResponseDetails (per the
structure i posted previously in this thread):

tblResponseDetails
DetailID (primary key, autonumber)
ResponseID (foreign key from tblResponses)
QuestionID (foreign key from tblQuestions)
AnswerID (foreign key from tblAnswers)

so we see that the QuestionID is part of the response detail record, as it
should be. now, as you say, you've bound the AnswerID field to a combobox
control whose RowSource is tblAnswers. and here's a reminder of the makeup
of tblAnswers, as

tblAnswers
answerID (PK)
qstnID (FK)

you just need to change the RowSource to a SELECT statement, as

SELECT answerID, answerText FROM tblAnswers WHERE qstnID =
Forms!MainformName!SubformControlName.Form!Questio nID

the above goes all on one line in the RowSource, regardless of linewrap in
this post. replace MainformName with the correct name of the main form;
replace SubformControlName with the correct name of the subform control
(within the main form) that "contains" the subform. and make sure that
QuestionID is the correct name of the question ID field in the subform.

last, you need to add a requery action to the combobox control's Enter event
procedure, as

Me!AnswerComboboxName.Requery

i'm guessing that the subform is in Datasheet view. with the above solution,
you may find that when you requery the combobox control, answers from
*other* records in the datasheet may seem to "disappear". relax, they have
not. it's just a visual issue, caused by the necessary requery of the
combobox control.

hth


"dee" wrote in message
...
Hi Tina,

I have worked for the better part of the day trying to get my subform to
work - to no avail.

So, I decided I would go back to your db and try to recreate the forms and
append query you had designed to see if I was missing something there
first.

I am definitely missing something. I have checked every property I can
think of, but must be missing something. I can create the forms and query
as
you have. When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

So that's the first thing I need to figure out.

The second thing would be this. Let's say I also want to display a list
of
prices of the products - maybe 1 price is for high volume clients, 1 price
for medium volume and 1 for low volume. This would be where the amount
column is - but I'd like to populate it via a combo box with the source of
a
tblProductsPrice.

This is sort of what I am needing to do with my subform. I want to
display
the question numbers and text that are associated with a partcular
questionnaire that I select in the main form. (I think I have done this -
just need it to "auto populate" as discussed above).

I have included the answerID field (fk) from the tblResponseDetails in the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how to
have
the correct answer choices display in relation to the question being
asked.

I know I'm missing something pretty basic. I keep trying to find it on my
own, but have hit another dead end after many hours.

help! :-)

Thanks again for your patience.
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news Hi Tina,

I have analysed your db and am working on my form/subform. The main
form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet
created
the
append query - am first making sure that the correct information is
displayed
on the subform.

In essence, I need two types of links between main and sub form. The
response_id field master/child, which is fine. But, I also need a
link
between the selected questionnaire in the main form and the questionID
field
in the subform so that the questions related to a specific
questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the Append
query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to the
tblQuestionnaires via fk questionnaireID and contains all of the
headings
in
the questionnaires. I did this to normalize the tblQuestions, as many
questions are in each different heading. So, I need to somehow
display
the
headings and sub-headings in that table, plus the question number and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do you
REALLY *need* the headings that show on the paper form? in other words,
is
it possible to go through the paper form from beginning to end, entering
the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be pretty,
it
just has to be accurate, so focus on that aspect of the setup and don't
get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not the
extra
words there (don't worry, the automated spammers won't understand
this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it, myself.
this is
the
point where you want a main form, bound to tblResponses, with a
combo
box
control to choose the respondent from tblParticipants, and
another
combobox
control to choose the questionnaire from tblQuestionnaires, and
controls
to
enter the date, interviewer (another combo, i think, to choose
from
the
interviewers table) and the language (possibly another combo, as
well?).

then you want a subform, bound to tblResponseDetails...and here
is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and enter
the
answers
to all the questions. this is not so hard to set up, but it
requires
an
Append query and a bit of coding. rather than try to talk you
through
it, i
can email to you a small demo db that i recently shared with
someone
else in
the newsgroups. you can study the setup to see how it works, and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email
address.
but
be
sure to "disguise" it, such as "dee somebody atsomeplacedotcom"
so
the
spammers don't pick it up. also, let me know what version of
Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm set
to
create
the
forms/subforms.

I think I answered your questions regarding the work flow and
how
things
are
received. I will receive a "pack" of respondent (participant)
questionnaires
and will fill in each of that respondent's data before moving
on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire

Here is what I have, as detailed as I can. (If you would find it easier
if
I sent you the db, please let me know)

you can send me the db, hon, and i'll take a look - but i probably won't
have time to do it until this coming weekend. i just sent you an email from
my "real" email address, so you can attach the db to a reply email.

hth


"dee" wrote in message
...
Hi Tina,

I'm really, really sorry about this - but I can seem to get things
working.

Main form is working fine. Can't get the subform to work. If I had both
qstnID and answerID fields, it doesn't show anything from combo drop-down.

If I only add the qstnID, it displays all questions in the drop-down, but
I
need to have them listed one after the other. I would prefer to work in
continuous form view if possible.

Here is what I have, as detailed as I can. (If you would find it easier
if
I sent you the db, please let me know):


Main form name: frmResponses
Source: tblResponses
Linking field to sub form: responseID
Name of field that displays questionnaireId - cboQstnaireID

Control name: sfrmResponseDetails
Subform Name: childDetails
Source: tblResponseDetails
Fields: responseID, qstnID, answered
On Enter event:
Private Sub ChildDetails_Enter()

If IsNull(Me!responseID) Then Exit Sub

With Me!childDetails.Form
If .RecordsetClone.RecordCount 1 Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryResponseDetails"
DoCmd.SetWarnings True
.Requery
End If
End With
End Sub

Append Query Name: qryResponseDetails
Append to: tblResponseDetails
Table: tblQuestions
Append: responseID from Expr1: Forms!frmResponses!responseID
Append: qstnID from tblQuestions

qstnID field in subform: combo box
Name: cboQstnID
Source:
SELECT tblQuestions.qstnID, tblquestions!qstnNo & ". " &
tblQuestions!qstnText AS Question
FROM tblQuestions
WHERE
(((tblQuestions.qstnaireID)=[Forms]![frmResponses]![cboQstnaireID]));

answered field in subform: combo box
Name: cboAnswerID
Source:
SELECT tblAnswers.answerID, tblAnswers.answer FROM tbAnswers WHERE

(((tblAnswers.qstnID)=[Forms]![frmResponses]![childDetails].[Form]![cboQstnI
D]));

I think that's it! :-)

As always, thank so much for your kind patience and expert advice.



--
Thanks!

Dee


"tina" wrote:

When I open the main form and select a saleperson, location and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

in my demo db, open the mainform in Design view, click on the subform
control *once* to select it, and then take a look at the Enter event
procedure - there's the code that runs the Append query.

I have included the answerID field (fk) from the tblResponseDetails in
the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how
to
have
the correct answer choices display in relation to the question being
asked.

okay, let's take another look at the fields in tblResponseDetails (per
the
structure i posted previously in this thread):

tblResponseDetails
DetailID (primary key, autonumber)
ResponseID (foreign key from tblResponses)
QuestionID (foreign key from tblQuestions)
AnswerID (foreign key from tblAnswers)

so we see that the QuestionID is part of the response detail record, as
it
should be. now, as you say, you've bound the AnswerID field to a
combobox
control whose RowSource is tblAnswers. and here's a reminder of the
makeup
of tblAnswers, as

tblAnswers
answerID (PK)
qstnID (FK)

you just need to change the RowSource to a SELECT statement, as

SELECT answerID, answerText FROM tblAnswers WHERE qstnID =
Forms!MainformName!SubformControlName.Form!Questio nID

the above goes all on one line in the RowSource, regardless of linewrap
in
this post. replace MainformName with the correct name of the main form;
replace SubformControlName with the correct name of the subform control
(within the main form) that "contains" the subform. and make sure that
QuestionID is the correct name of the question ID field in the subform.

last, you need to add a requery action to the combobox control's Enter
event
procedure, as

Me!AnswerComboboxName.Requery

i'm guessing that the subform is in Datasheet view. with the above
solution,
you may find that when you requery the combobox control, answers from
*other* records in the datasheet may seem to "disappear". relax, they
have
not. it's just a visual issue, caused by the necessary requery of the
combobox control.

hth


"dee" wrote in message
...
Hi Tina,

I have worked for the better part of the day trying to get my subform
to
work - to no avail.

So, I decided I would go back to your db and try to recreate the forms
and
append query you had designed to see if I was missing something there
first.

I am definitely missing something. I have checked every property I
can
think of, but must be missing something. I can create the forms and
query
as
you have. When I open the main form and select a saleperson, location
and
fill in the date - no problem. However when I hit Tab to move to the
subform
- it just sits there and looks at me - no products are automatically
filled
in. So, I'm missing how to do this.

So that's the first thing I need to figure out.

The second thing would be this. Let's say I also want to display a
list
of
prices of the products - maybe 1 price is for high volume clients, 1
price
for medium volume and 1 for low volume. This would be where the
amount
column is - but I'd like to populate it via a combo box with the
source of
a
tblProductsPrice.

This is sort of what I am needing to do with my subform. I want to
display
the question numbers and text that are associated with a partcular
questionnaire that I select in the main form. (I think I have done
this -
just need it to "auto populate" as discussed above).

I have included the answerID field (fk) from the tblResponseDetails in
the
sub form, changed it to a combo box, then made the source tblAnswers
(answerID, answerText, 2 columns, 0,1 widths). But I'm not sure how
to
have
the correct answer choices display in relation to the question being
asked.

I know I'm missing something pretty basic. I keep trying to find it
on my
own, but have hit another dead end after many hours.

help! :-)

Thanks again for your patience.
--
Thanks!

Dee


"tina" wrote:

comments inline.

"dee" wrote in message
news Hi Tina,

I have analysed your db and am working on my form/subform. The
main
form
is
fine - works well.

The sub form is where I'm encountering problems. I haven't yet
created
the
append query - am first making sure that the correct information
is
displayed
on the subform.

In essence, I need two types of links between main and sub form.
The
response_id field master/child, which is fine. But, I also need a
link
between the selected questionnaire in the main form and the
questionID
field
in the subform so that the questions related to a specific
questionnaire
are
"loaded".

no, you don't, hon. you'll choose the correct questions in the
Append
query,
by setting criteria on the questionnaireID foreign key field in
tblQuestions, taking the value from the questionnaire you chose in
the
mainform record, as

Forms!MainFormName!QuestionnaireIDControlName

I'm also thinking I may have to base the sub-form on a query that
also contains the tblHeadings, which is a table that is related to
the
tblQuestionnaires via fk questionnaireID and contains all of the
headings
in
the questionnaires. I did this to normalize the tblQuestions, as
many
questions are in each different heading. So, I need to somehow
display
the
headings and sub-headings in that table, plus the question number
and
question text, the latter two coming from tblQuestions.

well, my question here would be: for the purposes of data entry, do
you
REALLY *need* the headings that show on the paper form? in other
words,
is
it possible to go through the paper form from beginning to end,
entering
the
answers in the subform in the proper order, but without headings to
"separate" the question groups?

remember that when you're entering data, it doesn't have to be
pretty,
it
just has to be accurate, so focus on that aspect of the setup and
don't
get
too hung up on matching form to paper.

hth


I hope I have explained this clearly. Thanks so very much!
--
Thanks!

Dee


"tina" wrote:

okay, just sent it off. i assume you really meant hotmail, not
the
extra
words there (don't worry, the automated spammers won't
understand
this).


"dee" wrote in message
...
Thanks so much! You can e-mail me at:

candlelight1956 at the hotmail place dot com.

Thanks Tina! I truly appreciate every moment you are spending
on
this. I
couldn't do it alone.


--
Thanks!

Dee


"tina" wrote:

yes, it does make sense, and that's how i'd go about it,
myself.
this is
the
point where you want a main form, bound to tblResponses,
with a
combo
box
control to choose the respondent from tblParticipants, and
another
combobox
control to choose the questionnaire from tblQuestionnaires,
and
controls
to
enter the date, interviewer (another combo, i think, to
choose
from
the
interviewers table) and the language (possibly another
combo, as
well?).

then you want a subform, bound to tblResponseDetails...and
here
is
where
you
want the records "pre-added", one for each question from the
questionnaire
chosen in the mainform combobox, so you can go through and
enter
the
answers
to all the questions. this is not so hard to set up, but it
requires
an
Append query and a bit of coding. rather than try to talk
you
through
it, i
can email to you a small demo db that i recently shared with
someone
else in
the newsgroups. you can study the setup to see how it works,
and
then
set up
your forms the same way.

if you want to see the demo db, post back with your email
address.
but
be
sure to "disguise" it, such as "dee somebody
atsomeplacedotcom"
so
the
spammers don't pick it up. also, let me know what version of
Access;
i
can
send anything from A97 to A2003.

hth


"dee" wrote in message
...
Hi Tina,

OK, I've set up the tables as per your previous post. I'm
set
to
create
the
forms/subforms.

I think I answered your questions regarding the work flow
and
how
things
are
received. I will receive a "pack" of respondent
(participant)
questionnaires
and will fill in each of that respondent's data before
moving
on
to
the
next.


In my mind, I see a main form from which I can select a
questionnaire