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]

Your Sub should be in the class module for the form and need not be public unless you plan to call it from outside of the form (like from another form). It sounds like both problems are because records are not saved yet. Make the first line of your sub:
If Me.Dirty Then Me.Dirty = False
and it will make sure the records are saved. What version of Access are you using? If ac2K+ the you need to switch to the RunCommand replacements for the DoMenu commands. Maybe this link will help:

http://home.clara.net/tkwickenden/domenuitem.htm

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

 

[surfside1]

Thanks, I am using 2003, does that list mean SaveForm is ok? I guess I'm not familiar with DoCmd versus RunCommand.

Also can you explain If Me.Dirty Then Me.Dirty = False?

Is Me.Dirty the control name so what it is doing is making sure the control takes the value that's there and updates the table & form?

Thanks, RuralGuy

 

[PHV]

Me.Dirty = False
does the same thing as:
DoCmd.RunCommand acCmdSaveRecord

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[surfside1]

Good grief folks, I had no idea that existed, but then you knew I wouldn't PHV.

Thanks, I'll try that in the am! Appreciate it!

 

[surfside1]

Well, I'm back. What I'm confused about now is where do you put the If Me.Dirty Then Me.Dirty = False.

Also can you use this statement multiple times in the code so that multiple calculations also get updated. This is what I tried first. Below is the event for 1 of the fees to perform a Recalc Function that affects EbayFees.

Private Sub Insertion_Charged_Change()
Call recalculateAll
End Sub

Maybe this isn't the correct event to use?

Below is my Recalc Function that calls all the subs doing various other calculations. The individual totals effect each other. Example, the result of getEbayFees updates the Ebay Fees. The result of getTotalCost uses the value of Ebay Fees and adds that and other amounts to get it's total. The first one I added the Dirty statement was EbayFees, which Insertion_Fee affects, and that is below.

Public Sub recalculateAll()
Call getEbayFees
Call getTotalCost
Call getLossAmount
Call getWinningBidProfit
Call getWinningBidProfitPercent
Call getEbayFees
End Sub

Public Function getEbayFees() As Currency
Dim EBF As Currency
EBF = Me.Insertion_Charged _
+ Me.Picture_Charged _
+ Me.Gallery_Charged _
+ Me.Reserve_Charged _
+ Me.Duration_Fee _
+ Me.Final_Value_Fee
Me.Ebay_Fees = EBF
If Me.Dirty Then Me.Dirty = False
End Function

Now here's the kicker. Works great, when I add .01 to Insertion_Fee the Ebay_Fees increases by .01. But when I try to add the same Me.Dirty to one of the other calculations that is also effected by Ebay_Fees, such as TotalCost the Ebay Fees works great but the TotalCost does nothing. Then if I decrease the Insertion Fee by .01 the Ebay_Fees decrease by .01 and the Total_Cost increases by .01. If I do another amount the same things happens. Sounds like the sequence of when things happen get messed up?

That other calculation is below:

Public Function getTotalCost() As Currency
Dim TC As Currency
TC = Me.Ebay_Fees _
+ Me.Pay_Pal_Fees _
+ Me.Product_Cost
Me.Total_Cost = TC
If Me.Dirty Then Me.Dirty = False
End Function

Sound crazy or am I really missing something? Man if you made it to the end of this question........

 

[RuralGuy]

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

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

 

[surfside1]

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?

 

[RuralGuy]

You should be able to highlight everything and the type in .41 and have it work. Is that not the case?

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

 

[surfside1]

I get this:

Run-time error ‘2113:
The value you entered isn’t valid for this field.

And when I debug it is highlighting in yellow Me.Dirty = False. When I see what the values are Me.Dirty value = True and Me.Dirty = False.

The format for the field is Currency and decimal is 2.

Ideas? Thanks!

 

[RuralGuy]

In the table the field has a DataType. What is it? It has nothing to do with Format, which is a display option. Is it Single, Double, Long? The highlighting of the Me.Dirty you described does not make any sense to me.

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

 

[surfside1]

The field's data type is currency - 2 decimals. Here's the scenarios:

If I tab to that field and it has focus and is highlighted - above happens. When I exit debug there is a . only in the field. If I type 1 then it changes the field to $.10 then I need to highlight only the 0 to put a 5 to make it $.15

If I simply put the cursor at the end of the field & backspace it takes me to the beginning of the $.15 then I must use the --> key to get to the position, change and OK.

I was hoping just doing a backspace that it would remove the 5 - not a big deal - but normally it just removes the last digit. On text fields it does but not currency, not sure about number fields.

Just trying to make the data entry normal for the user. No big deal, just trying to understand, thanks.

 

[RuralGuy]

I like the Currency DataType and use it for all kinds of things instead of floating point numbers. I just tried tabbing into a Currency field, which highlights the entire field in my case, and changing the $14.24 that was in there to 1.24. When I exited the control it was changed to $1.24 as I would expect. It sounds like your control is not working as I would expect. Do you have Tools>Options>General tab>Name AutoCorrect turned OFF as it should be?

You should be able to use:
If Me.Dirty Then Me.Dirty = False
as many times as you want in your code without a conflict. Have you made sure there is no corruption in your db? A /decompile maybe?

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

 

[surfside1]

Log name AutoCorrect changes" is that what you meant? It is turned off.
And there are no compile errors. Is that what your decompile meant?

I just did a repair database, then tabbed to a field (which highlights) and type on highlighted amount and it gives that error again and highlights the Me.Dirty is True.

The thing that seems to be consistent is if I tab to a field and enter any value while it is highlighted, whether is a space or a . I get the error, then I close the error and do the same thing and the entry is accepted. If I tab to other fields I can also change their value. The first time I try to update a field on the form this happens, no matter what field I try first. Is there some sort of initialization that must take place when the form opens or something?

This recalculateAll is for every amount field that changes:

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

This is an example of the getEbayFees:
Public Function getEbayFees() As Currency
Dim EBF As Currency
EBF = Me.Insertion_Charged _
+ Me.Picture_Charged _
+ Me.Gallery_Charged _
+ Me.Reserve_Charged _
+ Me.Duration_Fee _
+ Me.Final_Value_Fee
Me.Ebay_Fees = EBF
End Function

Sound strange?

 

[RuralGuy]

Here's a couple of links for you to review:

http://allenbrowne.com/bug-03.html

http://www.granite.ab.ca/access/decompile.htm

I noticed you using what appeared to be the Change event of a control. Are you aware that this event fires *every* time there is a keystroke in that control?

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

 

[surfside1]

I just now thought about that and came upstairs to put the Me.Dirty statement somewhere else. Thanks, I'll let you know what I found.

 

[CaptainD]

Would this approach work.

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 then exit the sub[/color]
                KeyAscii = 0
                
        Else
        
            If KeyAscii = 13 Or KeyAscii = 8 Or KeyAscii = 46 Then
            [COLOR=green]'If backspace, decimal or delete, exit and allow to go through[/color]
                Exit Sub
                
            Else

                Call recalculateAll
            
            End If
                
                
        End If


End Sub

 

[surfside1]

Thanks! I tried modifying the Change event to call the following for one of the fields:

Private Sub Duration_Fee_Change()
KeyAscii = Me.Duration_Fee
Call CheckForNumeric
End Sub

and I have the CheckForNumber sub as:

Public Sub CheckForNumeric(KeyAscii As Integer)
'Private Sub Insertion_Charged_KeyPress(KeyAscii As Integer)
If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 13 And KeyAscii <> 8 And KeyAscii <> 46 Then
'If not numeric then exit the sub
KeyAscii = 0
Else
If KeyAscii = 13 Or KeyAscii = 8 Or KeyAscii = 46 Then
'If backspace, decimal or delete, exit and allow to go through
Exit Sub
Else
Call recalculateAll
End If
End If
End Sub

I wanted to have a public sub so that all fields would set KeyAscii to their own value. I'm probably clueless about this, but I get a compile error "argument not optional" when it hits Call CheckForNumeric. Where is my problem? Appreciate it!

 

[surfside1]

Ok, never mind, never mide, never mind, I'll check the event KeyPress!!!!

 

[surfside1]

Now I have tried both of these in the KeyPress event, you'll notice the first is now commented:

'Private Sub Insertion_Charged_Change()
' Call CheckForNumeric
'End Sub
Private Sub Insertion_Charged_KeyPress(KeyAscii As Integer)
'KeyAscii = Me.Insertion_Charged
Call CheckForNumeric
End Sub

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