MS Access 2013

Posted on 2014-07-30
Last Modified: 2014-08-01
I have a report that is based solely on an SQL statement in the record source. It is very efficient - however, there are certain needs that need to be addressed for one particular customer.

In the report there is a number of calculated fields that produce the total price of each number of items, and an invoice total. there is a textbox control that displays a customer number. it is designated as field79 in the property sheet with a control source of TrxCust.  there are several hidden textbox controls that calculate the invoice total. They are as follows


Control Sources
=rSetValue("CustInv","InvoiceTotal","Continues...") - Field 102

Not hidden

=IIf(IsNumeric([InvoiceTotal]),"Total:","")  with one adjacent to that named "InvoiceTotal"

What I need to do is ad $20.00 to the InvoiceTotal when the field79 "TrxCust" is 27. There is no designated format for field 79 - so I cannot tell you if it is number or text - and Decimal places is set to Auto

I have tried different variations of code in the On Load property Like

If Me.Field79.Value = 27 Then
Me.InvoiceTotal.Value = InvoiceTotal + Text127.Value
End If
 With and without Value added - and I am lost - I've attached the SQL code that pulls the info for this Invoice report

Any help would be greatly appreciated
Question by:dawber39

    Author Comment

    I might add that there is a form to view this order that to is based on SQL - and the subform within it is based on a query that does not contain the Cust No.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    Without a sample file, and the explicit steps to recreate the desired result, ...I am at a loss here...

    I am sure that what you are asking is as simple as you explain, ...but without any context, ...we would be just guessing till we hit on it...

    A shot in the dark would be to try your code on the Format event of the section that contains these controls.

    LVL 48

    Accepted Solution

    This (guessing only) should do as controlsource:


    You should rename your textboxes to something meaningful like txtSurcharge.

    LVL 33

    Expert Comment

    I think Gustav probably solved your question but given that this is a calculated control, it is not being saved to the table.  So, if you actually need to record the surcharge, you'll need to give us a little more information.

    Author Closing Comment

    Thank you - you people are awesome as always
    LVL 48

    Expert Comment

    by:Gustav Brock
    You are welcome!


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now