I'm creating a very simple Access database with a table and corresponding form. For some questions on the form, I'd like to disable following questions, or hide them using branching logic.
For example, in my form I have a combobox question that asks: Are you a smoker? - "Yes", "No", "Prefer not to answer". The following question is: If yes, how often do you smoke? If they chose the answers "No" or "Prefer not to answer" for the first question, then I don't want the second question to be visible/enabled.
I've been searching for a way to do this and the easiest way seems to be setting the Visible property of textbox "If yes, how often do you smoke?" to No. After that, I go to question "Are you a smoker?" and go to Event Procedure in the Properties menu. This brings up a VBA code editor with the following text:
Option Compare Database
Private Sub Text969_Click()
End Sub
Private Sub Combo367_Click()
End Sub
I've been looking at different pages but I can't seem to get the code to work. For the particular question I'm asking, the name of the form is "Chronic Smokers" and the field for the first question is named "Are you a smoker." and the second question is named "If yes, how often." This is the code I've been trying and it doesn't work, but I can't seem to figure anything else out:
Option Compare Database
Private Sub Text969_Click()
End Sub
Private Sub Combo367_Click()
If Chronic Smokers.Combo367='Yes' then
Chronic Smokers.If yes, how often.Visible = True
Else
Chronic Smokers.If yes, how often.Visible = False
End if
End Sub
I think part of my problem is that I don't know the way the naming conventions or syntax for this code works. I have a feeling part of the problem is that I have blank spaces without underscores in the names If anybody can help me out with this, I'd really appreciate it!
VBA code could be as simple as Me.[how often].Visible = Me.Combo367 = "Yes". No If Then Else needed. Code will need to be in combobox AfterUpdate as well as form Current events, not Click event. Code will apply to ALL instances of control, not just the current record.
NOTE: use of Me qualifier is shorthand for form/report object code is behind.
If you prefer to use If Then Else, correct syntax would be:
With Me
If .Combo367 = "Yes" Then
.[how often].Visible = True
Else
.[how often].Visible = False
End if
End With
Suggest you explore Conditional Formatting. It allows to enable/disable textboxes and comboboxes dynamically per record without VBA. Controls will still be visible but 'greyed out'.
And yes, strongly advise not to use spaces nor punctuation/special characters (underscore is only exception) in naming convention nor reserved words as names. If you do, then enclose in [ ] as shown above. Better naming would be ChronicSmokers and HowOften. And give objects more meaningful names than the defaults assigned by Access.