Records

[June7]

There is no need for both ProductID and Barcode in tblSalesDetails and tblPurchaseDetails. ProductID can probably be eliminated from all 3 tables.

I see tblPONum is now not in relationship with tblPurchases.

The Purchases form/subform appears to be working. However, really don't need to show the ID and PONum in the subform. If you do, at least make them Locked Yes and TabStop No.

The ProductID combobox properties need to be changed:


ControlSource: Barcode
RowSource: SELECT tblProducts.Barcode, tblProducts.ProductDesc FROM tblProducts ORDER BY tblProducts.ProductDesc;
ColumnCount: 2
ColumnWidths: 0";2.0"

[Griphon]

Why are ProductID and Barcode both in tblPurchaseDetails?
What is different in ProductID, ProductCode and Barcode in tblProducts?

You have Purchases and Sales, but there are no Amount fields??

Can you tell us in 4 or 5 sentences exactly WHAT your business is? Keep it simple so we can understand what your model is trying to support.

Hello Orange,

The main reason for this db is to attach stock received to a PO number and update the stock( The supplier will deliver the stock and this would be scanned in and checked against a delivery note) once this is done we would update the items into stock quantity.
On the sales side, this would be a delivery note only and the stock quantity would be subtracted from stock using a sales code (i.e.: Customer Order number)

So In a nutshell this is purely a stock management system.

Thanks in advance

[Griphon]

There is no need for both ProductID and Barcode in tblSalesDetails and tblPurchaseDetails. ProductID can probably be eliminated from all 3 tables.

I see tblPONum is now not in relationship with tblPurchases.

The Purchases form/subform appears to be working. However, really don't need to show the ID and PONum in the subform. If you do, at least make them Locked Yes and TabStop No.

The ProductID combobox properties need to be changed:
ControlSource: Barcode
RowSource: SELECT tblProducts.Barcode, tblProducts.ProductDesc FROM tblProducts ORDER BY tblProducts.ProductDesc;
ColumnCount: 2
ColumnWidths: 0";2.0"

Hello June7,

I really thank you for your responses, I shall give this a try and update.

Thanks

[Griphon]

There is no need for both ProductID and Barcode in tblSalesDetails and tblPurchaseDetails. ProductID can probably be eliminated from all 3 tables.

I see tblPONum is now not in relationship with tblPurchases.

The Purchases form/subform appears to be working. However, really don't need to show the ID and PONum in the subform. If you do, at least make them Locked Yes and TabStop No.

The ProductID combobox properties need to be changed:
ControlSource: Barcode
RowSource: SELECT tblProducts.Barcode, tblProducts.ProductDesc FROM tblProducts ORDER BY tblProducts.ProductDesc;
ColumnCount: 2
ColumnWidths: 0";2.0"

Hello June7,

I have managed to do what I have set out to accomplish , thanks to your guys help.

I would like to know, what would be the best way to generate a report after the capture of item in order to display a list captured for that transaction and then once I am happy that the delivery note matches what I have captured, update the stock item quantity?


Thanks , I have attached my progress.

[June7]

Ideally, would not 'update' the stock item quantity. Saving calculated data is usually a bad idea, especially aggregate calcs. This results in disconnect of raw data from the aggregated data. Balance on hand should be calculated whenever needed. Total purchases minus total sales equals total on hand.

Review: http://allenbrowne.com/AppInventory.html