You are here:

Using MS Access/adding 2 records from 1 form


QUESTION: Hi,  I am currently working on an Inventory database where there are deliveries, distributions and transfers between multiple warehouses across the company. We currently have a separate from for each type of movement to update a table called Movement. What I would like to do for transfers, is create a from where we could do a transfer from one warehouse and a transfer to the other warehouse and have it update the movement table with both records instead of having to do 2 different transactions.  I have limited knowledge working with code, but I'm learning.  Any help that you could provide would be appreciated.

ANSWER: I need to know the structure of the Movement table. It may not be necessary to have two records. Also how do you handle reporting on current inventory?

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: The structure of the table is like this:
ID    Shipment ID   Date   Boxes   Movement   Warehouse
54433   3445   12/18/2012   2   TransferTo   Toronto
54432   5210   12/18/2012   -2   TransferFrom   Ottawa

We must have 2 records.

It would be easier to do both halves of the transfer on one form.

Thanks for your help,

Frankly, I would be more inclined to use one record. Since you can always get the previous action, you can use that to determine the from location.

But, I would use an unbound form here. I would have controls on that form to enter the ShipmentIDs (ShipTo and ShipFrom), date (ShipDate), # of boxes (Boxes), sending location (cboSendWH) and receiving location (cboReceiveWH). The names of the controls are within the parrentheses. Then add a button that would populate the tables. The code behind that button would look like this:

Dim strSQL As String

strSQL = "INSERT INTO Movement (ShipmentID, ShipDate, Boxes, Movement, Warehouse) " & _
         "VALUES(" & Me.ShipTo & ", #" & Me.ShipDate & "#, " & Me.Boxes & ", 1" & _
         ", " & Me.cboReceiveWH & ");"
CurrentDB.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO Movement (ShipmentID, ShipDate, Boxes, Movement, Warehouse) " & _
         "VALUES(" & Me.ShipFrom & ", #" & Me.ShipDate & "#, " & Me.Boxes * -1 & ", 2" & _
         ", " & Me.cboSendWH & ");"
CurrentDB.Execute strSQL, dbFailOnError

Note: I'm assuming everything but the date is a numeric value. And that a Transfer To movement = 1 and Transfer From = 2. I'm also assuming that the warehouse is selected from a combo box that lists the warehouses from a table of warehouses.

Also this form would only be used to enter new transfers.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.