form asking multiple questions - best way to add to the table. (1 Viewer)

[kacey8]

Hi all,

I have a database I am creating asking a client questions,

I have the following tables & fields

Table > Clients
- ClientID (Primary Key)
- DateAdded
- ClientName
- ClientRef
- Other Comment
- AdviserContact
- MSCContact

Table > Questions
- QuestionID
- ClientID - Foreign Key linked to ClientID in Clients table
- QuestionNum
- QuestionAnswer

There are 8 questions numbered 1-8

The form will consist of fields to do the following

Add
ClientName
ClientRef
OtherComments
AdviserContact
MSCContact
Question 1
Question 2
Question 3
Question 4
Question 5
Question 6
Question 7
Question 8

I want to make sure I have the cleanest way to input the data into the tables so what's the best way to go about it. I appreciate it's probably a novice question.

Getting ClientName, ClientRef, OtherComments, AdviserContact, MSCContact in the tables is easy, but I am unsure how once entered onto the text box on the form to get that into the table. I assume it needs to have the question number added when it's inserted into the table.

Sorry I really hope this makes sense.

 

[Ranman256]

I think the form will be a data sheet view,
There will be 1 question per line. (With possible infinite questions)

A query can validate to make sure all were answered.

 

[kacey8]

I assume I'll need it as a sub table as I have questions further up which will not form part of the data sheet?

This I'd have a form with the normal fields fields
Sub form with questions 1 - 8 which sits inside the first form?

 

[kacey8]

Hi,

So couple of questions,

Firstly, is it possible to have the sheet to populate 8 records when loaded rather than only show one and for more to appear when the first is filled in.

Secondly, the Questions column, questions run 1-8 in order without fail. How can I make the numbers auto populate in the field?

 

[kacey8]

So essential it'll look like this.

Ideally I'd like the rows (8) to load automatically when the form is opened with the values added to them in 1-8.

Can this be done via an VBA on open? if so where would I start?