Stock Control Database

[Graeme]

Morning Loungers

I’m just making the first steps of putting a database together which will be used to collate all the information for our materials stock. The basic process is that the engineers (me!) request a Quotation from a couple of Suppliers, raise a Purchase Order against one of the Quotes and order the materials. The items are delivered to the depot on a number of Delivery Notes and then allocated to a Site.

The database will be used by our admin staff so I would like to put the bare bones together and then sit down with them to discuss how they want to use it before the queries and the forms are produced.

I’ve designed it with separate StockIn and StockOut tables because that seemed like a good idea! Then quantities of current stock can be calculated in a query. I would be grateful for any comments on/criticism of the structure before I proceed.



Thanks

Regards

Graeme

[HansV]

I'd use a stock table that represents the current state of the stock. That will be more efficient than always having to calculate the current state in a query. Also, if discrepancies occur (which is almost inevitable), you can bring the stock table up-to-date.
Store all modifications to the stock in a transactions table. You can indicate whether items are added or removed in one of two ways: either by the sign of the quantity (+ = in, - = out) or by keeping all quantities positive and adding a boolean "flag" field (true = in, false = out).

[Graeme]

Thanks for that Hans.

It seemed logical to split the two functions as logistically there are two sets of people controlling them. But overall there's one Engineer for each Site. I'll have a go at rev 02 with one stock table.

[HansV]

Also see Creating an MS Access Stock Control Database: Free download for some ideas.

Microsoft has some free templates for download - see for example Inventory management database (Access 2003) or Inventory (Access 2007).

[StuartR]

How do you plan to check deliveries when they arrive. Your purchase order table doesn't seem to include product and quantity, so the only place this appears is TblStockIn, which presumably only gets completed when the stock arrives.

Also you need some way of checking that the purchase order has been filled so that the supplier can be paid. I don't see where this information is being stored.

My general advice here would be to talk to the Admin staff before you design the database, and make sure that you know exactly what they need to do with the data.

[Wendell]

As Stuart and Hans have suggested, this is a complex application scenario, and you really do need to talk with each of the potential users and understand how they would use the system, and what are the problems they routinely encounter that you may be able to help with. Examples you need to think about are how you deal with discrepancies in inventory, how you deal with split shipments, how you determine when to reorder, how you track serial numbers if they are used, what sort of mechanical track is used (bar codes, RFID, etc) if any, and so on. You may ultimately conclude that a commercial system would be a better choice...

[Graeme]

Hans, I've requested a copy of the Stock Control Database and I'll have a look at it this evening, thanks. I saw the Inventory Database this morning whilst searching for stuff but it didn't seem to be what I was looking for.

Stuart, deliveries are checked manually by ticking off the delivery note against each item. Often there are a number of deliveries to each order so I was going to create a query at a later date to check that the orders are complete. The Purchase Order is raised separately by the company accounting system. My PO table is just to record the PO number for reference and so yes, the stock in table would be populated from the delivery notes. Suppliers send in their invoices and the Engineers authorise them for payment if the goods have been received. I had better add a table for Invoices!

Wendell, indeed the database needs to be more complicated than I first imagined! At present our company accounting system deals with the financial side of procurement only and as a result records of plant and materials are put together individually by the Engineers. The idea of a departmental database was an attempt to centralise all these separate spreadsheets. I'll stick with Access for now and see what develops, even a simple database which keeps basic records of our materials stock would be better than the system we have now!

Thanks for your help gents.

Regards

Graeme

[HansV]

As the others noted, a stock control database is complex, and it's easy to lose yourself in the details. A fully fledged application may well be beyond the capability of a single developer (it's certainly beyond mine!), so it might be best to strive for a relatively simple one that doesn't do everything, but that meets at least some of the key requirements of the users.

[Graeme]

it's certainly beyond mine!

That's not very likely!

[HansV]

Here is some extra info (adapted from a PM conversation with Graeme):

Let's say that the stock record for "widgets" currently lists a quantity of 59 items.

A shipment of 16 new widgets arrived on April 20. A record is added to the transactions table that lists the date (20/04/2010), the product name (Widgets), and the quantity (16) with a flag of "in", and other relevant data (such as provenance, price, etc.). When this record is processed, the quantity for widgets in the stock table is updated from 59 to 75.

On the 26th of April, 20 widgets are shipped out. A record is added to the transactions table that lists the date (26/04/2010), the product name (Widgets) and the quantity (20) with a flag of "out", and other relevant data. When this record is processed, the quantity for widgets in the stock table is updated from 75 to 55.

I wouldn't let the stock table be updated automatically from the After Update event of the transactions form - there's too much risk that it would be updated at the wrong moment or with the wrong data.
I'd force the user to make it a deliberate act, for example by providing a command button on the transactions form. If necessary, this command button could be made available only to specific users who have the right to authorize transactions.

[Graeme]

After much head scratching I'm on version 4 of my database. All the stock is in one table with no seperate products table. The stores are just another site in the sites table so there is no boolean stock in or stock out field. There is still much tidying up to do!

The first form opens and lists all the items in the stores in a list box. The new stock movement button opens a second form where the details of transactions are inputted. Could someone please point me in the right direction to add a where statement in the new stock movement button code to make the stock movement form open with the item in the list box already displayed in a text box instead of having to select it from a combo box?

Thanks

Graeme

[HansV]

You could set the value of the combo box in the On Load event of the FrmStockMovement form:

Code: Select all

Private Sub Form_Load()
    Me.Combo1 = Forms!frmStock!LstProduct
End Sub

By the way, you can simplify the code for the Confirm button considerably. There is no need to set focus to each control in order to retrieve or set its value:

Code: Select all

Private Sub CmdComfirm_Click()
    If Me.TxtSiteID = 1 Then
        Me.TxtStockQuantity = Me.TxtStockQuantity + Me.TxtTransactionQuantity
    Else
        Me.TxtStockQuantity = Me.TxtStockQuantity - Me.TxtTransactionQuantity
    End If
End Sub

[Graeme]

Hans

I tried changing the Combo to a text box since this would be more appropriate if it’s already filled in and I tried a separate form and then a sub form in place of the List box but have now gone back to plan A and abandoned the idea of pre-empting the Stock Description Combo Box. The New Stock Movement Form needs to open as a data entry form and so an empty combo is ok.

My needlessly verbose version of the code required to calculate the Stock Quantity Total evolved that way partly due to the Set Focus error I got initially and partly because I am once again in the position of having forgotten almost all of the vb skills I once had! Thank you for the succinct version.

Would I be right in that an object does not require to have it’s focus set if the property being changed is the default property?

The next step is to make all the ID text boxes not visible and to toggle the visible property of a couple of things depending on whether the stock movement is into the stores or out to site. And I need to add a couple of tables to record the Delivery Note and Purchase Order numbers, then we can use it to replace the ad-hoc system that is currently in place.

Regards

Graeme

[HansV]

There is a difference between the Value and Text properties of a text box.

The Text property is only available while the text box has focus. It represents the text as it is currently being entered or edited by the user. You only need this property if you want to react immediately to any change in the text box, for example if you want to search a form dynamically as the user types. You'd typically use the On Change event of the text box for this.

The Value property is the 'underlying' value of the text box. It is available whether the text box has focus or not. If the text box has the focus, the Value property represents the text as it was before the user started changing it. When the user leaves the text box, the Value property is updated to be equal to the latest value of the Text property.

The Value property is the default property of a text box, so if you use the name of a control without explicitly mentioning a property, you refer to its Value.

Don't hesitate to post back if you have more questions.