Variable for holding a calculation for a text box

[Patentinv]

Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.

Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.

It was suggested to me to have a Variable hold the calculation, I’ve been trying to write a variable but nothing seems to be working, I’ve been trying to place this variable I made into the after update and before update of the text box Nothing works I get all 0.00 in the form header text box, Here’s the code for the variable I made, can some one see if it looks correct.

Dim calcVariable
calcVariable = [Tot25yrcomp] + [Me.feltstot]
mat25yr = calcVariable

I was wondering if I need to say Me.mat25yr = calcVariable

Thanks--Any help will be greatly appreciated.

 

[lupins46]

Use the Form_Beforeupdate event procedure:

me.myTotalControl = [Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
Set the controlsource of control to the field you want to bind it to.

 

[TheAceMan1]

How are ya Patentinv . . .

If you havn't heard/read it yet, [blue]calculations are rarely stored in a table[/blue], nor is it suggested you do so. [purple]Since calculations should always come out right[/purple], display is all thats required. Also, consider the [blue]unecessary overhead[/blue] in storing this data!

There are exceptions (like accounting for a previous balance in a banking statement), but they are rare . . .

I'm sure you have your reasons, but I'd forget storage in this matter and move on . . .

See Ya! . . . . . .

 

[Patentinv]

Hi Lupins46,
I did as you said I did not have any luck here is what I did.
Me.mat30yr = [Tot30yrcomp] + [feltstot]
i placed this in the forms after update event procedure.

mat30yr is a text box in the form header that has a control source = txt30yrtotl

Do you see any thing I did wrong?

Thanks--for your help

 

[lupins46]

What I said:
'Use the Form_Beforeupdate event procedure:'

What you said:
'i placed this in the forms after update event procedure'

 

[Patentinv]

Hi lupins46,
You're idea may work in some cases but not mine, but thanks.
I need a dynamic approach not static like only when the form loads.

Hi AceMan1,
I'm doing well, how are you? Before I take you're advice and move on, I have one more idea:

I'LL take an invisible textbox control (say txtTot) on my form and bind it to a field in my table.
And then in the lost event procedure in the calculated text box I'll enter Me.txtTot = Me.mat25yr

When i do this I must click on the mat25yr text box to activate the population of the txtTot box. Which is not acceptable the user will never no to do this, is there a way
to refresh or activate this procedure with out having to click on the mat25yr text box?

Thanks--Any help is very appreciated

 

[PHV]

In the BeforeUpdate event procedure of the form:
Me.txtTot = Me.mat25yr

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

Patentinv . . .

I believe your not quite getting [blue]lupins46[/blue] reference to the forms [blue]BeforeUpdate[/blue] event. [purple]This event occurs just before a record is saved, which usually happens when the user navigates to another record or closes the form.[/purple]

Here's a method that should work:
[ol][li]Instantiate a new field in the table to hold the calculation (for now, lets name the field [blue]MyCalc[/blue]).[/li]
[li]In form design view, make sure [blue]MyCalc[/blue] appears in the Field List

, then bind the calculated unbound textbox to [blue]MyCalc[/blue].[/li]
[li]Now from the [blue]AfterUpdate event of each textbox involved[/blue] in the calculation, [purple]call a common routine that validates proper data for each of these textboxes and updates [blue]MyCalc[/blue] approrpriately![/purple]. Something like:

[blue]Public Sub Calc1()
   If IsNumeric(Me!Textbox1) And IsNumeric(Me!Textbox2) And IsNumeric(Me!Textbox3) Then
      Me!MyCalc = Me!Textbox3 + Me!Textbox2 + Me!Textbox3
   Else
      Me!MyCalc = Null
   End If
End Sub[/blue]

The call from each [blue]AfterUpdate[/blue] event would be:

[blue]   Call Calc1[/blue]

[/li][/ol]
In this way [blue]MyCalc[/blue] is updated on the fly.

You may also want to include a final check in the [blue]BeforeUpdate[/blue] event of the form, validating a calculation has been written to [blue]MyCalc[/blue]. If it fails, you can use the [purple]Cancel[/purple] property to roll back saving.:

[blue] If Not IsNumeric(Me!MyCalc) Then [purple]Cancel[/purple] = True[/blue]

[blue]Your thoughts! . . .[/blue]

See Ya! . . . . . .

 

[Patentinv]

Hi AceMan1,
You always come up with a great suggestion, Sorry it has taken me so long to respond I have been here looking and studying this code, So to understand it.

I actually have it working and it works perfect, except there are many combo boxes (did not work correctly when I placed them in the text boxes) that will have the call Calc1 in the afterupdate procedure and there are many text boxes in the form header with the Public Sub Calc1() in it.

If I place the Public Sub Calc1()in only one text box in the form header it works great. How ever as soon as I place it in more than 1 i get an error ambiguous name detected Calc1

Here's the code in the mat30yr in the form header
Private Sub mat30yr_AfterUpdate()
Public Sub Calc1()
If IsNumeric(Me!Tot30yrcomp) And IsNumeric(Me!feltstot) Then
Me!mat30yr = Me!Tot30yrcomp + Me!feltstot
Else
Me!mat30yr = Null
End If
End Sub

Maybe I could place the Public Sub Calc1() in 1 place to to
have it called but how will it know where to save the values?

here's a print screen of program

http://www.roofmart.net/RoofEst10105.asp

Notice the felt types each have text box below them these are the text box that gets added to each roof type in the form headers text box's where the Public Sub Calc1() is located and all the combo boxes in the print screen will have a call Calc1 in there After_Update procedure.

I'll keep trying to figure it out, you are by far the best programmer that has helped me, and I really appreciate all your help with out you I would be completely frustrated and probably wondering if I can accomplish buling this roof estimator program.

Thanks AceMan1 -- I really appreciate your help

 

[TheAceMan1]

Patentinv . . .

No! No! No! . . . all routines are seperate in the forms code module. You should have something similar to:

[blue][b]Public Sub Calc1()[/b]
   If IsNumeric(Me!Textbox1) And IsNumeric(Me!Textbox2) And IsNumeric(Me!Textbox3) Then
      Me!MyCalc = Me!Textbox3 + Me!Textbox2 + Me!Textbox3
   Else
      Me!MyCalc = Null
   End If
[b]End Sub

Private Sub TextboxName1_AfterUpdate()[/b]
   Call Calc1
[b]End Sub

Private Sub TextboxName2_AfterUpdate()[/b]
   Call Calc1
[b]End Sub

Private Sub TextboxName3_AfterUpdate()[/b]
   Call Calc1
[b]End Sub

Private Sub ComboboxName1_AfterUpdate()[/b]
   Call Calc1
[b]End Sub[/b][/blue]

As for the comboboxes you test in Calc1, you may need to use the [blue]Column[/blue] property to get the right column:

[blue]   Me!ComboName.Column(1)[/blue]

Be aware: [blue]column index starts at zero[/blue], and includes any hidden columns (widths set to zero in the [purple]Column Widths[/purple] property).

See Ya! . . . . . .

 

[Patentinv]

Hi, AceMan1
I think I understand I will place all the text boxes that make up the calculation in the Public Sub Calc1()
(Me!Textbox1)+(Me!Textbox1)would be (Me!Tot25yrcomp)+(Me!feltstot)etc. all text boxes that make up the calculion.

And then I will place the text boxes names in the form header into the textboxname1_AfterUpdate below.
So what happens is when the user selects the roof type it will activate these form header text boxes and they will call the routine above and save it in each of the form headers bound text box fields.
(Form header text box)
Private Sub TextboxName1_AfterUpdate()
Call Calc1
End Sub

You wrote: all routines are seperate in the forms code module. Where is this, Is it an event procedure, if so which one?

I'll see if I can get it to work it sounds like a really good idea. I really appreciate your help, Sorry about the late response, I was trying to get the invisible box idea I had to work, It populates the form header boxes even when the roof type is not chosen, so it won't work.

Thanks--AceMan1--I wish I could reward you some way for all your help.

 

[TheAceMan1]

Patentinv . . .

In VBA, you write code in modules. There are two types of modules, [purple]Class & Standard[/purple]. [purple]Standard Modules[/purple] are those modules you create in the Modules Window of the DB. [purple]Class Modules[/purple] for you right now, are the [purple]modules tied to forms & reports.[/purple] Each form/report has one. This is often called [blue]code behind forms/reports.[/blue]

To edit code in any module, you use the [blue]Visual Basic Editor[/blue] or [blue]VBE[/blue]. There are several ways to get to a module for editing. The rest of this discussion will refer to the Class Module of the form your working with. This should give you a little background in this area:
[ol][li]In [blue]form design view[/blue] click the code button

. This takes you to the Class Module of the form (or code behind the form as its called) and the VBE editor. This is where all your code [blue]Events, and User Defined Routines/Functions[/blue] go or reside. [blue]User Defined[/blue] are routines/functions you write yourself . . . like Calac1. For the code I've prescribed . . . [purple]you should have code nowhere else![/purple][/li]
[li]To get back to form view hit [purple]Alt+Q[/purple].[/li]
[li]To get to an event:
[ol a][li]If the [blue]properies window[/blue] is not already open, double-click the little square box just to the left of the ruler, or select the toolbar button

.[/li]
[li]Select the [blue]Events Tab[/blue][/li]
[li]Select the control of interest.[/li]
[li]Put the cursor on the line for the particular event (lets say [blue]AfterUpdate[/blue]). If [purple]event[/purple] appears in the dropdown list, select it and click the three elipses

just to the right. If [purple]event[/purple] doesn't appear, just click the three elipses.

This will take you to the event in the code module (already predefined but with no code in the body of the event). Looks like:

[blue]Private Sub [purple][b]ControlNameHere[/b][/purple]_AfterUpdate()

End Sub[/blue]

The body of the event is where you insert any of your prescribed code . . . like:

[blue]Private Sub [purple][b]ControlNameHere[/b][/purple]_AfterUpdate()
   Call Calc1
End Sub[/blue]

[/li][/ol][/li][/ol]

Special Note: I don't know what it is your doing in the Header of the form, or this invisible control, but operationally there not required and all you need is the code behind the form. Here's the simple interface operational sequence:
[ol][li]As the user enters data in those controls involved in the calculation, the [blue]AfterUpdate[/blue] event of the specific control at the time, calls [purple]Calc1[/purple].[/li]
[li][purple]Calc1[/purple] [purple]validates data for all controls involved in the calculation:[/purple]
[ol a][li]If validation passes, the calculation is performed and MyCalc is updated[/li]
[li]If validation fails, MyCalc is updated with Null.[/li][/ol][/li][/ol]
So MyCalc only shows data if [purple]all necessary controls in the calculation have validated data.[/purple]


[blue]Before you exercise the form, be sure to remove this entering of names in textboxes to prevent interaction.[/blue]

Let me know the results . . .

See Ya! . . . . . .

 

[Patentinv]

Hi Aceman1
I don't believe this will work in my case, but I really appreciate your help. My whole form interacts with comboboxes,
that populate text boxes and gets displayed in the form header. So I'm kinda confused what calls what, I just know the total have to get displayed in the form header and it would be nice to have it stored in a field in the forms bound table.

I have a feeling it's not going to work in my case. But you are still the best, Thank you very much for all your help.

Sorry about the late response I upgraded from win 2k to XP
and it would not recognize some hard drives, but I finally got it worked out.

This issue you have been helping me with is a minor improvement, in my program it will work fine with out this text box being bound to a table, The user will just have to refresh the combobox to have the roof type in the form header get displayed again.

What i did was I made each text box in the form header in visisble or visible = No, so when form loads all text boxes are invisible, Now I set and on click event procedure to
Me.mat25yr.Visible = True so when the user decides they want this type roof and click on this combobox it becomes visible, Otherwise all the other roof types in the form header would show and it would look cluttered, it's all because of my poor design in the beggining, I have
this in each one of the material text boxes per roof type across the form header
=[Tot30yrcomp]+[Totfelt1536]+[Totfelt3036] with many more being added, so what was happening was even if the use did not choose say med shakes the med shakes would still get populated along with every other roof type across the whole form header, So I made them invisible except for the one that was chosen.
But, when you close the form or just search for a specific record and filter to it all roof types would be blank again so the user will have to goto the combobox roof type and click it activate the on click procedure again.
If I could get these values saved to a field in each text box under roof type in form header or just any number i was going to try and write a SQL statement Select all text boxes
from table where 0=not visible, and any other value = visible. because by default all text boxes have a default of 0.

You are probably laughing, and I don't blame you, this is all based form not knowing VB code.

Here is a print screen of my program again.

http://www.roofmart.net/RoofEst10105.asp

Thanks AceMan1, I really appreciate your help.