automatically entering data

[Stephen]

I have a sales/products database that I track orders and
payments.

I have tables set up for:
Customers
Orders
Order Details
Payments, etc.

I created a screen, through a query, that shows me all
orders shipped today and allows me to put in a payment
amount for an order.

In the AfterUpdate I put in the following code to allow
the automatic input of the PaymentAmount into the Amount
Due Field.

Private Sub PaymentMethodID_AfterUpdate()


DoCmd.OpenForm "Orders by Customer Subform for
Payments", , , "[OrderID] = [Forms]![Daily Delivery
Posting Form].Form![OrderID]"

If IsNull(Me![PaymentAmount]) Then
Me![PaymentAmount] = [Forms]![Orders by Customer Subform
for Payments].Form![Amount Due]

End If
DoCmd.Close

Exit Sub
This works ...but it slows down the program because I
open and close a form (Orders by Customer Subform for
Payments) with every entry.

How can I get the info from a specific Record (determined
by OrderID) on the form (Orders by Customer Subform for
Payments) and have it automatically inserted into the
PaymentAmount field?


There must be a better way.
Please help.

Thanks
Stephen
..

 

[Reggie]

I would design an Update query. Add the table with the [PaymentAmount]
field that you are trying to update. Add the OrderID and the
[PaymentAmount] field to the design grid. Select Query/Update Query from the
main menu.

Criteria under the OrderID type in: [Forms]![YourFormName]![OrderID]
Criteria under PaymentAmount field type in: Is Null
Update To under PaymentAmount type in: [Forms]![YourFormName]![Amount Due]

In the AfterUpdate event replace your code with:

DoCmd.SetWarnings False
DoCmd.OpenQuery "MyUpdateQry", acNormal, acEdit
DoCmd.SetWarnings True

Hope it helps!