Creating skip rule in MS Access?

[Arif]

I am developing a database in MS Access. I want to create a skip rule. Following is an example of the problem:

The first question: 'Are you a student'. The next question is 'which school do you go to?'. and then the next one is 'how old are you?'.

In the above context if someone answers 'No' to the first question then the second question does not apply to that person so we can jump to the third question. I want to know how to create this skip rule in MS Access so after entering 'no' to the first question if one hit 'tab' it will take him/ her to the third question while skip the second one.

Any help is appreciated.

Thanks

[comteck]

You simply use an IF..THEN statement, with each question as part of a SUB. For example:

Private Sub Command5_Click()
If Question1 = A Then
Call SUB1
Else
Call SUB2
End If
End Sub

Private Sub SUB1()
'(Enter Code Here for Question 2)
End Sub

Private Sub SUB2()
'(Enter Code Here for Question 3)
End Sub

Hope this helps.
comteck

[Arif]

Thanks for your help. However I am a novice in Access. Where to write the commands? Do I have to use Macro?

Thanks

Aha

[comteck]

No.. Code is not written in a macro. In this case, the code is written using "Code Builder".

Create a new Command Button (turn off the wizard - or cancel when it appears). Still in design mode, right click on the button, and select "Build Event". Then choose "Code Builder". This will automatically create an OnClick Event Procedure for that button. Visual Basic will open, and the following code will appear (or something similar):

Private Sub Command5_Click()

End Sub

You then put the following code in between these 2 lines:

If Question1 = A Then
Call SUB1
Else
Call SUB2
End If

The final code will be:

Private Sub Command5_Click()
If Question1 = A Then
Call SUB1
Else
Call SUB2
End If
End Sub

Then, while still in Visual Basic, add the other subs. The final code will be:

Private Sub Command5_Click()
If Question1 = A Then
Call SUB1
Else
Call SUB2
End If
End Sub

Private Sub SUB1()
'(Enter Code Here for Question 2)
End Sub

Private Sub SUB2()
'(Enter Code Here for Question 3)
End Sub

Hope this makes sense.
comteck

[MMcCarthy]

An easier solution would be to set the Visible property of textbox 'which school do you go to?' to No on the form. (Right click on the textbox in design view and go to properties)

Then in the Events section of the properties for the textbox 'Are you a student' click on the arrow and go to [Event Procedure] then click on the button beside this with the dots on it. The VBA code editor will open with a procedure as below:

Private Sub textboxName_AfterUpdate()

End Sub

Add the line of code

If Me.textboxName = 'Yes' then
Me.otherTextBox.Visible = True
Else
Me.otherTextBox.Visible = False
End If

Hope this helps

[Arif]

I really appreciate your help. I tried the way you suggested but unfortunately it didn't work. I think I am doing some mistakes somewhere. When you said '(Enter Code Here for Question 2), what should be the code? Should I just put the question there as it is typed in the database or I have to put some code? and if it is, then what could be the code?

It would even be helpful if there is a similar example that I can follow.

Thanks a lot.

[comteck]

When I say "Enter Code for Question 2", that means that the code for question 2 would have to be inserted there.

In order to give exact details of what the code would be, I would have to know exactly what the questions are, as well as the name of all the textboxes, etc that are involved.

If you want to provide all the details.. maybe include tables as well with field names.

comteck

[Arif]

Thanks.

I am furnishing a section from my database. You can see the skip instructions. I want to build these skip rules in the database.

Following are the questions that are in the database.

1.a. Did you cultivate any farmland last season? Yes...........1
No.............2 If no skip to Q. 2

1.b. Did you cultivate rice in last season? Yes...........1
No............2

Area rice cultivated _____________ (sq meter)
Quantity harvested ______________ (kilograms)

1.b. Did you cultivate maize in last season? Yes...........1
No............2

Area maize cultivated _____________ (sq meter)
Quantity harvested ______________ (kilograms)

2.a Did you have a home garden last season? Yes...........1
No.............2
If no skip to Q. 3.a.1

2.b.1 Did you buy seeds from market? Yes...........1
No.............2
2.b.2 Did you get seeds from demo plot? Yes...........1
No.............2
2.b.3 Did you get seed from local nursery? Yes...........1
No.............2
3.a.1 Did you do vermi composting last year? Yes...........1
No.............2

Thanks

[Arif]

Dear mmccarthy

Thanks for your help. Finally I was able to make it work. Thanks. I am wondering whether there is a way to fade the questions that I want to skip instead of make them invisible?

[MMcCarthy]

Instead of using the .Visible command you could use the .Enabled command.

Set it to True when you want the user to be able to access it and to False when you want it greyed out and unavailable to the user.

Private Sub textboxName_AfterUpdate()

If Me.textboxName = 'Yes' then
Me.otherTextBox.Enabled = True
Else
Me.otherTextBox.Enabled = False
End If

End Sub

[Arif]

Thanks a lot. This was helpful.