Results 1 to 2 of 2
2004-01-14, 07:25 #1
- 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.
2004-01-14, 10:07 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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]
<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.