Blank out field based on selected criteria

[Paul15]

Hi all, hope you can help.

I have a user input form, on which there are two option buttons. On selecting either, I have the code to blank out certain other N/A input fields (turn red and N/A in the field). Speeds up data input by blanking out fields of no importance for the user. This works. Code below:

*******************************************************************
'Option button to mark out unwanted fields

Private Sub optRailPort_Click()

Me.cboVess.Text = ""
Me.cboVess.Enabled = False
Me.txtTMode.Text = ""
Me.txtTMode.Enabled = True

If cboVess.Enabled = False Then cboVess.BackColor = vbRed

' to place N/A in disabled field

If cboVess.Enabled = False Then cboVess.Text = "N/A"
If txtTMode.Enabled = True Then txtTMode.Text = "Rail"

'to clear other option from use

If optRailPort.Enabled = True Then
Me.optSeaPort.Visible = False

End If

End Sub

************************************************************************

I now wish to expand this idea where a user selects a particular entry in a Combo Box. There are two possibilities in the ComboBox, "Accompanied" and " Unaccompanied". Where "Unaccompanied" is selected I wish a user input text field, 'Nam2', to be blanked vbRed and have N/A as text entered in that field. Where "Accompanied" is selected I wish no action to be taken with the Nam2 field.

I have been playing with this code to try and get it to work:

'**************************************************
'to blank out Accompanied Adult if pax is unaccompanied

'Private Sub Accompanied_Change()

'Me.Accompanied.Text = "Unnaccompanied"
'Me.Nam2.Text = ""

'If Accompanied.Enabled = "Unnaccompanied" Then Nam2.BackColor = vbRed





'End Sub

'********************************************************

Any help please

 

[dmt32]

Hi,
try following update to your code & see if does what you want

Private Sub Accompanied_Change()

    With Me.Nam2
        .BackColor = IIf(Me.Accompanied.Text = "Unaccompanied", vbRed, vbWhite)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With

End Sub

Dave

 

[Paul15]

Hi Dave,

Great start. Does what I want, bar one issue. The background colour of my user input fields is &H00C0FFFF&, a pale yellow. I do not want this to change on selection of the "Accompanied" option, things should remain the same as default. With your code it goes vbWhite. I have tried playing with your code but don't seem to be able to get that last bit to work.

Many thanks in advance and best wishes

 

[dmt32]

Try,

Private Sub Accompanied_Change()

    With Me.Nam2
        .BackColor = IIf(Me.Accompanied.Text = "Unaccompanied", vbRed, &HC0FFFF)
        .Locked = CBool(.BackColor = vbRed)
        .Text = IIf(.Locked, "N/A", "")
    End With

End Sub

Dave

 

[Paul15]

Awesome. nearly had it but left a & in. Many thanks. all sorted now

Regards

 

[dmt32]

Awesome. nearly had it but left a & in. Many thanks. all sorted now

Regards

welcome & glad all resolved

Dave