Okay, I've gone a slightly different method here; 1 single form that opens upon a certain condition, a 2nd form that shows the inventory status for the associated Stock Item.
The form I'm using is Stock_Catalog, it contains the following fields: Stock_ID, Full_Description, Add, MRR, Item_Note, Order_No, Order_Type, Qty, and Unit_Price.
If the user wishes to put a stock item into a new order; they check Yes in Add...and then fill in MRR/Item_Note/Order_No/Order_Type/Qty and Unit_Price. Click Command Button and a macro runs 4 update queries that populate tables downstream. That works properly.
I want to key in an OpenForm condition AfterUpdate on the Order_Type field. There are 7 options for Order_Type (Buy/Sell/Trade In/Trade Out/Transfer In/Transfer Out). If a person is putting material into inventory be selecting Buy, Trade In or Transfer In, I don't want this event to trigger. However, if they choose Sell, Trade Out, Transfer Out, or Use, I want to open a Small Form that shows the Inventory Qty on hand.
That small form is named qryInventory and consists of Stock_ID, Full_Description and a calculated field Qty_OH.
So far, I have this as my VBA code placed under the After Update on Order Type box.
- Private Sub Trans_TypeLbl_AfterUpdate()
-
-
Dim stDocName As String
-
Dim stLinkCriteria As String
-
-
stDocName = "qryInventory"
-
stLinkCriteria = "[Stock_ID]=" & Me![Stock_ID]
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
-
End Sub
It works in that it pops up the form with the relevent selection, but it does it for every update for all 7 selection options. I want to only place it for the 4 'outgoing' transaction types. Can this be modified using If's and if so...how?