Should I use "On load" Event Proceedure?

[dragongunner0351]

Greetings all, I have a bit of a problem. Let me begin by describing the structure. I have 3 Tables

1. Products
a. Hold info on products, Available Units, Beginning Balance, List Price, Sale Price, etc...

2. Orders
a. User input for processing customer orders.

3. Order Details
a. Finer details of customer orders, what products ordered, cost, how many shipped, etc...

I have 2 forms (Products, and Orders) and one subform (Order Details) The subform is embedded in both the Products form and Orders form and each have a relationship to one another Orders -> Order Details via OrderID and Products -> Order Details via Product ID.

The subform has a combo control (products) that has a control source to the Products Table. When a Product is selected the Product Name, List Price, Sale Price and Available Units autofill.

My problem is I can't get the Availble units to update from order to order. I have a control in the Products form called "BeginningBalance"; this was my starting point for assigning a value to the product (Qty) Then I have another control "Available Units" in the Products form. I need the Available units control to update based off of an Order. I can get it to work great ONCE! and after fumbling around I got it work however in order for the "Available Units" to Update I would have to click somewhere in the "Order Details Subform" embedded in the "Products" form".

Any help would be greatly appreciated and my apologizes if I have givin too much info and confused anyone.

 

[randy700]

How about the BeforeUpdate event of the quantity field? Maybe run an update query whenever the quantity is changed on the form.

Let's say your beginning quantity on hand is 100.
You enter 10 in the quantity field of your order form.
Update query changes quantity on hand to 90.

Now, you change the order to 5.
You will want to determine the difference and run another update query to reflect the new quantity on hand.

Haven't tested this yet.


Randy

 

[dragongunner0351]

Thanks for the reply Randy I will give it a shot

 

[dragongunner0351]

Randy is this the proper syntax for the update query
Me.[Qty Shipped].Requery?

 

[randy700]

You will have to do something like....

Private Sub TextBox_BeforeUpdate()
Dim strSQL As String
strSQL = "UPDATE tblProducts " & _
"SET AvailableUnits = AvailableUnits - " & _
me.txtQuantityShipped
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub



Randy

 

[dragongunner0351]

Hey Randy I'm getting the following error:

Run-time error '3144':
Syntax error in UPDATE statement

I made the following adjustsments to the code you sent so that they matched my controls and tables.

Dim strSQL As String
strSQL = "UPDATE Products " & _
"SET Available Units = Available Units - " & _
Me.Qty_Shipped
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[dragongunner0351]

I noticed a problem with the Available Units it should have been "Available_Units". My fault. I made the adj. and now I get this error

Run-time error '3073'
Operation must use an updateable query

any ideas?