MS Acces Help Again!!

Scottie_uk

I have a DB in access that has the following tables:


Questionnaire
QID INT P-Key
QuestionnaireID INT
QuestionNo INT
QuestionText MEMO
ResponseType TEXT
LikertScale INT


Responces
EvaluatorID INT C-Key
QID INT C-Key
LikertResponse INT
TxtResponce MEMO
Date Date

Evaluators
EvaluatorID INT P-Key
Name TEXT
School TEXT
Year INT
DateJoined Date


I want an access form to provide fields that will say list all the questions of a particular and provide space for input.

I have three combo boxes to select questionnaire,school and evaluator. I also have a date entry txt field that automatically places the date entered in the date fields of the table as a defult value.

However, when a record is created and the question number is entered I would like the relevant question to appear in the Question text field of the form.

I have written a VBA function that returns the question text associated with the question of the selected questionnaire. However, when I set the the value of the questionText text field all the text fields for the form update to the same thing.

How can I set this field on a per record basis??

Below is an example of the form and how I'd like it to look.

+------------------------+   +------------------+   +----------------------+
| Select Questionnaire |V|   | Select School  |V|   | Select Evaluator   |V|
+------------------------+   +------------------+   +-----------------------

			+----------------------------+
			|        Enter a Date        |
			+----------------------------+
			:	29/1/2009	     :
			+----------------------------+
--------------------------------------------------------------------------------

Qestion No   Queston Text    Respnce Type    Likert     TxtResp         Date
===========  ============   ==============   ======   =============  ==========
|  1	   | | I really |   |             |  |     |  |            | |29/1/09 |
|          | | like it. |   |	Likert	  |  |     |  |		   | |        |
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
|  2	   | | it is    |   |             |  |     |  |            | |29/1/09 |
|          | | good.    |   |	Likert	  |  |     |  |		   | |        |
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
|  3	   | | it helped|   |             |  |     |  |            | |29/1/09 |
|          | | me.      |   |	Likert	  |  |     |  |		   | |        |
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
|  4	   | | What did |   |             |  |     |  |            | |29/1/09 |
|          | | you like?|   |   Open	  |  |     |  |		   | |        |
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+
|  5	   | | it was   |   |             |  |     |  |            | |29/1/09 |
|          | | smell    |   |	Likert	  |  |     |  |		   | |        |
+----------+ +----------+   +-------------+  +-----+  +------------+ +--------+

monty.mole

Set the Control Source property of the Question Text text box to:

=DLookUp("QuestionText","Questionnaire","QID = [Forms]![frmQuestionnaire]![QID]")

(Assuming that frmQuestionnaire is the name of the form).

Alternatively, and better still, set the Record Source property of the form to the following query:

SELECT Q.QuestionNo, Q.QuestionText, Q.ResponseType, R.LikertResponse, R.TxtResponce, R.Date
FROM Questionnaire Q, Responces R, Evaluators E
WHERE Q.QID = R.QID AND R.EvaluatorID = E.EvaluatorID AND Q.QuestionnaireID = [Forms]![frmQuestionnaire]![cboQuestionnaire] AND E.School = [Forms]![frmQuestionnaire]![cboSchool] AND E.Name = [Forms]![frmQuestionnaire]![cboEvaluator]

(Assuming that cboQuestionnaire, cboSchool, and cboEvaluator are the names of your combo boxes).

Then set the Control Source property of your text fields to the corresponding fields of the query.

However, that query may turn out to be non-updatable; without actually trying it myself, I don't know.

Scottie_uk

Thanks Monty I really appreciate it. :)