Access 2016 - Dependent Fields

Question

Hi, I have created a DB and need some assistance in completing it. 

I have several questions and the end user can select either "yes" or "no". If they select "yes" I need it to produce a text box with specific guidance.

So, my question is this, what is the best way to go about this? Do I create a table, then a form? How do I get it to be dependent on the response?

For the purpose of this question I will provide an example:

Q: Is the project a result of a new process or procedure?

A: (If yes) Please complete form 223

  (If no) nothing

Just not sure of the best way to go about this.

Any help would be greatly appreciated.

D

Answer 1

HI DV, I'm an independent adviser and will try to help.

it would help us help you if you described the purpose of the application in more detail.

But from what you posted, you have a form with several checkboxes. It appears that you want to give people instructions based on their answer to the checkboxes. But that you aren't' necessarily storing their answers.

I would have a table of the responses. In the After Update event of the checkbox you would have code like:

If Me.chkQ1 Then

Me.txtQ1 = Dlookup("[Response]","tablename","Question = Q1")

End If

This would pull the stored response and put it in the text box.

This advice could change once I understand the application better.

Scott, you have helped me on many occasions. Thanks for the reply. 

I think you hit the nail right on the head. I think this will work perfectly. 

So I created the table with all of the questions, then on the form added a combo box for the yes/no/n/a of each question. Below it I added a text box and in the after update (code) I added your suggested code. Only question is how do I indicate which question specifically? You write Q1, but how does it know which one is which? Only thing I changed was the response ("Yes") and the table name. What else do I need to change, if anything?

Thank you.

Answer 2

HI DV, I'm an independent adviser and will try to help.

it would help us help you if you described the purpose of the application in more detail.

But from what you posted, you have a form with several checkboxes. It appears that you want to give people instructions based on their answer to the checkboxes. But that you aren't' necessarily storing their answers.

I would have a table of the responses. In the After Update event of the checkbox you would have code like:

If Me.chkQ1 Then

 Me.txtQ1 = Dlookup("[Response]","tablename","Question = Q1")

End If

This would pull the stored response and put it in the text box.

This advice could change once I understand the application better.

Answer 3

You stated "I have created a DB and need some assistance in completing it". Maybe you should tell us a little about what you have created.

Table structure is key to the success of your database. If you don't get it right, you will probably struggle to build a complete and functional application. My suggestion is to have each question create a record. Many people make the mistake of creating fields based on questions so more questions result in more fields.

Can you tell us a bit more about your requirements?

Answer 4

This sounds like a survey type application. I would create a table of questions with fields like:

tblQuestions

queQueID autonumber primary key

queText "Is this project the ...."

queIfYesText "Please complete form 223"

queIfNoText "Please complete form 223"

Can we assume you need to store the results? If so create a table for answers:

tblAnswers

ansAnsID autonumber primary key

ansRespondent possibly a link to a table of respondents

ansQueID link to tblQuestions.queQueID

ansAnswer probably text or whatever to store the yes/no

You could have code in the after update event of your answers that would display the If Text.

Thank you for your response. 

Sorry, bit confused, so I create a table and in DesignView I (for the sake of this example) add the above in a field name section? so everything will be dependent on this table?

Also, yes, to be stored.

Answer 5

This sounds like a survey type application. I would create a table of questions with fields like:

tblQuestions

queQueID autonumber primary key

queText "Is this project the ...."

queIfYesText "Please complete form 223"

queIfNoText "Please complete form 223"

Can we assume you need to store the results? If so create a table for answers:

tblAnswers

ansAnsID autonumber primary key

ansRespondent possibly a link to a table of respondents

ansQueID link to tblQuestions.queQueID

ansAnswer probably text or whatever to store the yes/no

You could have code in the after update event of your answers that would display the If Text.