Combo box enabling a text box

GraemeG

Registered User.
Local time
Today, 09:05
Joined
Jan 22, 2011
Messages
212
Hello.

I have combo boxes with several options.
i.e.
FIELD: Entrance Doors Flats
None
Standard Door
Fire Door
Direct Access

If a surveyor selects anything other than "None" they must answer a Quantity and Renew Year txtbox fields.

Is there some code to disable these two txt boxes until they select a type of door. Or keep disabled if they select None?

But also I want an afterform update to validate the code something like below: However it flags up at the minute regardless of the answer.

Code:
Private Sub Form_AfterUpdate()
If Not Me.[Entrance Doors - Flats (Fire Doors)] = "None" And Nz(Me.[EntranceDoorsFlatsRenewYear], "") = "" Then
  MsgBox "If an Entrance Door is Selected a Renew Year Must be Entered!"
  [EntranceDoorsFlatsRenewYear].SetFocus
  [EntranceDoorsFlatsRenewYear].BackColour = vbRed
  Cancel = True
  Exit Sub
 End If
End Sub

Also in this code if not answered it backcolours red. Once answered can this be returned to white?

Thanks
 
If Not Me.[Entrance Doors - Flats (Fire Doors)] = "None" And Nz(Me.[EntranceDoorsFlatsRenewYear], "") = "" Then
MsgBox "If an Entrance Door is Selected a Renew Year Must be Entered!"
[EntranceDoorsFlatsRenewYear].SetFocus
[EntranceDoorsFlatsRenewYear].BackColour = vbRed
Cancel = True
Exit Sub
End If

If Me.[Entrance Doors - Flats (Fire Doors)] is the name of your combo box I would personally rename it for brevity to something like Me.CboFireDoor

Then

Code:
If Trim(Me.CboFireDoor & "") <> "None"  OR Trim(Me.CboFireDoor & "") <> "" Then

 .......

End If
 
Renamed. But still getting error and flags up red if "None" is selected.

But regardless of this code. Is there anything to disable the textbox if None is selected?
 
Have you tried stepping through the code? what does it look like now?
 
Yeah. it does not need debug it just does not recognise the combo box specific response.

Code:
If Trim (Me.[EntranceDoorsFlats], "") <> "None" And Nz(Me.[EntranceDoorsFlatsRenewYear], "") = "" Then
MsgBox "If an Entrance Door is Selected a Renew Year Must be Entered!"
[EntranceDoorsFlatsRenewYear].SetFocus
[EntranceDoorsFlatsRenewYear].BackColour = vbRed
Cancel = True
Exit Sub
End If
 
But it must be the combobox has a reponse AND the renew year is blank for the validation to make sense
 
Form's Before Update event:
Code:
Private Sub Form_[COLOR=Blue][B]BeforeUpdate[/B][/COLOR](Cancel As Integer)
    If Me.[COLOR=Red]ComboBox[/COLOR] <> "None" And Len(Me.[COLOR=Red]QuantityTextbox[/COLOR] & Me.[COLOR=Red]RenewYearTextbox[/COLOR] & "") = 0 Then
        Cancel = True
        MsgBox "If an Entrance Door is Selected a Renew Year Must be Entered!"
        
        If Len(Me.[COLOR=Red]QuantityTextbox[/COLOR] & "") = 0 Then
            Me.[COLOR=Red]QuantityTextbox[/COLOR].SetFocus
            Me.[COLOR=Red]QuantityTextbox[/COLOR].BackColour = vbRed
        Else
            Me.[COLOR=Red]RenewYearTextbox[/COLOR].SetFocus
            Me.[COLOR=Red]RenewYearTextbox[/COLOR].BackColour = vbRed
        End If
     End If
End Sub
Amend all the red bits.

By the way, is there a blank selection in the combo box too?
 
Thanks vbaInet!

I utilised that code. But still flags up when option "None" is selected in the combo box. Also if quant and renew year are missing, it only flags up red the quant not both.

No there is no blank option in the Combobox, just 4 options with Limit to List and no edits selected.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EntranceDoorsFlats <> "None" And Len(Me.EntranceDoorsFlatsQuant & Me.EntranceDoorsFlatsRenewYear & "") = 0 Then
        Cancel = True
        MsgBox "If an Entrance Door is Selected a Renew Year Must be Entered!"
        
        If Len(Me.EntranceDoorsFlatsQuant & "") = 0 Then
            Me.EntranceDoorsFlatsQuant.SetFocus
            Me.EntranceDoorsFlatsQuant.BackColor = vbRed
        Else
            Me.EntranceDoorsFlatsRenewYear.SetFocus
            Me.EntranceDoorsFlatsRenewYear.BackColor = vbRed
        End If
     End If
End Sub
 
I utilised that code. But still flags up when option "None"
If this is the case then the value of the combo box is not None. I suspect that you have two columns in your combo box.

Let's have a look at your db.
 
If this is the case then the value of the combo box is not None.
I was referring to the value returned by the VALUE property of the combo box, not the fact that it has None in the Row Source.

The bound column should have been 1 not 0, plus I amended the first line of the code to include OR like DCrake mentioned.

See attached.
 

Attachments

I was referring to the value returned by the VALUE property of the combo box, not the fact that it has None in the Row Source.

The bound column should have been 1 not 0, plus I amended the first line of the code to include OR like DCrake mentioned.

See attached.

Thats fantastic! Thankyou
Just one more thing lol. It only highlights one missing field at a time is there a way to make them all flag up Red? Also is there a way to turn the backColor back to white once the required data has been entered?
 
Instead of the IF...ELSE block (i.e. the inner IF block) have them as two separate IF blocks:
Code:
If this = that then
    .... code here
end if

if this = that then
     ... code here
end if

Reset the color on After Update event of the control checking the Len(Me.Control & "") like already shown.
 
Sorry.
But this now does not pick up the renew if missed.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EntranceDoorsFlats.Value <> "None" And Len(Me.EntranceDoorsFlatsQuant & "") = 0 Then
        Cancel = True
        MsgBox "Entrance Door Selected: Quant Required!"
        If Len(Me.EntranceDoorsFlatsQuant & "") = 0 Then
            Me.EntranceDoorsFlatsQuant.SetFocus
            Me.EntranceDoorsFlatsQuant.BackColor = vbRed
        ElseIf Len(Me.EntranceDoorsFlatsQuant & "") <> 0 Then
            Me.EntranceDoorsFlatsQuant.SetFocus
            Me.EntranceDoorsFlatsQuant.BackColor = vbWhite
        End If
    End If
    If Me.EntranceDoorsFlats.Value <> "None" And Len(Me.EntranceDoorsFlatsRenewYear & "") = 0 Then
        Cancel = True
        If (Me.EntranceDoorsFlatsRenewYear & "") = 0 Then
            Me.EntranceDoorsFlatsRenewYear.SetFocus
            Me.EntranceDoorsFlatsRenewYear.BackColor = vbRed
        ElseIf Len(Me.EntranceDoorsFlatsRenewYear & "") <> 0 Then
            Me.EntranceDoorsFlatsRenewYear.SetFocus
            Me.EntranceDoorsFlatsRenewYear.BackColor = vbWhite
        End If
    End If
End Sub
 
I mentioned the INNER IF block only.

Sorry yeah they are two seperate IFs. the else if is too turn it back to white. But I can remove this and it still does not work.
i,e
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.EntranceDoorsFlats.Value <> "None" And Len(Me.EntranceDoorsFlatsQuant & "") = 0 Then
        Cancel = True
        MsgBox "Entrance Door Selected: Quant Required!"
        If Len(Me.EntranceDoorsFlatsQuant & "") = 0 Then
            Me.EntranceDoorsFlatsQuant.SetFocus
            Me.EntranceDoorsFlatsQuant.BackColor = vbRed
        End If
    End If
    If Me.EntranceDoorsFlats.Value <> "None" And Len(Me.EntranceDoorsFlatsRenewYear & "") = 0 Then
        Cancel = True
        If (Me.EntranceDoorsFlatsRenewYear & "") = 0 Then
            Me.EntranceDoorsFlatsRenewYear.SetFocus
            Me.EntranceDoorsFlatsRenewYear.BackColor = vbRed
        End If
    End If
End Sub
 
That's incorrect. In the original code there are two IF blocks, the OUTTER and then the INNER. The INNER block is the one that changes the color. That is the one you should turn from an IF... ELSE to two separate IF... END IF blocks. One for the second textbox and the other for the first textbox, in that order.
 

Users who are viewing this thread

Back
Top Bottom