Replace control source with actual form values

[DRH192]

Hi,

I am building a calculation tool which will house a number of different calculators. On each calculator there will be one field which has a control source set to use data provided elsewhere on the form.

A simple example is shown below
=([FlowRate]*[HoursUsedPerAnnum])*[RestrictionVolume]

In this example my form has 3 fields FlowRate, HoursUsedPerAnnum and RestrictionVolume.

I want to know how to create a string that shows the formula with the values entered on the form.

p.s. I want to create a function that I can use to do this, so I can re-use it when it comes to the next calculator/form.

Cheers

 

[Remou]

What did you try?

 

[DRH192]

Well to be honest I don't know how to approach it...

I am assuming I need to pass the form name to a function, then loop through all the controls on the form and replace the [FormControlName] part in the string with the value it has.

But can you do that?

I'm sorry, I don't like to post a question without giving it a go, but I really don't know where I should start with this. I have looked around this site and some others and couldn't find anything to help me get started.

cheers

 

[Remou]

I suppose that you could store a table of functions:
[tt]FName F
Add ctlA+ctlB
Subtract ctlA+ctlB[/tt]

Then, as you say, replace:

Sub Ans()
Z = Y(Forms!frmForm)
End Sub

Function Y(frm As Form)
X = DLookup("Formula", "tblTable", "FormulaName='b'")

For Each ctl In frm.Controls
    If InStr(X, ctl.Name) > 0 Then
       X = Replace(X, ctl.Name, ctl.Value)
    End If
Next

Y = Eval(X)
End Function

If you store the form name in the table:

[tt]Forms!frmF![A] * Forms!frmF! + Forms!frmF![C][/tt]

You can just Eval:

[tt]Z = Eval(DLookup("Formula", "tblTable", "FormulaName='a'"))[/tt]

 

[DRH192]

Thanks Remou,

This looks really usfull. I will take a close look at this after lunch and I'm sure I'll be posting my next question after that!

Thanks for your help this morning

 

[DRH192]

This is really good, I have had a close look and it is exactly what I was looking for. I made a couple of small changes and its perfect.

I changed the code to read:

Function Y(frm As Form)
Dim X As String
Dim Ctl As Control

'DIDN'T NEED THIS LINE
'= DLookup("Formula", "tblTable", "FormulaName='b'")

X = frm.Controls("CalcResult").ControlSource
For Each Ctl In frm.Controls
If InStr(X, Ctl.Name) > 0 Then
X = Replace(X, Ctl.Name, Ctl.Value)
End If
Next Ctl

Y = X

End Function

Then I called it by doing the following

MsgBox Y(Me)


I should be able to then call this function from any of the calculators provided the result control is always called "CalcResult".

Thanks!

 

[DRH192]

Remou,

I have stumbled upon my next problem and I'm wondering if you can help again!

I now want to loop through the controls, and if the control is a text box, add it to a string.

So using the same logic as before I started doing the following

For Each Ctl In frm.Controls
If Ctl.Type = TextBox Then
X = X & Ctl.Name & ": " & Ctl.Value & vbNewLine
End If
Next Ctl

but this returns nothing...

any ideas whats wrong?

cheers

 

[Remou]

That would be:

[tt]ctl.ControlType=acTextbox[/tt]

 

[DRH192]

Remou,

Do you know what I can do to solve this problem?

I am now at the stage of opening up my calculator forms...

Dim frmStr As String
frmStr = "CALC_" & SF_replaceAll(Me.CalcType, " ", "_")
DoCmd.OpenForm (frmStr)

Then I want to call my LoadCalc function (below) which requires a string type and a form type to be passed. The following code gives me an error (I assume because I am passing a string expression ?)

LoadCalc Me.FieldNotes, "[Forms]![" & frmStr & "]"

Is there any way I can basically pass a string expression of a form name to a form type???

I need to do this because I will be opening different calculators on each occasion.

Cheers

 

[Remou]

That would be:
Forms("frmStr")

 

[DRH192]

Remou,

Thanks for the previous answer, perfect.

I should post this as a seperate question but thought I would be cheeky and ask you as you seem to have all the answers!

Is there any reason why when using a DAO.Recordset to update/add a table record, I can only get the first line of a notes field to append to the memo field in which it should be going?

The enter key behaviour of the notes field is new line, and its a normal text box.

any ideas?

Many Thanks

 

[Remou]

Yes, it should be a new thread Include a little code, too, when you re-post.