ms-accessunbound

Access unbound form fields to update multiple tables

My Database has 3 tables Inventory (Item Name, Barcode, Unit Type), In (Lot #, Barcode, Location, Expiration Date, Received Date), Out (Barcode, Team, Person Removing, Out Date). I have an unbound form for when I add a new Item to the main inventory. I would also like it to add the information to the In Table from the same unbound fields in the form. The unbound Field names are Item, Unit, Barcode, Lot, Expiration. I have tried using the “INSERT INTO” but cannot get it to work, I have never used it before.

Solution

  • Use this code: (make sure you keep In with [] since In is a reserved word)

    strSQL = "INSERT INTO [In] (Item, Unit, Barcode, Lot, Expiration) "
strSQL = strSQL & "(" & Me.Item & "," & Me.Unit & "," & Me.Barcode & "," & Me.Lot & "," & Me.Expiration & ")"
DoCmd.RunSQL strSQL