Real Time Calculations

[jlindquist23]

I have a formula in a subform (also have the same formula in the main form) that counts how many boxes are checked in the Rerun column.

It will only calculate the total if I toggle the record forward and back on the main form.

How do I get that to calculate in real time?



I'd like to send an email from outlook when the count is greater than zero.
I have a field called RerunTorF that changes to a 1 when the count goes over zero.



The email does not trigger (I have checked the box to turn on Microsoft outlook 16.0 Object Library) . I've shown the code below:

Private Sub RerunTorF_Change()

Dim Msg As String

Msg = "All" & ",<P>" & _
"Heat treat line " & HeatTreatLine & " has parts that have been rerun. Please inspect for quality."

Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatHTML
.HTMLBody = "Msg"
.To "jlindquist@whirltronics.com"
.Subject = "HEAT TREAT LINE RERUNS " & Now()
.Send 'If you want it to send immediately
End With

Set M = Nothing
Set O = Nothing


End Sub

[kd2017]

You're missing an equal sign = in the .To line

.To = "jlindquist@whirltronics.com"

Your .HTMLBody should be set to Msg, not "Msg"

.HTMLBody = Msg

HeatTreatLine isn't declared (in this code snippit anyway)

As far as the rerun column calcs, where is the formula? what is the formula? What's triggering the formula calculation?

[jlindquist23]

Thank you for catching the code issues.

I don't quite understand how to fix the HeatTreatLine one.

The main form is called HTRecord.
The calculation is inside a subform called LoadData_Subform

The HeatTreatLine variable is outside the LoadData_Subform in the HTRecord. Help on how to declare that variable would be appreciated!

[Welshgasman]

Pass HeatTreatLine into the sub as a parameter, or make it a TempVar ?

[jlindquist23]

Thank you for catching the code issues.

I don't quite understand how to fix the HeatTreatLine one.

The main form is called HTRecord.
The calculation is inside a subform called LoadData_Subform

The HeatTreatLine variable is outside the LoadData_Subform in the HTRecord. Help on how to declare that variable would be appreciated!
The main form HTRecord that has the HeatTreatLine is pulling from a query called Joined_Qry

[jlindquist23]

I have the calculation in the form footer of the LoadData_Subform and seems to be updating now when I move to a new line.


new code is:

Private Sub Text74_AfterUpdate()
Dim Msg As String

Msg = "All" & ",<P>" & _
"Heat treat line " & Forms![HTRecord]![HeatTreatLine] & " has parts that have been rerun. Please inspect for quality."

'Remember to add REFERENCE to Microsoft Outlook Library'

Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatHTML
.HTMLBody = Msg
.Display = "jlindquist@whirltronics.com"
'.CC = "anyone@Whirltronics.com
'.BCC = "anyone@whirltronics.com
.Subject = "HEAT TREAT LINE RERUNS " & Now()
'Display -- if you want it to come up on the screen before it gets sent
.Send 'If you want it to send immediately
End With

Set M = Nothing
Set O = Nothing
End Sub

[kd2017]

Sounds like good news!

Are your issues resolved?

[pbaldy]

FYI I think your original issue was expecting the change event to fire when the control's value was being changed by an underlying calculation/requery. That event will only fire when the user changes the value in the textbox, not when it changes for other reasons. It looks like you found a good solution, the after update event of a control the user is changing.

[jlindquist23]

I still can't get the email part to work.

[kd2017]

I still can't get the email part to work.

For future reference you will usually get the best help if you can also tell us if your getting any error messages or anything like that. "it doesn't work" doesn't help us much on our end since we can't see what's going on at your end.

To me the issue appears to be that this

.Display = "jlindquist@whirltronics.com"

is supposed to be this:

.To = "jlindquist@whirltronics.com"

[jlindquist23]

Oh yes...my apologies.
I've tried both of those codes you show above
I get no error messages ... nothing happens

[Welshgasman]

Check the variables abd objects in the Locals window.

[Micron]

I get no error messages ... nothing happens

Respond to post and address what is in post 8? Your code works (does create an email with body text) as long as a value is substituted for the forms reference. That won't matter at your end.

[jlindquist23]

Can you say more about checking the variables and objects in the locals window?
Do I need to define the Forms![HTRecord]![HeatTreatLine] as a variable? See first part of code below.

I tried this and still nothing happens. No errors or emails sent or created.

Private Sub RerunCountMain_AfterUpdate()
Dim Msg As String
Dim HTLine As String

HTLine = Forms![HTRecord]![HeatTreatLine]

Msg = "All" & ",<P>" & _
"Heat treat line " '& HTLine & " has parts that have been rerun. Please inspect for quality."

[pbaldy]

Are you sure the code is even running? Throw in a message box or debug.print on the Msg variable and see if it gets triggered.