I am working on an inventory project, and want to have a form where you scan a UPC into an unbound text box to initiate the check in and out of items. Because the UPC is not guaranteed to be unique for all of the unique items (sizing, series, kits, etc), I have the AfterUpdate of this control trigger a lookup for all items with matching UPCs and put it into a datasheet where a user can pick the item to update.
I have two main tables I am working with to do this:
tblInventory -> *LocalID, Manufacturer, Description, UPC, Location
tblInventoryTransactions -> *TransactionID, LocalID, TransactionType, UnitCost, Quantity, TaxRate, VendorID
The PK tblInventory.LocalID is 1:M on tblInventoryTransactions.LocalID, and I have supporting lookup tables for transaction types and vendor info.
So I can create a split form that will handle the UPC scan and bring up my items in datasheet view from tblInventory, but what I need to do now is update tblInventoryTransaction with the TransactionType, UnitCost, Quantity, VendorID and TaxRate. I have been fooling around with unbound boxes on the main form and subforms with various record sources for a couple days now, and can't get this form to append to tblInventoryTransactions and leave the data in tblInventory alone. Because the transaction only stores the item LocalID, I want to make sure that the Manufacturer and Description are visible to the user as they enter the quantity, cost, etc.
I have a feeling that I have overthought this and am making this much harder than it needs to be. Does anyone have some advice on how to handle a form that sources data from one table, but needs to take inputs and append another table? In split view, is it possible to have a click event on a row in the datasheet bring up a Transaction form and pass along the LocalID value that I need and still display the Manufacturer and Description values?
Thanks
I have two main tables I am working with to do this:
tblInventory -> *LocalID, Manufacturer, Description, UPC, Location
tblInventoryTransactions -> *TransactionID, LocalID, TransactionType, UnitCost, Quantity, TaxRate, VendorID
The PK tblInventory.LocalID is 1:M on tblInventoryTransactions.LocalID, and I have supporting lookup tables for transaction types and vendor info.
So I can create a split form that will handle the UPC scan and bring up my items in datasheet view from tblInventory, but what I need to do now is update tblInventoryTransaction with the TransactionType, UnitCost, Quantity, VendorID and TaxRate. I have been fooling around with unbound boxes on the main form and subforms with various record sources for a couple days now, and can't get this form to append to tblInventoryTransactions and leave the data in tblInventory alone. Because the transaction only stores the item LocalID, I want to make sure that the Manufacturer and Description are visible to the user as they enter the quantity, cost, etc.
I have a feeling that I have overthought this and am making this much harder than it needs to be. Does anyone have some advice on how to handle a form that sources data from one table, but needs to take inputs and append another table? In split view, is it possible to have a click event on a row in the datasheet bring up a Transaction form and pass along the LocalID value that I need and still display the Manufacturer and Description values?
Thanks
