Inventory levels in stock control database

  • Thread starter mark@quicksolutions
  • Start date
M

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
 
S

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
 
M

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...

scubadiver said:
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


mark@quicksolutions said:
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
 
L

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.
 
M

mark@quicksolutions

Perfect. Thank you!

LauriS said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top