Full Version: Extend Northwind 2007 Functionality - Add All Items
UtterAccess Forums > Microsoft® Access > Access Forms

This question is for those who understand the Northwind Database. If I were to list down all the intricacies of the database for those who don't the post would be 10 pages long

i am adapting the Northwind 2007 database and want to extend the functionality a bit.

Basically when the items arrive after the purchase order goes through, I have to select each individual line item to tell access that the item has arrived. A module is run and the tables are updated accordingly.

I would like some input on how I can introduce an add all button as sometimes there are 20 or more line items that need to be added and it is redundant to keep clicking on each line item.

any thoughts on how to go about doing this.
I'm not intimately familiar with NW2007, but generally
- a PO could have 1 or Many Lineitems
- a LineItem would be associated with 1 PO

- a Product could be related to many lineitems and therefore many POs.

So if you have a list of Lineitems received, you would seem to have the PO associated with the line item. If you have a "list" of received line items, you could order it by PO and line item, and use a loop construct to do the update.
I'm not sure if that is exactly what you are asking, but that's how I understood your post.

If you have a jpg of your tables and relationships, including your extensions, posting same may help readers to put your post into context.
this is a very complex database (in my point of view at least smile.gif )

but here you go. attached relationships.jpg has the database relationships

basically when a po is ordered and confirmed..the items will be received. right now i need to press the check mark on add to inventory to "receive" the items

the after update on the inventory tick mark gives me this. i may have tweaked this a bit from it's original nw2007 but i think we can see this and basically tell it to "Recieve ALL" of the items.

On Error GoTo ErrorHandler

Dim InventoryID As Long
Dim ProductID As Long
Dim Quantity As Long

ProductID = Nz(Me![Product ID], 0)
Quantity = Nz(Me![Quantity], 0)
InventoryID = Nz(Me![Inventory ID], 0)

'Posting New Inventory
If Me![Posted To Inventory] Then
If IsNull(Me![Date Received]) Then
Me![Date Received] = Date
End If

If inventory.AddPurchase(Me![Purchase Order ID], ProductID, Quantity, InventoryID) Then
If InventoryID > 0 Then
Me![Inventory ID] = InventoryID
Me![Posted To Inventory] = True
MsgBoxOKOnly InventoryPostingSuccess

End If
Me![Posted To Inventory] = False
MsgBoxOKOnly InventoryPostingFailure
End If

If inventory.GetQtyOnBackOrder(ProductID) > 0 Then
If MsgBoxYesNo(FillBackOrdersPrompt) Then
inventory.FillBackOrders ProductID
End If
End If

'Removing Posted Inventory
If InventoryID > 0 Then
Me![Posted To Inventory] = True
End If
End If

Exit Sub

' Resume statement will be hit when debugging
If eh.LogError("Posted_To_Inventory_AfterUpdate") Then Resume

i would like it to receive all items in one go.
It appears you have the checkboxes for each item set up. Add a checkbox in the header "Check All".
Private Sub chkCheckAll_AfterUpdate()
   Dim bkmk as Variant
   With Recordset
      If not .BOF Then
        Do While Not .EOF
         .AddToInventory=chkCheckAll   'mark it as received or not by chkCheckAll value
      End if
   End With
End Sub

Private Sub chkCheckAll_AfterUpdate()
   Me.Dirth = False   'force save current record and avoid some error messages
   CurrentDB.Execute "UPDATE WhatEverTable SET AddToInventory=" & chkCheckAll & "WHERE InventoryID=" & InventoryID 'use you own table/field names and criteria
End Sub

Note this approach lets you check or Uncheck all.

Question. What if you only receive partial orders?

Added the Execute's WHERE clause
usually the purchase part is entirely internal so there is no question of not receiving parts. in fact the purchase is done only after receiving the parts. the purchase part of this is just to add to inventory
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.