UtterAccess Discussion Forums > Refresh and show respective value on every record

Full Version: Refresh and show respective value on every record

UtterAccess Discussion Forums > Microsoft® Access > Access Forms

Acess_help

Oct 22 2004, 01:51 AM

I have a Yes/No field for Tax in my table. Records that have this field checked will have 5% of tax added to its total cost. I did a DLookup on the On_Current event of the form and it does not work. My code is as follows,

CODE

    If DLookup("[Tax]", "tblOrderInfoMain", "[PONumber]='" & txtPONumber & "'") = -1 Then
        txtTax = 0.05 * [SubFrmLookupRecords].[Form]![txtSumTotalCost]
        txtGrossTotal = 1.05 * [SubFrmLookupRecords].[Form]![txtSumTotalCost]

    ElseIf DLookup("[Tax]", "tblOrderInfoMain", "[PONumber]='" & txtPONumber & "'") = 0 Then
        txtTax = ""
        txtGrossTotal = txtFinalSum
    End If

Is it the wrong event to insert my code in? Grateful of any feedback and help. Thanks!

mishej

Oct 22 2004, 02:12 AM

I think that I would code this as so:

CODE

    Dim bTaxIncluded As Boolean, nTaxAmount As Currency

    ' determine if tax was added
    bTaxIncluded = Nz(DLookup("[Tax]", "tblOrderInfoMain", "[PONumber]='" & txtPONumber & "'"), False)

    ' figure tax amount
    nTaxAmount = IIf(bTaxIncluded, 0.05 * [SubFrmLookupRecords].[Form]![txtSumTotalCost], 0)
    txtGrossTotal = [SubFrmLookupRecords].[Form]![txtSumTotalCost] + nTaxAmount

but it's possible that I'm missing something. The logic here is that I first determine if tax
applies to this order. Then I figure the tax amount and add it to the Total Cost to get the
GrossTotal.

Acess_help

Oct 22 2004, 02:19 AM

Do I place that in the On_Current event of the form? One more question, what's the use of IIf?

mishej

Oct 22 2004, 02:39 AM

Yes, this would be in the OnCurrent event so that each record is evaluated as it is visited.

The IIF() function allows you to implement an IF..THEN structure within a function allowing you to return one of two different values dependent on the first argument. The first argument is evaluated as an expression. If it evaluates to TRUE the second param is returned; if FALSE the third param is returned.

In my example if bTaxIncluded is TRUE, you get the tax amount for the purchase. If it is FALSE you get zero.

Besides the OnCurrent event you should call this code if any item is added/deleted/modified in the invoice/purchase order.

Acess_help

Oct 22 2004, 02:45 AM

Hi John,

I added that to the onCurrent event but it doesnt show. It only worked after I added the code to the onClick event of a button on the form. I did a msgbox to see the value of nTaxAmount and it showed 0. Why is that so?

mishej

Oct 22 2004, 02:55 AM

Open the form in design mode and display its properties. On the event tab ensure the "[Event Procedure]" appears next to the OnCurrent event - this is the hookup between the form event and the code that executes when that event is raised.

nTaxAmount should only be zero if the [Tax] field for that PO is False or the txtSumTotalCost form control is zero.

You can use a Breakpoint to detemine what is happening on execution.

Acess_help

Oct 22 2004, 03:21 AM

Hmm txtSumTotalCost is not zero but I'm not sure why is nTaxAmount showing 0. I have attached a db of mine so you can see what's wrong with it. Hope you can understand my problem better! Cheers

Acess_help

Oct 24 2004, 08:48 PM

Does anyone have any ideas on how to solve my problem?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.