Update data using unbound text boxes in an calculated query

[HSXWillH]

I am designing an inventory system and am stuck on a potential problem.

I have a table of Stock_Catalog containing the following fields: Stock_ID (random autonumber), Full_Desc, Serial, Auto, Mem, RC, Trans_Type. These are the basic item descriptions.

I then have 5 other fields: Add (yes/no), MRR (yes/no), Qty, Unit_Price, Item_Note that come into play when the user chooses which stock item is going to be part of the transaction. User changes Add to yes, fills in the remaining 4 boxes appropriately and then hits the update macro button.

All the selected Yes are then appended to the Transaction_Lines table and through other macro'ed queries, the last 5 fields of Stock_Catalog are then reset to null or no.

I then have a query named qryInventory that calculates how many of what items I have on-hand based on all the Transaction_Lines.Qty summary. Here is where my problems arises.

I wish to be able to enable a checkbox solution as above involving only on-hand inventory in order to rapidly add the selected products to Transaction_Lines; however I cannot get qryInventory to update a table that would contain the Add...etc fields as my Stock_Catalog table does currently. I understand you shouldn't put calculated data in a table anyhow so that's not optimal.

So, I'm wondering if it's possible on a created filter form of qryInventory to add the necessary unbound text boxes and then for each line if Unbound CheckBox = yes...append the Full_Desc, and user-supplied unbound "Qty", "Unit_Price" to my Transaction_Lines table?

Is this feasible or not? Even if feasible, is there a better method to go through being able to allow the user to select which lines they wish to append to a table so as to be quickest and easiest?

Edit: The entire reason I wish to add this 2nd line of functionality is so the user is not selling items that aren't in stock.

[NeoPa]

Have you considered showing only the items on hand (available to sell) in the form that allows the operator to select which they're interested in. I would expect this to resolve your issue.

[HSXWillH]

Yes, I have, but I can't figure out how to only list those items given the qryInventory doesn't contain the selection buttons or anything to then append to the table. I don't know how to allow an updateable segment on the calculated query.

I know I'm missing something very simple, but I don't know what exactly.

[NeoPa]

I think if you need some help with this then I will need some more details from you :

1. What is the name of the form you are using for this?
2. Is the form bound to qryInventory? If not what is it bound to?
3. What is the SQL of qryInventory (or whatever the form's bound to if not that)?
   To extract the SQL from a QueryDef (saved Access query) it is only necessary to view the query in SQL View. This shows the underlying SQL for the QueryDef and is text (See Access QueryDefs Mis-save Subquery SQL for problems with SubQueries). When a QueryDef is open (either in Design View or Datasheet View) it is possible to switch to the SQL View simply by selecting View \ SQL View from the menu.
   
   From here it is simple to Copy & Paste it to wherever you need it.
4. What in the bound recordset determines whether or not you want it to be included?

[HSXWillH]

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?

[NeoPa]

Perhaps you could answer my questions first before going off at a tangent.

[HSXWillH]

1) Form title is Search_Inventory

2) It is bound to qryInventory

3) SQL is:

SELECT Inventory_Detail.Stock_ID,
       Inventory_Detail.Full_Description,
       Inventory_Detail.[Qty OH],
       Stock_Catalog.Item_Type,
       Stock_Catalog.UoM,
       Stock_Catalog.Sport,
       Stock_Catalog.Card_Year,
       Stock_Catalog.Brand,
       Stock_Catalog.Subset,
       Stock_Catalog.Player_Name,
       Stock_Catalog.Grade,
       Stock_Catalog.Auto,
       Stock_Catalog.Mem,
       Stock_Catalog.Serial,
       Stock_Catalog.RC,
       P_Hdr.*
 
FROM   (Stock_Catalog LEFT JOIN
        P_Hdr
  ON   Stock_Catalog.Player_Name=P_Hdr.Player_Name) INNER JOIN
       Inventory_Detail
  ON   Stock_Catalog.Stock_ID=Inventory_Detail.Stock_ID
 
WHERE  (Inventory_Detail.[Qty OH]>0);

4) This is where I'm stuck; there's nothing in the qryInventory to allow me to select the record as an addition. It's why I went on the tangent.

[NeoPa]

Will,

I don't think you appreciate how difficult you are to follow. This last post is good as far as it goes, but it essentially says "Ignore my first post as it doesn't make sense." That's fine, but your next post refers back to an understanding (that I don't yet have) of what you're trying to achieve, and what you're working within.

If the English were perfect it would be hard to follow, but, like most people, your sentences flow from one to the other without correct punctuation and trying to work out exactly what you mean is difficult indeed. You can get away with this in a short paragraph of prose, but this is technical talk, and that requires accurate puncuation and putting your words in the correct order to a much higher degree. I don't mean to belittle your efforts. I'm simply hoping to illustrate why understanding your problem is not as straight forward as you may think from your side of the table. You have access to all the details. I have to rely on what you post, and if that's not very clear then I struggle.

I was hoping for something that would help me see to the heart of this matter, but unfortunately everything refers to each other in such an unstructured way that making sense is really too difficult.

Why don't we go back to something solid and see if we can work from there?
@HSXWillH
Are you saying that you have no way, within your qryInventory, of determining whether a record is stocked or not?

Bear in mind Access only provides a method to implement your logic. If the fundamental logic is flawed, or the information you want isn't even there, then Access cannot help you resolve that. The design must support the idea if the idea is to have any chance of implementation.