?bound fields

  • Thread starter Thread starter Marion
  • Start date Start date
M

Marion

I can not get my form to display the correct
information. My table consists of fields such as
question1 answer1 question2 answer2 etc. Each field has
3 records to reflect a choice of answers a,b or c and a
corresponding answer to suit

I have set up my form to have drop down box to select one
of three options for question1 but how can I get my
answer1 box to automatically select the corresponding
answer to suit the drop down option selected in
question1. At present all three answers show. I have
set the answer1 field up as a memo field as the text in
the answer is quite long.

Any help gratefully received

Regards

Marion
 
Marion,

I think that your problem is essentially the result of a poorly designed
table. You actually have a one to many relationship of questionnaires to
questions, but have put everything in the same table - hence the fields
"question1", "question2" etc. If possible, I would suggest that you change
the table structure - this will not be the only case where this table
structure will cause you problems in the future.

If for some reason you can't or won't redesign the tables then you will have
to use VBA code to change the control source of the answer field. The code
would go in the AfterUpdate event of the options combo box. I don't
understand what you mean by "Each field has 3 records to reflect a choice of
answers a,b or c and a corresponding answer to suit", so I can't help you
with the code.
 
Thank you for your advice - do you think it will work if
I create a separate table for each questionnaire, within
that table, a separate field for each question with each
field being a look up field where I can select the
response depending of whether the customer has selected a
b or c.

(on the questionaire that customer has to select 1a or 1b
or 1c and I have a set script which I have to send back
depending of which one he has selected)

Many thanks Marion
 
No, I don't think a separate table for each questionnaire would be a good
idea. Also, lookup fields in tables are considered to be an abomination and
to be avoided at all costs:

http://www.mvps.org/access/lookupfields.htm

I suspect you are used to working with spreadsheets, and are thinking of a
database as being like a spreadsheet, which it isn't. From what you have
said I think you need five related tables:

Questionnaires:
QuestionnaireID (Primary key)
QuestionnaireDescription
QuestionnaireDate
Other fields about the questionnaire

Questions:
QuestionID (Primary key)
QuestionnaireID (Foreign key, linked to the Questionnaires table)
QuestionNo
QuestionText

QuestionOptions
QuestionOptionID (Primary key)
QuestionID (Foreign key, linked to the Questions table)
QuestionOptionNo (ie a, b or c)
Prompt (ie the text presented to the user)
Answer (ie the text you send back if this response is selected)

Users:
UserID (Primary key)
FirstName
LastName
Other fields about your users that you may need (eg contact details)

Responses:
ResponseID (Primary Key)
UserID (Foreign key linked to the Users table)
QuestionOptionID (Foreign key linked to the QuestionOptions table)
This table stores the answers given by each user to each question.

So, each questionnaire can have any number of questions in the Questions
table. Each question can have any number of options in the QuestionOptions
table (you only need 3, but this is not really important to the table
design). For each option you store the number (a, b or c), the prompt that
is shown to the user and the text that you will return.

You will need to store some information about each of the users (people
filling in the questionnaires), and their answers to each question. These
are stored in the Users and Responses table. The users table should be
straightforward. I'm not quite sure how I'd design the Responses table -
you'll want to ensure that there can only be one response to each question
and the design shown above gives you no way of enforcing this, so you'll
probably want to include QuestionId in this table too (then you can create a
unique index on the UserID and QuestionID fields and this will guarantee
only one response per question).

(Sorry for the delay in posting this message - my ISP's news service has
gone down, and I'll not be logging on again for about 24 hours!)
 
Back
Top