Form results influencing the next form field.

[brobertson89]

Hi All!



New to the forum and new to Access so excuse the naivety.

In a form I want to be able to tick a box and it produce more options. So for example if I tick a box (on a form) next to a label "Do you want a fast car?" (which is a yes/no data type for the field name "Do you want a fast car?") it will then bring up another field in my table for example "which type of fast car?".

Also I want to be able to change the options dependent on what is selected in a combo box. For example if you selected "Subaru" out of the combo box for the field "which type of fast car?" the next field, which is "what model fast car?" would automatically only show makes of Subaru's not all the other makes that are listed in that combo box.

I would prefer to use macros for this. Can you please help.

Cheers,

Brandon

[June7]

This is cascading or dependent controls. Review http://www.datapigtechnologies.com/f...combobox2.html

Code can set controls as Visible or Enabled.

However, be aware this will not work nice if form is set to Continuous or Datasheet View.

Conditional Formatting can be used to set textboxes and comboboxes as Enabled/NotEnabled for any form view.

[brobertson89]

Thank you for your fast reply June7. The cascading control flash presentation is very handy. How do you use conditional formatting, all I can seem to be able to do is change the format of the text, ie. font, colours, etc. ?

[June7]

Open a Conditional Formatting rule, next to the Font Color dropdown is a tiny little toggle box, hover cursor over it, popup should say Enable. Click to change setting.

[brobertson89]

This enables the formatting ie. font, colours, etc. to be whatever you set it. I want to be able to make other fields appear when the condition is met.

[June7]

Would have to set up rule on the textbox that you want to 'appear'. The control will be Enabled if condition is met. Condition could be the state or content of another textbox.

[brobertson89]

Okay I tried creating a macro:

If [Forms]![JobInfoF]![Number of Barrels Required]=1 Then
SetProperty
Property: Visible
Value: Yes

however this just returns a type mismatch

The field Number of Barrels Required is set as a combo box with 1;2;3;4 and is on the JobInfoF form.

[June7]

I don't use macros, only VBA. I don't see anything wrong with the syntax.

Is the form in Continuous or Datasheet view?

[Number of Barrels Required] is a number type field?

[brobertson89]

Nope neither Continuous or Datasheet and [Number of Barrels Required] is a number type field.

If anyone knows what I am doing wrong, I would appreciate your help.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[brobertson89]

Okay so I figured this out and I thought I would reply with the code I used in case someone else was having the same problem. Number_of_Barrels_Required is the first combo box and BarrelSize / BarrelSerialNumber were the ones that I want to make visible or not.

Private Sub Number_of_Barrels_Required_AfterUpdate()
If Me.Number_of_Barrels_Required.Value >= 1 Then
Me.BarrelSize1.Visible = True
Me.BarrelSerialNumber1.Visible = True
Else
Me.BarrelSize1.Visible = False
Me.BarrelSerialNumber1.Visible = False
End If
If Me.Number_of_Barrels_Required.Value >= 2 Then
Me.BarrelSize2.Visible = True
Me.BarrelSerialNumber2.Visible = True
Else
Me.BarrelSize2.Visible = False
Me.BarrelSerialNumber2.Visible = False
End If

If Me.Number_of_Barrels_Required.Value >= 3 Then
Me.BarrelSize3.Visible = True
Me.BarrelSerialNumber3.Visible = True
Else
Me.BarrelSize3.Visible = False
Me.BarrelSerialNumber3.Visible = False
End If
If Me.Number_of_Barrels_Required.Value >= 4 Then
Me.BarrelSize4.Visible = True
Me.BarrelSerialNumber4.Visible = True
Else
Me.BarrelSize4.Visible = False
Me.BarrelSerialNumber4.Visible = False
End If
End Sub


Also for anyone wanting to do cascading combo boxes then I found this video very helpful: https://www.youtube.com/watch?v=I_wwlZofCgk

[brobertson89]

I have another issue related to this. When I have used the above code some of the labels disappear whilst others stay regardless of the conditions. Not sure what property it is to link the label to the combobox? Can someone help please. I have tried including a line like: Me.Label1111.Visible = True however VBA wouldn't allow it.

[June7]

Newly created data controls have an associated label. A label can be detached by deleting (ctrl X) and copying it back to the form (ctrl V). When a detached label first appears, it will have a little green triangle in upper left corner. This is an alert that the label has an issue (such as not associated with a data control). Once the form is closed and saved, the triangle will no longer appear. Select the label and click the dropdown and make choice, one of which is to associate it with a control.