Update delivery to products table


Michael M

I have a products table a delivery and deivery details
The products table has the quantity in stock and the
delivery details table has the quantity delivered.
How do I get the quantity delivered to add to the
quantity in stock? I cant get my head round this one as I
am a real newbie to access.Any help would be appreciated.


Michael - There are probably as many ways to solve this as
there are VBA programmers. Here's my solution:

Product Name(text)
CurrentInventory(long integer)

DeliveryCompany(Text, but should be a Lookup)
IndexProduct(Lookup from tblProduct)
ProductCount(long integer)

holds the tblDelivery fields
The AfterUpdate event triggers on the ProductCount

Code for the ProductCount(AfterUpdate) event:
Private Sub ProductCount_AfterUpdate()
Dim r As Recordset
Dim strSQL As String
Dim lngInventory As Long

strSQL = "SELECT [CurrentInventory] FROM tblProduct " _
& "WHERE [IndexProduct] = " & Me.IndexProduct & ";"

Set r = CurrentDb.OpenRecordset(strSQL)

If r.RecordCount > 0 Then
lngInventory = (r![CurrentInventory] +
strSQL = "UPDATE tblProduct " _
& "SET tblProduct.CurrentInventory = " &
lngInventory _
& " WHERE ((tblProduct.IndexProduct) = " &
Me.IndexProduct & ");"
CurrentDb.Execute strSQL
End If

End Sub

Make sure that you have your Tools-References set
properly. Mine have "Visual Basic for Applications,
Microsoft 9.0 Object Library, OLE Automation, Microsoft
DAO 3.6 Object Library, and Microsoft Visual Basic for
Applications Extensibility 5.3". Note that the order is

Good luck - Rick

Ken Snell

Use an update query that "reads" the value of the field in "Delivery
Details" table, and use that value as the new value to add to the existing
value in "Products" table field. Something like this:

UPDATE [Products] INNER JOIN [Delivery Details] ON [Products].[FieldName] =
[Delivery Details].[FieldName] SET [Products].[FieldName] =
[Products].[FieldName] + [Delivery Details].[FieldName];

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question