How do I change a textbox's control source while a form is open?

[radhika1990]

I have a Form CenterStock on which I have an unbound textbox "Text6". I need to change its control source based on the value of the field Supply (which is bound to the field Supply)

The Code for the control source (With Nz(DSum...)) worked when I had two separate textboxes (by putting it in directly in th control source property in design view mode of the 2 boxes), but its not working when I put this in my form's OnCurrent event.


Private Sub Form_Current()
If Me!Supply.Value = 1 Then
Text6.ControlSource = Nz(DSum("[QtySupplied]", "CenterIndentSupplyQuantity2", "[SupplyID]= Forms!CenterStock!SupplyID"), 0)

ElseIf Me!SupplyID.Value = 2 Then
Text6.ControlSource = Nz(DSum("[Quantity]", "ChangesInCenterStockMore", "[Supply]= Forms!CenterStock!SupplyID"), 0)

End If

End Sub

Please help me with this, or provide an alternate way to do this.

Any help will be appreciated. Thanks in advance,
Radhika

 

[radhika1990]

I got the syntax to work--

Private Sub Form_Current()
If Me!Supply.Value = 1 Then
Text6.ControlSource = "=Nz(DSum('[QtySupplied]', 'CenterIndentSupplyQuantity2', '[SupplyID]= Forms!CenterStock!SupplyID'), 0)"

ElseIf Me!Supply.Value = 2 Then
Text6.ControlSource = "=Nz(DSum('[Quantity]', 'ChangesInCenterStockMore', '[Supply]= Forms!CenterStock!SupplyID'), 0)"
End If
End Sub


But my form is in Datasheet view, and the textbox only changes value when I select the particular record with the given Supply

i.e If I have selected Supply.Value=1, then even the record with Supply=2, has the same calculated expression that the one with Supply=1 has.

Please help if you can.
Thanks