Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Thanked 0 Times in 0 Posts

    Form Calculated Field and Record Update to a Table (Access 2000)

    <P ID="edit" class=small>(Edited by tony510 on 14-Jan-04 07:25. )</P>Hi! I am new to access and need help. I formatted a form with these fields; Customer ID, Customer
    Name, Transaction Date, Billed Amount, Amount Paid, Unpaid Balance. This form is a data entry
    form to update all new records on "Orders Table". I have two issues that I need help for.
    a) Whenever Customer ID is entered, automatically it fills the form combo box for "Customer Name"
    and works perfectly. All the rest of the fields entered above updates the "Orders Table" except two
    fields. Customer Name and the Unpaid Balance. The Customer name on the form is derived from
    a Master Customer Table. Can you show how it is done so that the "Customer Name" is on the
    Orders Table? Do I need to do a record source expression and correct the column count on the
    field properties of the Orders Table? Please show how it is done.

    b. On the form under "Unpaid Balance" I created a formula such as; = [Billed Amount]-[Amount Paid]
    and calculates correctly on the form but does not update the "Orders Table" . I know that calculations
    should not be saved but this is a required history calculations so we can track history of open balances.
    Please show me on how to do this.

    I appreciate any assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Form Calculated Field and Record Update to a Table (Access 2000)

    a. I don't quite understand your description. I would use a combo box with two columns, corresponding to Customer ID (hidden) and Customer Name. The combo box is bound to the Customer ID field (by setting its Control Source to Customer ID), but displays the Customer Name. You don't need to store the Customer Name in the Orders table if you already have the Customer ID.

    b. If you want to store the unpaid balance in the table, the corresponding control should have the Unpaid Balance field as Control Source instead of a formula. Set its value in the Before Update event of the form:
    <UL><LI>Open the form in design view.
    <LI>Activate the Event tab of the Properties window.
    <LI>Click in the Before Update event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the builder button (the three dots to the right of the dropdown arrow.)
    <LI>The Visual Basic Editor will be activated, and the first and last lines of the event procedure will be created, ready for you to complete.
    <LI>Make it look like this, but with the actual names substituted if necessary:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.[Unpaid Balance] = Me.[Billed Amount] - Me.[Amount Paid]
    End Sub

    <LI>Close the module (Ctrl+F4) and quit the Visual Basic Editor (Alt+F4).
    <LI>Back in Access, close and save the form.[/list]When you modify either the Billed Amount or the Amount Paid, the Unpaid Balance will be updated when you move to another record or close the form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts