Update delivery to products table

[Michael M]

I have a products table a delivery and deivery details
table.
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.

 

[rretzko]

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

tblProduct
IndexProduct(Autonumber)
Product Name(text)
CurrentInventory(long integer)

tblDelivery
IndexDelivery(Autonumber)
DeliveryDate(Date/Time)
DeliveryCompany(Text, but should be a Lookup)
IndexProduct(Lookup from tblProduct)
ProductCount(long integer)

frmDelivery
holds the tblDelivery fields
The AfterUpdate event triggers on the ProductCount
field.

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] +
Me.ProductCount)
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
important!

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];