Populate Combobox on subform from start and end values from controls on another form?

[Ray Spackman]

Still learning in the finer points of Access and VBA and needing help here.

I have a tab control with two a subform (sfrm1) on one tab and a subform (sfrm2) on another tab.

I have a subform (sfrm1) with two numeric controls (SL) and (EL) whose values are chosen by the user.

I have another subform (sfrm2) that has a combo box control that needs to be populated with the values >= (SL) and <= (EL) with a step of 2.

This combo box needs to be populated with these values when the user selects the tab with (sfrm2) on it.

Example: User chooses 13 for (SL) and 21 for (EL) on he tab containg (sfrm1). When changing tabs to the one that contains (sfrm2), the como box needs to populate itself with the values of 13, 15, 17, 19, and 21.

Any help here would be appreciated and thank you in advance.

 

[VilaRestal]

You could put the code in the after update events of both numeric controls (?).

You would want the combobox to be set to Value List and don't Allow Value List Edits.

Then set it's RowSource something like this (in sfrm1's module):

Private Sub num1_AfterUpdate()
    UpdateComboList
End Sub

Private Sub num2_AfterUpdate()
    UpdateComboList
End Sub

Private Sub UpdateComboList()
    Dim i1 As Integer, i2 As Integer
    Dim s As String
    i1 = Nz(Me.num1,0)
    i2 = Nz(Me.num2,0)
    If i2 > i1 Then
        Dim i As Integer
        For i = i1 To i2 Step 2
            s = s & i
            If i < (i2 - 1) Then s = s & ";"
        Next i
    End If
    Me.Parent.sfrm2.Form.cbo1.RowSource = s
End Sub

Change num1, num2 and cbo1 accordingly.

You probably want some validation on num1 and num2 to make sure num1 < num2 (the code will set cbo1's list to nothing if they're not but it would better if the user couldn't set them the wrong way round) and that prevents users from putting a stupidly big number in num2 that would cause that loop to take ages (and probably crash access trying to put so many rows in a combobox).

And, what if cbo1 has a value that's not in the new list? Warn the user or wipe it or?

 

[Ray Spackman]

Thank you so much, my headache has disappeared. Works like charm with the only correction needed was changing your Me.Parent.sfrm2.Form.cbo1.RowSource = s
to Forms!Parent!sfrm2.Form!cbo1.RowSource = s