Access 2013 table not showing correct value

I am trying to SetProperty Value of a field in a table with the current value of a temporary variable.
I am using a Macro to do this.   My CUSTOMER table has a 'General Number with 3 decimals' field defined as ProRate.
I have a calculated 'temporary variable' named   tmpProRateAmount.   I use a 'message box' to display the result and in this particular case the result is    .08   .   This is correct and I now want to move this value into my table.
So I SetProperty of ProRate with the  Value   =[TempVars]![tmpProRateAmount]

Problem:   The value that shows in the CUSTOMER table is   0
I am not sure what I am doing wrong.    Any thoughts?

LQ
LVL 1
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
Can you please provide the code you are using.
0
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Please see attached document.  
I appreciate your quick response and willingness to help.
LQ
Macro.docx
0
MacroShadowCommented:
Try:
SetProperty (ProRate, Value, =FormatCurrency([TempVars]![tmpProRatedAmount]))

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
No luck.   It's very confusing because I have double checked all the formatting on the form, in the table and in the macro.   It's like the table field  PRORATE  is not recognizing the decimal values.  
Any other ideas?
0
MacroShadowCommented:
Try converting the macro to VBA. it is a whole easier to debug.
0
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
I went to the Macro screen and the 'convert to VBA' is grayed out.   Any idea why?
I may just try to rewrite in VBA, but I hate to go through the hassle if I don't have to.
0
MacroShadowCommented:
Click the Macro button under Create on the Ribbon. That will bring up the Macro Design window. Under the Action Catalog window, there is a section called "In this Database." Click down to the form which contains the macro to convert, then double-
click it. The macro will be displayed in the left hand window.  Click "Convert Macro's to Visual Basic".
0
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
OK...thank you for your help.    Here is the code.   I tried running it after and still don't have an answer.
I appreciate your continued support.    Also, now that I have the code, I'm trying to 'STEP INTO' so that I can follow it, however, I can't seem to get it to accept that command.  

Private Sub btnOneOffVIPBilling_Click()
On Error GoTo btnOneOffVIPBilling_Click_Err

    TempVars.Add "tmpProRatedAmount", FormatNumber(TempVars!tmpProRate / 12)
    If (NoOfModels_numeric <> TempVars!tmpNbrOfModels) Then
        If (NoOfModels_numeric <= 13) Then
            DoCmd.SetProperty "ProRate", , FormatCurrency(TempVars!tmpProRatedAmount)
            TempVars.Add "tmpVIPBilling", FormatCurrency((NoOfModels_numeric - TempVars!tmpNbrOfModels) * 75)
            DoCmd.SetProperty "ProRatedVIP", , FormatCurrency(TempVars!tmpVIPBilling * TempVars!tmpProRatedAmount)
            DoCmd.SetProperty "ProRateDate", , Date
            DoCmd.SetProperty "ProRateEmpID", , TempVars!tmpEmployeeID
            DoCmd.SetProperty "ProRateNoMachines", , NoOfModels_numeric - TempVars!tmpNbrOfModels
            Beep
            MsgBox TempVars!tmpProRatedAmount, vbOKOnly, ""
        Else
            TempVars.Add "tmpVIPBilling", FormatCurrency((((NoOfModels_numeric - 1) - TempVars!tmpNbrOfModels) * 75) + 25)
            DoCmd.SetProperty "ProRatedVIP", , FormatCurrency(TempVars!tmpVIPBilling * TempVars!tmpProRatedAmount)
            DoCmd.SetProperty "ProRateEmpID", , TempVars!tmpEmployeeID
            DoCmd.SetProperty "ProRateNoMachines", , NoOfModels_numeric - TempVars!tmpNbrOfModels
            DoCmd.SetProperty "ProRateDate", , Date
            DoCmd.SetProperty "ProRate", , FormatNumber(TempVars!tmpProRatedAmount)
        End If
    End If


btnOneOffVIPBilling_Click_Exit:
    Exit Sub

btnOneOffVIPBilling_Click_Err:
    MsgBox Error$
    Resume btnOneOffVIPBilling_Click_Exit

End Sub
0
MacroShadowCommented:
Try
Me.ProRate = TempVars!tmpProRatedAmount

Open in new window

Instead of
DoCmd.SetProperty "ProRate", , FormatCurrency(TempVars!tmpProRatedAmount)

Open in new window

0
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
I gave that a try and still no luck.  So I changed the field type from Numeric (in the table)   to   Short Text -- and I got the correct result.   Now I just need to get it converted to a numeric field so that I can use it in a calculation on the report.    I think there is a way to do that right?
0
MacroShadowCommented:
CLng(field) will convert text to numeric.
0
PatHartmanCommented:
'General Number with 3 decimals'
That is a format.  What is the data type? Integer and Long Integer (the default Number Field Size) are INTEGERS.  They do not support decimal values.  Change the data type to Currency (my personal choice if no more than 4 decimal digits are required since it avoids floating point errors) or change the Field Size to single or double depending on the magnitude of the number you need to store.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Linda QuintanaAnalyst, Business Process Development and Continuous ImprovementAuthor Commented:
Thank you Pat.    I did 'finally' figure that out this morning.   Sometimes one just needs a little sleep.  
I appreciate your response and have marked it as 'answered'.

LQ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.