Combo Box text required (1 Viewer)

RickS · Apr 10, 2004

Help Please,
I have three combo boxes on a form, all three use the lookup from a table for the info in them. Combo box one is set for required to have info in it (can only choose 1,2 or 3). That was easy, now is it possible to have combo box 2 set for required, if combo box one's choice is 1, and combo box 3 set for required, if combo box one's choice is 2, and combo box 2 & 3 both set for required if combo box one's choice is 3.

And also would like if possible not to be able to have info in combo box 2 if combo box one's choice is 2, and the same for combo box 3 if combo box one's choice is 1.

Hope this makes sense to you.
Thanks for any help.
RickS

dcx693 · Apr 10, 2004

All these choice possibilities boil down to having some code in the form check to see if the required combo boxes have valid choices before saving and updating the record. For that, use the form's before update event to run some validation code. Something along the lines of:

Code:

Select Case Me.combo1
    Case is = 1
        If IsNull(Me.combo2) Then
            Cancel=True
            MsgBox "You need to fill in combo2"
            Beep
        End If
    Case is = 2
        If IsNull(Me.combo3) Then
            Cancel=True
            MsgBox "You need to fill in combo3"
            Beep
        End If
    Case is = 3
        If IsNull(Me.combo2) Or IsNull(Me.combo3) Then
            Cancel=True
            MsgBox "You need to fill in combo2 and combo3"
            Beep
        End If
End Select

RichO · Apr 10, 2004

When you say "set for required" do you mean the combo box requires a selection to be made before the user can proceed?

It sounds like you need alot of "If...Then" statements in VBA in the After Update events for these combo boxes.

And also would like if possible not to be able to have info in combo box 2 if combo box one's choice is 2, and the same for combo box 3 if combo box one's choice is 1.

In this case I would suggest disabling the combo box as opposed to blanking out its record source.

An example of this would be... in the After Update event of combo one...

Code:

If Me.Combo1 = "2" Then
   Me.Combo2.Enabled = False
Else
   Me.Combo2.Enabled = True
End If

If Me.Combo1 = "1" Then
   Me.Combo3.Enabled = False
Else
   Me.Combo3.Enabled = True
End If

To meet your requirements, you just need many pieces of VBA code similar to the one above.

RickS · Apr 10, 2004

Still need help

Okay here's what I tried:

Private Sub Duty_Tri_Annual_Choice_AfterUpdate()

If Me.Duty_Tri_Annual_Choice = "Duty Station" Then
Me.Duty_Stations.Enabled = True
Else
Me.Duty_Stations.Enabled = False
End If
If Me.Duty_Tri_Annual_Choice = "Tri-Annual" Then
Me.Tri_Annual_Catagorie.Enabled = True
Else
Me.Tri_Annual_Catagorie.Enabled = False
End If
If Me.Duty_Tri_Annual_Choice = "Duty and Tri-Annual" Then
Me.Duty_Stations.Enabled = True
Me.Tri_Annual_Catagorie.Enabled = True
Else
Me.Duty_Stations.Enabled = False
Me.Tri_Annual_Catagorie.Enabled = False
End If
End Sub

The Last statement works:
(if Duty_Tri_Annual_Choice = "Duty and Tri-Annual" Then)
it enables both combo boxes (Duty Stations & Tri Annual Catagorie)
But the first two statements don't work the combo boxes stay locked
Can anyone tell me whats wrong here?
Thank you,
RickS

RichO · Apr 10, 2004

One thing you may want to check is that there are no leading or trailing spaces in your selection. It appears that the code is not recognizing "Duty Station" or "Tri-Annual" as selections in the combo box.

Have you ever used the step debugger? This is a great way to find out exactly where the code is going. Another thing to try is to insert a MsgBox at the start of your code that will tell you what value your combo box holds when the code runs.

MsgBox "XXX" & Me.Duty_Tri_Annual_Choice & "XXX"

I put in the XXX because that will tell you if your string has any extra spaces or not. You should see "XXXDuty StationXXX". If not you can use the Trim$ function to clean it up.

RickS · Apr 10, 2004

RichO, or anyone,
I tried the msg box and other suggestions. With the msg box at the begining of code, with each choice it does bring up the items each time like this XXXDuty StationXXX, XXXTri-AnnualXXX, or XXXDuty and Tri-AnnualXXX. But the last statement is the only one that enables the other combo boxes (Duty_Stations & Tri-Annual Catagorie) The first two statements still don't enable any combo boxes. Any other suggestions or other ways to do this?

Thanks Alot for help
RickS

RichO · Apr 11, 2004

OK I think I've spotted your problem. If the combo box result is Duty Station, the code enables the box but then the else code from "Duty and Tri-Annual" disables it. We'll have to re-work the code a bit.

Code:

If Instr(Me.Duty_Tri_Annual_Choice, "Duty") > 0 Then
     Me.Duty_Stations.Enabled = True
Else
     Me.Duty_Stations.Enabled = False
End If
If Instr(Me.Duty_Tri_Annual_Choice, "Tri-Annual") > 0 Then
     Me.Tri_Annual_Catagorie.Enabled = True
Else
     Me.Tri_Annual_Catagorie.Enabled = False
End If

This is checking for the occurence of the word "Duty" in the text box. This will catch both "Duty Stations" and "Duty And Tri-Annual". Same goes for Tri-Annual.

Try that out.

RickS · Apr 11, 2004

It Worked

RichO,
Thank you very much it worked, It was driving me crazy. I new at writing code and still have a lot to learn.
Again thanks
Rick

RickS · Apr 11, 2004

One more question on this. Is there a way to have Duty station & Tri-Annual Sub catagorie combo boxes disabled on entering form? As it is now if I go to one of those combo boxes first I can enter info. I don't want info to be able to be entered there until a selection has been made in the combo box Duty/Tri-annual catagorie first.
Thanks Rick

RichO · Apr 11, 2004

2 ways to do it.

Either insert the lines of code that disable the boxes into the Form On Open event or go into the properties table for the combo box, select the data tab and change Enabled to No.

Combo Box text required (1 Viewer)

RickS

Registered User.

dcx693

Registered User.

RichO

Registered Yoozer

RickS

Registered User.

RichO

Registered Yoozer

RickS

Registered User.

RichO

Registered Yoozer

RickS

Registered User.

RickS

Registered User.

RichO

Registered Yoozer

Similar threads

Users who are viewing this thread