Hello. I have created a subform (record source is a query that combines the tables below) that contains questions from a tblQuestion and a text box for responses(control source Response). In the response text box, I want to enter new responses and record them in the Response table. Currently, I can only view a response and am not able to add a new response. TblResponse should contain all the info for each response to each question answered by many respondents.

tblQuestion
QstnID
QstnText (actual question text)
QstnType

tblResponse
ResponseID
QuestionID
Response

How can I enter and record new responses?

Thanks!

The subform is based on tblResponse only and linked to the main form via QuestionID. Now you can add data to the Response control in the subform.

hth,
Jack

I am getting the following error message:

You cannot add or change a record because a related record is required in table 'tblOperator'

tblOperator
ResponseID
LN
FN

Do I need to include tblOperator in my query?

The problem here is that you are enforcing referential integrity between tblOperator and tblResponse and the ResponseID you are putting into tblResponse does not exist in tblOperator.

If I were doing this, I would use a Main Form with TWO subforms. The main form would contain data from tblOperator. The first subform would allow selection of the question, the second would allow entry of the responses.

I would have a combo box on the main form to allow selecting the operator. That would in turn filter the subforms to show the correct data from their data sources.

tblOperator
OperatorID (PK and auto)
LastName
FirstName

tblQuestions
QuestionID (PK and auto)
Question

tblRepsonses
ResponsesID (PK and auto)
QuestionID (FK)
Response

tblOperatorResponses
OperatorResponsesID (PK and auto)
OperatorID (FK)
QuestionID (FK)
ResponseID (FK)

How do I set up the queries for the subforms? I want to be able to open the form and enter a new Operator's last name, the survey date, and the machine type. On the subform I want to see the QuestionID and Questions from tblQuestions and have a text box to enter the data. There will be no combo boxes because a majority of the answers are going to be opinion(narrative). Can I do this by a query or do I need to do something else?

Thank you!

Your main form should be based on the question table and should show one question (record) at a time. Have the operators sign in and save their ID in a control on the form. The subform is based on tblOperatorResponses with the Master/Child links to QuestionID and OperatorID.

hth,
Jack

Should I also include the Master/Child link to ResponseID?

Since a response is not a selection from an existing set of possible answers then you can remove the Response table and change ResponseID in tblOperatorsResponses to a text or memo field.

Jack

Hello, I have created the main form with the QuestionID and QuestionText but I can't get the records to navigate. I know it's something simple but I'm having trouble with it.

The main form should be bound to the Question table and you should have no trouble moving from question to question.

What happens when you try to move to a different record? Do you get an error message? Is the form based on the table or a query based on the table?

I have attached a simple survey example. Hopefully this will assist you with your questionnaire...

Jack