Change RowSource with VB

[mattc66 via AccessMonster.com]

Private Sub Form_Current()

Dim strFilter As String

If Me.Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

Me.Item.RowSource = strFilter

End Sub

 

[mattc66 via AccessMonster.com]

I forgot to finish my posting. I want to change the row source on a drop down
box depending on the me.company. The me.Item.RowSource is in a subform. I
placed the below code on the subforms Form_Current event. It's not working.

Any Suggestions?


Private Sub Form_Current()

Dim strFilter As String

If Me.Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

Me.Item.RowSource = strFilter

End Sub

 

[Dirk Goldgar]

I forgot to finish my posting. I want to change the row source on a
drop down box depending on the me.company. The me.Item.RowSource is
in a subform. I placed the below code on the subforms Form_Current
event. It's not working.

Any Suggestions?


Private Sub Form_Current()

Dim strFilter As String

If Me.Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

Me.Item.RowSource = strFilter

End Sub

So "Item" is the name of a combo box on a subform, and this code is
running on the main form? What's the name of the subform control, on
the main form, that is displaying the subform?

 

[mattc66 via AccessMonster.com]

I have the code on the Subforms OnCurrent Event.

The Main form is called frmOrders.

The Main Form fldName = Company and depending on what the value is of this
fld determines what Row Source the ITEM combo box has on the subform.

I forgot to finish my posting. I want to change the row source on a
drop down box depending on the me.company. The me.Item.RowSource is

[quoted text clipped - 16 lines]

End Sub

So "Item" is the name of a combo box on a subform, and this code is
running on the main form? What's the name of the subform control, on
the main form, that is displaying the subform?

 

[Dirk Goldgar]

I have the code on the Subforms OnCurrent Event.

The Main form is called frmOrders.

The Main Form fldName = Company and depending on what the value is of
this fld determines what Row Source the ITEM combo box has on the
subform.

It doesn't really make sense to me to have this code in the subform's
Current event, since the Company is on the parent form. But if that's
the way you have it, then the relevant code would be:

If Me.Parent!Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Parent!Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

 

[mattc66 via AccessMonster.com]

I agree - but I don't know the best place to have the code. Since its a drop
down box I think I will move the code to the Click event on the dropdown box.

Matt

I have the code on the Subforms OnCurrent Event.

[quoted text clipped - 3 lines]

this fld determines what Row Source the ITEM combo box has on the
subform.

It doesn't really make sense to me to have this code in the subform's
Current event, since the Company is on the parent form. But if that's
the way you have it, then the relevant code would be:

If Me.Parent!Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Parent!Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

 

[Dirk Goldgar]

I agree - but I don't know the best place to have the code. Since its
a drop down box I think I will move the code to the Click event on
the dropdown box.

I don't think that's the best choice. I was thinking the Current event
of the main form would be better, probably combined with the AfterUpdate
event of the Company control on the main form. Note that, if you move
the code to the event(s) of the main form, you need to modify it to
something like this:

Dim strFilter As String

If Me.Company = "C1" Then
strFilter = "qryLookUpItem_C1"
ElseIf Me.Company = "C2" Then
strFilter = "qryLookUpItem_C2"
End If

Me!NameOfSubform!Item.RowSource = strFilter

For "NameOfSubform" you'd have to substitute the name of the subform
control on the main form, which may or may not be the same as the name
of the form it displays.