Need to recalculate a field on a form if another field changes 3

[surfside1]

I have a form with a field that should change (recalculate) based on the values changed in other fields.

I created a new public sub such as:
Public Sub recalculateAll()
Call getEbayFees
Call getTotalCost
Call getLossAmount
Call getWinningBidProfit
Call getWinningBidProfitPercent
Call getEbayFees
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveForm, , acMenuVer70

End Sub

And I was going to call this from every input field that effects all of the totals in the functions listed above, and call it from the change event:

Private Sub Insertion_Charged_Change()
Call recalculateAll
End Sub

I change the value of an amount and I need to close the form, but when I go back it reflects the change.

I've had a similar issue when adding a new record and then display those records in a subform on a form that it goes back to when it closes and but until I do a refresh, which just a macro to close with save, it doesn't show the changes. Are these two situations similar and can I fix them both the same way?

Thanks!

 

[RuralGuy]

I would only call the recalc in the AfterUpdate event of a control.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[surfside1]

So what would I do in the Else statement where it says to call the recalc in the CheckForNumeric Sub?
Thanks!

 

[CaptainD]

Because I am doing the recalc in that event, does that mean I should not have the "Change" event?

Yes, that was ment to be in place of the "Change" event.

I do wonder though, if it's in the "Key_Press" event if it would fire the "recalculateAll" before the KeyAscii registers. Hmmm... Test time.

 

[CaptainD]

As I suspected, "recalculateAll" fires before the KeyAscii registers so I moved it to the "Change" event as you see it below and it works fine. Give that a try.

Option Compare Database

Private Sub Insertion_Charged_Change()
    Call recalculateAll
End Sub

Private Sub Insertion_Charged_KeyPress(KeyAscii As Integer)

        If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 13 And KeyAscii <> 8 And KeyAscii <> 46 Then
       [COLOR=green]'If not numeric cancel the action[/color]
                KeyAscii = 0
                
        End If
End Sub

Private Sub recalculateAll()
lblCalc.Caption = Insertion_Charged.Text * 2
End Sub

 

[surfside1]

If I want the recalulateAll to do this:

Public Sub recalculateAll()
If Me.Dirty Then Me.Dirty = False
Call getEbayFees
Call getTotalCost
'Call getTotalProfit
Call getLossAmount
Call getWinningBidProfit
Call getWinningBidProfitPercent
End Sub

Then where does the following fit in?
Private Sub recalculateAll()
lblCalc.Caption = Insertion_Charged.Text * 2
End Sub

First I used your code without that and I got the same original error when I entered a "." I didn't think I would want to not do all of those calls for the 'gets'

 

[CaptainD]

Then where does the following fit in?
Private Sub recalculateAll()
lblCalc.Caption = Insertion_Charged.Text * 2
End Sub

That was my code for testing since I don't have all the code for your "recalculateall sub"

Is this the error you are refering to?

Just too easy!!
Something else, if I have a textbox defined as currency (like the Insertion Fees above) and they want to edit the field to change it from $0.40 to $0.41, the only way I can change it is to highlight the 0 at the end then change it to one. If they want to just backspace so that it highlights the last 0, or just type over the entire field, I get an error. "The value you entered isn't valid for this field". Any ideas?

I set my test "Textbox" to currency and when I back space or press delete I get the same error. The reason is because the value is "" or "Nothing"

So I changed my "recalculateall()" to this and fixed the problem.

Private Sub Insertion_Charged_Change()
If Insertion_Charged.Text = "" Then
    Exit Sub
Else
    Call recalculateAll
End If
End Sub

 

[surfside1]

Mine is still not working if I put a "." I get that error. Here's my code for keypress: It works ok for backspace and space??

Private Sub Insertion_Charged_KeyPress(KeyAscii As Integer)
'Call CheckForNumeric
If (KeyAscii < 48 Or KeyAscii > 57) And _
KeyAscii <> 13 And KeyAscii <> 8 And KeyAscii <> 46 Then
'If not numeric cancel the action
KeyAscii = 0
End If
End Sub

 

[CaptainD]

Did you try my "change" event code?

It might also help if you trap the error in the sub it is thrown in.

 

[surfside1]

Yep,
Private Sub Insertion_Charged_Change()
If Insertion_Charged.Text = "" Then
Exit Sub
Else
Call recalculateAll
End If
End Sub

I won't be back on until tomorrow, but thanks for you continued help!

 

[CaptainD]

I played around with my version and found that if I enter just a "."I get the error because you can not do math on a "." by it's self so it throws an error. So, I fixed mine with this.

Private Sub Insertion_Charged_Change()
On Error GoTo ErrHandler
    If Insertion_Charged.Text = "" Then
        Exit Sub
    Else
        If Insertion_Charged.Text = "." Then
            Exit Sub
        Else
            Call recalculateAll
        End If
    
    End If
Exit Sub
ErrHandler:
    MsgBox "Error under Insertion_Change Event. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

 

[surfside1]

Ok, I finally had a moment to try what you said. And you are right. It will allow me to change $0.40 to $.50 but when I try to change it to $.55, it immediately changes it to $0.50 before I can put the other 5. And since I didn't notice and I enter the other 5, it looks like "5$.50". Is there something that I must do to get the cent field in also?

Strange?

 

[CaptainD]

I can't make mine do that. When I start typing in the text box the $ sign disappears and it allows me to enter a number where ever the cursor is located. When I exit the text box, the $ sign reappears where it should be.

 

[surfside1]

Interesting..... I'll have to look at that later. Thanks for responding.

 

[surfside1]

Ok, I even created a new currency field in the table and the form to see of my field was corrupted, I entered on the field (which has focus) and type .4 and I get $0.40 and the cursor is before the $. I can go to the cent 0 field and enter 5 and it is good. What do you think I need to do to get the $ to display first so I can enter the .40? I'm totally stumped.....thanks!

 

[CaptainD]

On mine I set the text box to a currency field. I though that is what you had done. As I mention earlier, when I type, my dollar sign disapears and then when I exit the text box it reappears in the correct location.

 

[surfside1]

I have a question about the sequence of when an event occurs and I'm wondering if there is any reading material that defines that.

I'm thinking that because the change event is what triggers the recalculation of other fields due to that particular field changing, that maybe once you enter any change which could be the .4 of a .45 field that it just does that recalulation and doesn't wait until you enter the .05 of the .45?

Just a thought. Did you try the additional cent field value also? Because it works like a charm with the .4.

But I would like to know if there is anything that I can read about the sequence.

As always - thanks!

 

[CaptainD]

That is correct, any change to the field fires the event.
Entering and leaving does not.

If you put some type of code in the change event and place a break point in it, you will see that it fires at the key press. (Tab and Enter do not fire the event)

As far as documentation, I'd have to Google that one.

But playing with it, it does as stated above.

 

[surfside1]

I found the descriptions of the events in the Access Bible and tried the after update instead of after change and that solved it! Thanks so much for your diligence with this!

 

[CaptainD]

Glad to it works!