Setting Control Source via another field

[Thaddeus]

How can I control the computation of one field via another...

In other words, I want to input the formula that will control TextboxA via Textbox/FieldB.

The motivation behind this is that I want to enable an end user who does not have the permission or knowledge to edit the properties and therefore the control source of a field to still be able to edit and modify the formula from another input feild on the same form.

The boss is expecting a solution soon... and I am at a loss... PLEASE HELP!!!!!

thaddeus@sherlockbuilders.com

 

[pono1]

T,

I can offer you not a final solution but perhaps something that will allow you to see out to the open.

Create a test table called, say, TblFormulas. Create one field in the table called Operators. Open the table and add three values under the Operator field: add, subtract, multiply.

Create a practice form, called, say, FrmChangeFormula. Create the following controls on the form:

1. An unbound textbox named txtValue1 (default value = 8)
2. An unbound textbox named txtValue2 (default value = 6)
3. An unbound textbox named txtformula
4. Use the wizard to create a combo box called, say, cboChangeSource, binding it to TblFormulas.

In the After Update event of the combo box, add the following code:

Private Sub CboChangeSource_AfterUpdate()

'NOTE to SELF: if the user chooses add
If Me.CboChangeFormula.Value = "add" Then

'change textbox control source to add values of two other textboxes
Me.TxtFormula.ControlSource = "=[TxtValue1]+[TxtValue2]"

'done here, so leave
Exit Sub
End If

'if user chooses subtract
If Me.CboChangeFormula.Value = "subtract" Then

Me.TxtFormula.ControlSource = "=[TxtValue1]-[TxtValue2]"

Exit Sub
End If

'if not add or subtract, then multiply
Me.TxtFormula.ControlSource = "=[TxtValue1]*[TxtValue2]"

End Sub

Fiddle with it and I'm sure, in time, you will have what you need.

Regards,
Tim

 

[Thaddeus]

THANKS!

Thanks for the help! I have it working now and the boss is happy... mucho thanks!!!