Inventory levels in stock control database

[mark@quicksolutions]

I have created a stock control database for my business. I have a table of
items and a table of inventory transactions. I calculate stock levels by:

1) Creating a single item form
2) Embedding an inventory transactions subform
3) Creating a text box containing a formula to sum the inventory transactions

Although I can do this in a single item form, I cannot generate a multiple
item form listing stock levels of everything in one go.

Basically, my operations staff would like the system to flag up to them when
items reach a certain stock level so that they can be reordered. Can anyone
help me here? My knowledge of VB is limited.

Thanks,

Mark

 

[scubadiver]

So you have a "one" table for each item and a "many" table for the orders?

In the table for each time you can have a field for storing the critical
stock level. In the footer of the subform you can count the number of items
ordered by putting the following in a text field:

=sum([fieldname])

and put a reference in the main form:

=[forms]![form name]![subform name].form![fieldname]

When the reference = the critical stock field you can put a critical message
box in the after update event.

msgbox "Message here", vbcritical

 

[mark@quicksolutions]

Yes, the tables have a 'one' item to 'many' inventory transactions
relationship.

I can display stock levels in a single item form using the method you
describe, however I am looking to display the stock levels for all items at
the same time. Currently I have to click through every individual item one by
one to check the stock levels.

As you say, I must calculate the stock levels using a subform. I cannot
insert a subform in a multiple item form, so I can't perform the calculation.
If that makes sense...

So you have a "one" table for each item and a "many" table for the orders?

In the table for each time you can have a field for storing the critical
stock level. In the footer of the subform you can count the number of items
ordered by putting the following in a text field:

=sum([fieldname])

and put a reference in the main form:

=[forms]![form name]![subform name].form![fieldname]

When the reference = the critical stock field you can put a critical message
box in the after update event.

msgbox "Message here", vbcritical



--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I have created a stock control database for my business. I have a table of
items and a table of inventory transactions. I calculate stock levels by:

1) Creating a single item form
2) Embedding an inventory transactions subform
3) Creating a text box containing a formula to sum the inventory transactions

Although I can do this in a single item form, I cannot generate a multiple
item form listing stock levels of everything in one go.

Basically, my operations staff would like the system to flag up to them when
items reach a certain stock level so that they can be reordered. Can anyone
help me here? My knowledge of VB is limited.

Thanks,

Mark

 

[LauriS]

I am by no means an expert but here's some thoughts that come to mind.

Basically, my operations staff would like the system to flag up to them when
items reach a certain stock level so that they can be reordered.

Ok, you will need a field in the item table that has the re-order point.

Then create a query to calculate the total stock transactions for each item.

Finally, create another query that links the stock table to the query that
calculates the totals and pull only those records where the transactions have
reached the reorder point.

Build your form using that query as the source and you should be set.

Lauri S.

 

[mark@quicksolutions]

Perfect. Thank you!

I am by no means an expert but here's some thoughts that come to mind.


Ok, you will need a field in the item table that has the re-order point.

Then create a query to calculate the total stock transactions for each item.

Finally, create another query that links the stock table to the query that
calculates the totals and pull only those records where the transactions have
reached the reorder point.

Build your form using that query as the source and you should be set.

Lauri S.

 

[LauriS]

Glad to help!!

Lauri S.

Perfect. Thank you!