How to run a query against all of the records in a subform

[sgkim151]

Hi,



I'm creating an import management system for my international trade company. So, a given import transaction includes several products imported, and those products obviously add to the current stock in the warehouse. I have a form which, in turn, has a subform that lists all the products imported in a given import transaction. When you click on "Run qryUpdateStockLevel" button, it runs the query that is supposed to update the inventory level. The inventory levels must be updated for all of the records of the products listed in the subform. In the attached picture, [frmImport] has a subform which shows 3 products to be imported. I've created a query (named 'qryUpdateStockLevel'), but when I run the query, it updates only the one single row that's currently selected in the subform. The query needs to travel all the items in the subform and update the inventory levels for all of the products listed in the subform.

My question is, what is the best way to update/edit the records in a separate product table, thereby updating the inventory level for each product? 1) Should I run a query passing the quantity imported as a parameter? 2) Should I somehow run a series of macros, or 3) Should I utilize VBA recordset and manipulate the tables manually? For either of the three options, how do I specifically implement those functionalities? I'm pretty new to MS Access, so if you could be very detailed in your answer, it'll be very very helpful.

[orange]

Before working on the details of a form or macro or recordset, what exactly --in plain simple English -- is the database about?
Can you show us a jpg of your tables and relationships? We need to know more about your tables.
It sounds like you are updating your current quantity on hand for each of the products involved.

You may find more info in this Inventory related Allen Browne article

Here is a 26 minute youtube video on stock management with Access that may be worth watching for ideas.

[sgkim151]

Before working on the details of a form or macro or recordset, what exactly --in plain simple English -- is the database about?
Can you show us a jpg of your tables and relationships? We need to know more about your tables.
It sounds like you are updating your current quantity on hand for each of the products involved.

You may find more info in this Inventory related Allen Browne article

Here is a 26 minute youtube video on stock management with Access that may be worth watching for ideas.

Here's the screen captures of the DB relationship map and the navigation pane. What I'm trying to create is pretty much the same as a typical inventory management system. We import products from China and the stock level (inventory) goes up when the products actually arrive at the company's warehouse. Every import transaction has one or more products, so the import table is linked to the import detail table, which in turn has a list of products to be imported. When the user clicks on the button on the import-import detail form, the system is supposed to update the stock level stored in the records of the product table.

I checked out the YouTube video, but it does not explain about the dynamic update of the stock level. I'd appreciate if anybody gives an opinion on this issue.

[orange]

Did you see the sample function at the Allen Browne link?
What is the business process by which your Inventory is reduced? Do you sell to Customers? If so, that should be in your model.
According to your model ( to me anyway), you only receive Products, so Inventory is always increasing.
A "typical inventory management system" is often more complex than expected.

In many Inventory management systems, there is a Transaction table. Acquisitions from Supplier(s) is considered a positive(+) transaction, and each Sale is considered a negative(-) transaction. There are incidents of theft, misplacement, breakage/damage to Items in Inventory, so from time to time there is a manual Stock Taking of each Item in Inventory.
The current Inventory(per Item) is calculated using:

Current Quantity for each Item = Latest StockTaking Count + Acquistions since last StockTaking - Sales since last StockTaking
or said slightly differently
Current Inventory (Item X) = LatestStockOnHandCount(Item X) 
                                        +Incoming TransactionCount (Item X) 
                                        - Outgoing TransactionCount (Item X)

These are often concerns with Inventory Management.
How do you record Prices? How do you change Prices?
Do you ever Request X of Product and Receive only a Partial Order? (BackOrders)
Do you ever get some sort of substitution Product because the supplier does not have sufficient Product on hand?

Good luck.

[sgkim151]

Did you see the sample function at the Allen Browne link?
What is the business process by which your Inventory is reduced? Do you sell to Customers? If so, that should be in your model.
According to your model ( to me anyway), you only receive Products, so Inventory is always increasing.
A "typical inventory management system" is often more complex than expected.

In many Inventory management systems, there is a Transaction table. Acquisitions from Supplier(s) is considered a positive(+) transaction, and each Sale is considered a negative(-) transaction. There are incidents of theft, misplacement, breakage/damage to Items in Inventory, so from time to time there is a manual Stock Taking of each Item in Inventory.
The current Inventory(per Item) is calculated using:

Current Quantity for each Item = Latest StockTaking Count + Acquistions since last StockTaking - Sales since last StockTaking
or said slightly differently
Current Inventory (Item X) = LatestStockOnHandCount(Item X) 
                                        +Incoming TransactionCount (Item X) 
                                        - Outgoing TransactionCount (Item X)

These are often concerns with Inventory Management.
How do you record Prices? How do you change Prices?
Do you ever Request X of Product and Receive only a Partial Order? (BackOrders)
Do you ever get some sort of substitution Product because the supplier does not have sufficient Product on hand?

Good luck.

I'll check out the sample function at the abovementioned link later soon. Just to comment on my inventory management system itself, yes, you're right. For now, the inventory only increases whenever the products are imported. I do have the actual business with actual, physical stock. The inventory goes down when we make sales. That part of the system will be implemented after I implement the "import" side.

Looks like the sample function at the link does the job by VBA codes, but some other sample inventory management templates successfully do the job by putting macros in place. (This is my guess.) What's the better way?

[orange]

From experience I find it easier to build a model that reflects the whole business. This model can be quite conceptual, but it includes all aspects of the business. You can add details to show how the "entities" are related. Then, based on your priorities, develop the database keeping the holistic model as a guide. This way you will not have to redevelop to fit the later parts of the business into a physical database. The approach allows you to develop according to schedules/priorities, but never loses sight of the fit within the overall business.


I use vba.


Good luck with your project.

[sgkim151]

I use vba.

This was helpful. Will try VBA for this. And yes, I agree with your opinion that the system is better off reflecting the whole business. I won't publish the system for my staff members until the test with the real dataset is over. Thanks for your comments.