Filter Access Table based on External List

1
votes

I have a table called "Inventory", which contains all the "Items" (physical parts, components). I have another table, called "Products", where the main products are listed. A third table is "Manufacturing Orders".

Every time a new manufacturing order is in production i'd like to get this two effects:

  • The quantity in stock for the selected Products increase
  • The quantity of the items which made the Product up decrease of their specific units.

Of course to do this i need a BOM. I'm trying to figure out the most efficient way to generate a BOM.

The solution I've thought about so far is the one as follow:

  • Every time there is a new Manufacturing Order, filter the Inventory table based on an External list (on Excel), and decrease the qnt just for the items available in the "Filtering list".

I'm wondering if there is some function in Access which allows this or i do need VBA. Also, if there is a more quick and efficient solution.

Note: i decided not to create the bom as tables to keep the db more clean.

Thanks in advance

1
excelvbams-access
Why is Excel involved? Not clear to me how Excel would be utilized. Manufacturing/assembly type of db is one of more complicated to design. Topic of many discussions. Ideally, stock balances are calculated when needed from transaction records, not increase/decrease a value in table. allenbrowne.com/AppInventory.html – June7
Every Product is an assembled, made up with several Items stored in Inventory Table. Every time a new Product is created, i need to update the quantities in stock just for the items who made up the Product, so, basically, i need to filter the record based on a BOM. I don't know where or how to create the BOM list in Access, so i was thinking to create them in Excel, and set up a filter somehow (vba app, for example). I'd like to know if there is a more efficient way. – Vba-automation.com
More efficient or not, best way is to calculate balance from transaction records. Difference of stock received and stock used is balance. – June7
Sorry but, i'm still not getting how to manage a BOM in the way you suggested. – Vba-automation.com
Define the acronym BOM. – June7

1 Answers

0
votes

I would first determine from which application are you controlling this update; from within Access retrieving the data from Excel, or within Excel updating the tables in Access?

If you are within Access you could pull the data into a record set in a manner such as Query Excel worksheet in MS-Access VBA and then using the rs2.field(***).value to build a Access-SQL UPDATE string to update the table. This could be placed in a form that drives the VBA code. Note that within the referenced hyperlink there is a function fncOpenFile. This is most likely a file dialog procedure to retrieve the filename. A similar function is this:

Private Function GetFileName() As String
Dim FO     As Object    'file object
Set FO = Application.FileDialog(3)
With FO
    .allowMultiSelect = False
    .show
    GetFileName = .SelectedItems.Item(1)
End With
End Function