You are here:

Using MS Access/adding 2 records from 1 form

Advertisement


Question
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
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,

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.