Limit second combo box to a certain value

[scopes456]

I seen how to set up dynamic combo box when you select a value in one combo box it limits the value in the second. I have two combo box cmb1 and cmb2. Cmb1 has days of the week (mon,tue,wed and so on).cmb2 has activities ( football,study time,tennis, free play).cmb1 and cmb2 does not have a relationship connection with each other. I would like when I select "Monday"on cmb1 the ONLY OPTION I CAN SELECT in cmb2 is "free play".

Also I would like when I select "Friday" on cmb1 on cmb2 I CANNOT SELECT "free play"

Many thanks

 

[arnelgp]

if your combos rowsource is value/list, then make the rowsource blank first:

Option Compare Database
Option Explicit

Private Sub Form_Load()
    FillCombo1
    FillCombo2
End Sub

Private Sub Combo1_AfterUpdate()

    Dim varText As Variant
    varText = Me.Combo1.Value
    FillCombo2
    With Me.Combo2
        Select Case Me.Combo1.Value
            Case "Monday"
                While .ListCount > 1
                    .RemoveItem 0
                Wend
            Case "Friday"
                .RemoveItem 3
        End Select
        .Value = ""
    End With
End Sub

Private Sub FillCombo1()
    Dim arr As Variant
    Dim item As Variant
    arr = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
    With Me.Combo1
        While .ListCount > 0
            .RemoveItem 0
        Wend
        For Each item In arr
            .AddItem item
        Next item
    End With
End Sub

Private Sub FillCombo2()
    Dim arr As Variant
    Dim item As Variant
    arr = Array("Football", "Study Time", "Tennis", "Free Play")
    With Me.Combo2
        While .ListCount > 0
            .RemoveItem 0
        Wend
        For Each item In arr
            .AddItem item
        Next item
    End With
End Sub

 

[scopes456]

arnelgp thank you for your reply.

Will the code be the same if cmb1 is getting its values from tbl1

and cmb2 is getting its values from tbl2

?

 

[scopes456]

how did you get "FILLCombo" options, i look under the event tab for the selected combobox and did not see that event

 

[arnelgp]

FillCombo is user define procedure that i created.

if you are using table/query as your combo recordsource:
1. cmb1:
rowsource: select [dayname field in tbl1] from tbl1
row source type: table/query

2. cmb2:
rowsource: blank
row source type: table/query

paste this on your form's code:

Private Sub FillCombo2()
Dim strSQL As String
Dim strCriteria As String
strSQL = "SELECT [fieldname from tb2] FROM tbl2"
Select Case (Me.Combo1.Value & "")
Case Is = "Monday"
strCriteria = " Where [fieldname from tb2] = 'Free Play'"
Case Is = "Friday"
strCriteria = " Where [fieldname from tb2] <> 'Free Play'"
End Select
strSQL = strSQL & strCriteria & ";"
Me.Combo2.RowSource = strSQL
Me.Combo2.Value = ""
End Sub

Private Sub Combo1_AfterUpdate()
FillCombo2
End Sub

Private Sub Form_Load()
FillCombo2
End Sub

'*********** end of code **************

just replace the blue color text with the correct field name in tbl2.