Printable View
This thread supplies information on a method for doing an INVENTORY system using a single table for stock-in and stock-out transactions. Using a single table allows for easy queries for stock-on-hand and other figures related to stock and date ranges. I am going to cobble posts from other threads and attempt to make an argument for this design.
It does not make sense to me, in a SQL world, to have a RAW material table and a Order table.
You should have a single inventory table - something like this (and this is just for example purposes):
Product Id
Transaction Date
Transaction Type (C for CREDIT, D for DEBIT)
Transaction Sequence (if needed)
Reason Code (I for initial quantity, R for restock, O for order, A for adjustment)
Quantity
So you would end up with data like this:
Some initial stock of product 000001 and 000002 on 2004-02-01.Code:ProdId TransDate Type Seq Reason Quantity
000001 2004-02-01 C 001 I 50
000002 2004-02-01 C 001 I 100
000001 2004-02-10 D 001 O 20
000001 2004-02-10 C 002 R 100
000002 2004-02-20 D 001 O 99
000001 2004-03-01 C 001 A 2
000002 2004-03-01 D 001 A 1
Then an Order and Restock of product 000001 on 2004-02-10.
Order of product 000002 on 2004-02-20.
Then some stock adjustments on 2004-03-01 - found out we had 2 more of product 000001 and that the remaining quantity of product 000002 was lost/damaged.
This main INVENTORY table drives everything about the quantity of stock - on-hand, ordered, sold - all figures - date driven.
You have additional tables for order info - linked however you want - maybe with product id, date and sequence - only when you have a REASON code of "O" for order.
This respects the "set-based" logic that you are supposed to use in SQL - and loses the old "record-by-record" processing techniques that we should avoid in SQL.
Is this a question or an arguement? Just curious.
It is an issue that comes up every couple of months on the forum - being discussed this week in an old and resolved thread. I decided to create a new thread and if SimplyMe wants to continue the discussion we can have it here instead of hijacking that thread from 2 years ago...Quote:
Originally Posted by RickyOswaldIOW
Do you have any experience or opinions regarding inventory table design?
i already posted in that old thread but since you open up this one i would gladly continue our discussion here. The design is somewhat near to what i need.
I'll be posting here the last two post i had in that old thread.
Im not using bound controls. Im using ADODB to connect to my database. Here is the code in sub main i used to connect to my database
Regarding the separate tables of stock-in and stock-out. I have it so that i can have a record for stocks going out. If this is not a good idea then how should my tables look like and if I use only one tables for stock-in and out which field should I use to put relationship to my client table?Code:Public Sub openConnection()
Set oConn = New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.Oledb.4.0"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.ConnectionString = "Data Source=" & DBPath & "\" & DBName & ";Persist Security Info = False;Jet OLEDB:Database Password=xx;"
.Open
End With
End Sub
Sorry for my questions, I know this are basic ones which unfornately i still dont know yet and I'm very much willing to learn about it.
Thanks for your patience.
I just edited my database. please refer to the attached file.
Can we just use the fields i am using so that i'll not get lost in the processing of our discussion?
Your field names or my field names are not an issue.Quote:
Originally Posted by Simply Me
Tell me what is the purpose of Quantity and QuantityOut in tblStockTransFile?
Why do you have two fields?
My example in Post #2 of this thread clearly shows that a single QUANTITY field is sufficient.
Granted I propose a CREDIT/DEBIT field to determine if it's a "in" or "out" of inventory - but it would be even simpler to remove the CREDIT/DEBIT field and simply have the QUANITY be a positive or negative number.
What is the purpose of your UNIT field??
The Quantity there is for stocks available while the quantityOut is number of stocks taken out or sold. its basically stock in and out.
The unit there is use to know if the item is on pieces, bar, tube, sachet, gallon, etc....
Could show me how your tables looks like as well as its relationships please?
Get rid of QuantityOut - you do not need two fields. Put a negative number in the Quantity field if the stock is going out. Put a positive number in the Quantity field if the stock is coming in.Quote:
Originally Posted by Simply Me
This allows: Select Sum(Quanity) From... to actually work - which is the whole point.
If every time a product is referred to it's the same unit - like selling product XYZ is always done as BAR, for instance, then that field belongs in the tblStockMasterFile.Quote:
The unit there is use to know if the item is on pieces, bar, tube, sachet, gallon, etc....
Let's deal with the issues above first - we will build up your design if that's ok.Quote:
Could show me how your tables looks like as well as its relationships please?
Ok for me.Quote:
Get rid of QuantityOut - you do not need two fields. Put a negative number in the Quantity field if the stock is going out. Put a positive number in the Quantity field if the stock is coming in.
This allows: Select Sum(Quanity) From... to actually work - which is the whole point.
Yes product has the same unit.Quote:
If every time a product is referred to it's the same unit - like selling product XYZ is always done as BAR, for instance, then that field belongs in the tblStockMasterFile.
Can we build the design please?Quote:
Let's deal with the issues above first - we will build up your design if that's ok.
Ok - so if I understand your reply you are about to change your tables
tblStockMasterFile:
ProductId
ProductName
Unit - since this is the same for all transactions - it belongs here
tblStockTransFile:
ProductId
EntryDate - let's put this field up here - it's the second segment of the pri key
Quantity - positive for in-stock, negative for out-stock
ClientCode
Now for questions...
PurchasePrice - it can be argued that this belongs in some price schedule table - but let's deal with that later
ReorderPoint - seems to belong in tblStockMasterFile - this has nothing to do with an order being placed by a client - it's related to the product itself - just like the Unit - right?
DateOut is redundant - the ENTRYDATE field is the only field for date needed in this table.
In post #2 I discussed having a separate table for the ORDER to support the CLIENT associated with out-going-stock. You seem to want to have a single table for both inventory and order data - that's ok with me.
You might want to consider having a REASON code as I suggested in post #2 - so you can determine more easily the reason for a row being in the tblStockTransFile.
Please post back questions on any of these issues.
If you agree then please re-design your tables and post back a new image of the relationships.
already done with it.Quote:
Ok - so if I understand your reply you are about to change your tables
tblStockMasterFile:
ProductId
ProductName
Unit - since this is the same for all transactions - it belongs here
tblStockTransFile:
ProductId
EntryDate - let's put this field up here - it's the second segment of the pri key
Quantity - positive for in-stock, negative for out-stock
ClientCode
The purchaseprice of the same item may differ from time to time.Quote:
Now for questions...
PurchasePrice - it can be argued that this belongs in some price schedule table - but let's deal with that later
Ok.. done...Quote:
ReorderPoint - seems to belong in tblStockMasterFile - this has nothing to do with an order being placed by a client - it's related to the product itself - just like the Unit - right
DateOut is redundant - the ENTRYDATE field is the only field for date needed in this table.?
In post #2 I discussed having a separate table for the ORDER to support the CLIENT associated with out-going-stock. You seem to want to have a single table for both inventory and order data - that's ok with me.
fine with me.Quote:
You might want to consider having a REASON code as I suggested in post #2 - so you can determine more easily the reason for a row being in the tblStockTransFile.
below is my new database.Quote:
Please post back questions on any of these issues.
If you agree then please re-design your tables and post back a new image of the relationships.
Ok - the design you now have matches in "concept" with what is suggested at the top of the thread.
Do you have any other questions? I'm leaving for a few hours...
How should i go about it inserting data to the tables, Do i need to use JOIN or a separate INSERT using begintrans and endtrans?
with the design we have can i generate the following reports later?
Yes - you use separate INSERT's - BEGINTRAN and COMMIT or ROLLBACK is a nice clean method.
Those reports look very basic - and should easily be possible with a query and/or sub-query combination.
What is your backend database again??
Im using MS ACCESS but i really would like to use ms sql 2005 express but i dont know how much about and how to open a connection using it.
Switch to MS SQL 2005 Express now - it's worth it.
Download the database and the free Management Studio Express software from MS.
Then visit www.connectionstrings.com to find out what's up with the connection string - it's really very, very easy.
You will be very happy that you switched - for many reasons - speed, better SQL syntax - more modern tools.
Ok. Hope you could help me through in learning it. Should i download MS SQL server 2005 Express or just the database and the free Management Studio Express software ?
This download is the database - the server so to say (although it runs on a workstation)
http://www.microsoft.com/downloads/d...displaylang=en
and this is for Mgt Studio Express
http://www.microsoft.com/downloads/d...displaylang=en
Thanks! I'll be back after I downloaded and installed it.
I find your design in post #2 a very good one. I have some questions though.
1. what's the difference between the transaction type and Reason fields?
2. when do i use the two (i mean what scenario)?
With my current design, i am having problem on what to do whenever there is additional stock of a certain item. Since my productID is a PK then duplicate productID is not allowed...How do i solve such.
This is what i have far. I can enter records to my two tables already using the code below. Assuming I encoded the following products:
product ID: 00001
Product name: Tide Powder
Unit: Sachet
Purchase Price: 45
Quantity: 30
Reason: In
Entry Date: 9/11/07
Re-Ordere Point: 10
product ID: 00002
Product name: Colgate 150ml
Unit: Tube
Purchase Price: 70
Quantity: 25
Reason: In
Entry Date: 9/11/07
Re-Ordere Point: 15
My problem here is since my productID fields for both tblStockMasterFile and tblStockTransfile are PK, then two does not allow duplicate entry, right? so how about if on 9/12/07 i have additional item to be entered for product 00001 how should i be able to add the item without getting the sum of the item just entered to the previous item?
I am attaching the data entry form i created in vb. I dont know if this is the best way to do it or do i need to have separate data entry form for the tblStockMasterFile and tblStockTransFile?Code:Private Sub cmdSave_Click()
If CheckNullValue = False Then Exit Sub
Call ReplaceQuotation(txtProdName)
'check whether user has change the PK otherwise it'll not be saved
If Checker = True Then
Call msgExist("Product")
txtProdID.SetFocus
' Start highlight before first character.
txtProdID.SelStart = 0
' Highlight to end of text.
txtProdID.SelLength = Len(txtProdID.Text)
Exit Sub
End If 'Checker
On Error GoTo ErrHandler
oConn.BeginTrans
sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
"VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
oConn.Execute sSQL
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
"VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
' Debug.Print sSQL
oConn.Execute sSQL
oConn.CommitTrans
Call FillListView(lstStockMasterFile, rsStock)
Call ClearFunction(frmStockMasterFile, "TextBox")
txtProdID.SetFocus
Exit Sub
ErrHandler:
Call msgError(Err)
End Sub 'cmdSave_Click
Transaction type of C is for a credit - stock coming in - D is for a debit - stock going out. That field is used for building the SUM() formulaQuote:
Originally Posted by Simply Me
Select ProdId,Sum(Case When Type='C' Then Quantity Else -Quantity End) From...Group by ProdId
That SELECT give you total stock on hand - using the TYPE field to determine a positive or negative number. You can do away with the TYPE field if you put the "-" into the value itself in the QUANTITY field for debit amounts.
The PK of this table should be: (ProdId,TransDate,Type,Seq) - with those 4 fields duplicates are not possible (the Seq value forces this).
Does this answer your question in post #22 also??
In my mind, I used the Reason fields for Stock In (In/C) and Stock Out (Out/D). Is it good enough?
As I've mentioned, I can stockIn a product using the code I used in cmdSave--there's no problem with is, but when I tried adding the same product (additional stock-In) I can't add anymore because the PK won't allow me. How do I resolve that?Quote:
Does this answer your question in post #22 also??
If you feel REASON can indicate C or D for stock in or out - then that's fine.
I would not do that - but I've got different experiences that put me in that position. I cannot bring you there.
For your second question - what fields are in your PK? The sequence # should the last segment in the compound PK and it should be incremented to allow for unique PK's.
tblStockMasterFile:Quote:
Originally Posted by szlamany
ProductID (PK)
ProductName
Unit
ReOrderPoint
tblStockTransFile:
ProductID (PK)
EntryDate (PK)
PurchasePrice
Quantity
Reason
Client Code
tblClientMasterFile:
ClientCode (PK)
ClientName
Budget
here's my code:
Code:Private Sub cmdSave_Click()
If CheckNullValue = False Then Exit Sub
Call ReplaceQuotation(txtProdName)
If Checker = True Then
Call msgExist("Product")
txtProdID.SetFocus
' Start highlight before first character.
txtProdID.SelStart = 0
' Highlight to end of text.
txtProdID.SelLength = Len(txtProdID.Text)
Exit Sub
End If 'Checker
On Error GoTo ErrHandler
oConn.BeginTrans
sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
"VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
oConn.Execute sSQL
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
"VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
' Debug.Print sSQL
oConn.Execute sSQL
oConn.CommitTrans
Call FillListView(lstStockMasterFile, rsStock)
Call ClearFunction(frmStockMasterFile, "TextBox")
txtProdID.SetFocus
Exit Sub
ErrHandler:
Call msgError(Err)
End Sub 'cmdSave_Click
Code:'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
Checker = False
If rsTempStock.State = adStateOpen Then rsTempStock.Close
sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
"FROM tblStockMasterFile " & _
"WHERE ProductID = '" & (txtProdID.Text) & "'"
'Debug.Print sSQL1
rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function 'Function Checker
Code:Private Sub Form_Load()
Call openConnection
Me.Left = LeftPos - 200
Me.Top = TopPos - 200
Set rsStock = New ADODB.Recordset
Set rsTempStock = New ADODB.Recordset
rsStock.CursorLocation = adUseClient
' sSQL = "SELECT ProductID, ProductName,Unit, ReOrderPoint FROM tblstockMasterFile"
sSQL = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
"tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
"tblStockTransFile.EntryDate, tblStockTransFile.PurchasePrice, " & _
"tblStockTransFile.Quantity, tblStockTransFile.Reason " & _
"FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
"tblStockMasterFile.ProductID = tblStockTransFile.ProductID"
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load
Post #2 and post #23 both show a field called Seq - which is part of the primary key.
You need to have that field in tblStockTransFile - and have it be a segment in the PK.
That field needs to have 1, 2 or 3 (and so on) in it to make the PK unique.
Im sorry, I can't get what you mean here.Quote:
You need to have that field in tblStockTransFile - and have it be a segment in the PK.
That field needs to have 1, 2 or 3 (and so on) in it to make the PK unique.
Perhaps i'm offbase, if so just ignore this post,
If your planning to due any detailed reporting on Sales and Purchase history there seem to be important things that are missing.
Some type of order number (Sales Order# or Invoice# or Purchase Order#), something to track this transaction. Can't use client ID and date because they could have more than one transaction per day.
There are some quantities that could be helpful to your sales people
committed - sold but not shipped
on hand - Actual quantity in physical inventory
availiable = on hand - committed
on order - Ordered but not in inventory
Maybe this is more than you need, just wanted to mention it while you were in the design stage of your project.
@wes4dbt - from post #2
I'm trying so hard to get the point across that the basic inventory count needs to be stored as inventory detail rows in a transaction table.Quote:
Originally Posted by szlamany
I fully realize that order info needs to be in a different table - as stated in post #2 (and quoted above).
Right now I'm trying to get Simply Me to understand that a "Sequence Number" field added to the table - and made part of the compound primary key - can offer a unique PK when more then one entry is done for a single date.
I admire how much time you have spent helping Simply Me.
Even a simple inventory system need a Product Master table and a Product Detail table. Otherwise just create an EXCEL spreadsheet.
The most important things are setting the relationships and the primary keys. After that your main concern is what information do I want to track and in how much detail.
I think sooner or later the programmer will be sorry if they don't use a relational DB.
Might as well design with the future in mind.
For what it's worth.
Good luck
I guess Im already aware of it that I need to add the Sequence number field. My question is-- is this something that the user will input or it should be an autonumber field?Quote:
Originally Posted by szlamany
Kindly please take a look my code. I know that the function below does not let me have more than one entry of the same product. when I removed the code i am still not able to add products since my productID needs unique value.
Kindly please take a look at my code above. What should it look like?
Code:'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
Checker = False
If rsTempStock.State = adStateOpen Then rsTempStock.Close
sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
"FROM tblStockMasterFile " & _
"WHERE ProductID = '" & (txtProdID.Text) & "'"
'Debug.Print sSQL1
rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function 'Function Checker
I'm very much thankful that szlamany had been so patient in sparing his time for me.Quote:
Originally Posted by wes4dbt
If you try to look at the things i've posted I think i have the Product Master table and a Product Detail table already.
If you decide to use a sequence number, it will be used in only the Detail table. You don't need it in the Master table because there is only one record per product. In the detail table the PK could be ProductID+SequenceNo, this would allow you to track all the various transactions.
In my opion the system should generate the sequence# and the operator should not be able to edit it. But because there is always a chance of data corruption or maybe the company just wants to start each new year with a fresh seq#, there should be a some way to reset the current seq# and this can be done thru a utility function. I hardly ever use an autonumber field because of the lack of flexability, I like to control the number with code. It's more work but worth it, but thats a design decision you must make.
You don't have to use a seq# there may be other options, it depends on what is going to be entered for each detail record.
example - ProductID+OrderNo ' where OrderNo is always unique
Using a seq# is a good method, especially if you not 100% sure of whats going to be entered.
Ok this is my scenario:
1. The user will enter the names of clients as well as their monthly budget
2. The user will enter(stock In) the product Id, product name, purchase price, unit, quantity, Date of entry, and the re-order point.
3. One product can have different purchase price, so table should contain the product but with different purchase price. (this where I dont know what code should use since the product ID is a PK)
4. The user will enter(Stock Out) the product the client will order.
Store the purchase price in the detail record not the master record.
or
If you absolutely need to have a master record for a product with more than one price then entry the product multiple times with different ProductID's.
Are you sure you need multiple prices in master? Why
Certainly one product will have more than one price since prices in the market is not fixed. What you bought today for 100 may become 110 tomorrow. So if this is the case therefore the selling price will also be different. the mark-up price is 15% of the purchase price.
Do you understand the SEQ field and how it makes the PK unique?Quote:
Originally Posted by Simply Me
It seems by this statement that maybe you don't.Quote:
3. One product can have different purchase price, so table should contain the product but with different purchase price. (this where I dont know what code should use since the product ID is a PK)
This should be the tblStockTransFile:
tblStockTransFile:
ProductID (PK)
EntryDate (PK)
SeqNo (PK) - this field gets added to the PK
PurchasePrice
Quantity
Reason
Client Code
That SEQNO field gets the value 1 for the first entry for a product on a given entry date. So almost always this field is simply a 1. In the situation where a second row is added for a product on the same ENTRYDATE then you make this value a 2. A 3 for the third an so on.
This is such a simple concept - we use it all the time for NOTE tables where someone enters 1 or 2 lines of text on a given date.
We find it extremely easy for the client program to manage this SEQNO field - either show it to the user or don't - doesn't really matter. We usually show it to the user and the GUI forces the column value to be unique for a given date.
You could even do the INSERT with a 1 and if it fails change it to a 2 and try again - that's a reason method in SQL to get a unique value also.
Yes, you're right i really dont understand SEQ and thanks for the explanation. I already added SEQ to my table. If you look at the code in post #26. What am i missing there? so that what you mentioned above will work out?
The INSERT in post #26
I don't see a SEQNO in that list of fields being inserted.Code:sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
"VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
' Debug.Print sSQL
Yes because i posted that before i added the SEQ. if I add the SEQ it will still not let me insert the same product with one product ID.
Well that is not possible.Quote:
Originally Posted by Simply Me
If the PRIMARY key is Product Id + ENTRY DATE + SEQNO then as long as the SEQNO is different the row will be added.
Perhaps its bcause of my first INSERT... What should i do with it?
Clarifications:
1. is the SEQ field autonumber or i let the user type values or it should be put in the code? if put in the code, can you show me the SQL code for it?
2. regarding the entry of products--is it a good idea to put all together the fields of the tables(tblStockMasterFile and tblStockTransFile) in one data entry form or its better to have one data entry form tblStockMasterFile and one for tblStockTransFile?
It is not an AUTONUMBER field - it's a 1 nearly always.
The only time it's a two is when you have a "second posting" for the same product on the same ENTRYDATE.
I'm not sure how to show you code for it - what are you asking for?
As for your second question - that's really not possible for me to answer either. An ENTRY FORM is supposed to, in my opinion, mimic a real-world human operation or process that is already being done. If they are used to filling out a paper ORDER form - then you give them that same experience - but on the screen instead. Having two forms for the sake of making it easier for the programmer to handle the tables being manipulated should not be a driving force.
Im asking for a code if its not an autonumber then the user will just input it or perhaps using if statement?Quote:
Originally Posted by szlamany
Im asking this because i am really having trouble what code should i use so that a product entered more than once will be accepted by the program. I did what you said about the compound PK but still im not successful. Perhaps its my code logic.... I'll be posting the whole code and can you please take a look at it?Quote:
Originally Posted by szlamany
Code:Private Sub Form_Load()
Call openConnection
Me.Left = LeftPos - 400
Me.Top = TopPos - 200
Set rsStock = New ADODB.Recordset
Set rsTempStock = New ADODB.Recordset
rsStock.CursorLocation = adUseClient
' sSQL = "SELECT ProductID, ProductName,Unit, ReOrderPoint FROM tblstockMasterFile"
sSQL = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
"tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
"tblStockTransFile.EntryDate, tblStockTransFile.PurchasePrice, " & _
"tblStockTransFile.Quantity, tblStockTransFile.Reason " & _
"FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
"tblStockMasterFile.ProductID = tblStockTransFile.ProductID"
' sSQL = FormatSelect(sSQL)
' MsgBox sSQL
If rsStock.State = adStateOpen Then rsStock.Close
rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic
Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load
'TODO: Populate the ListView control
Sub FillListView(lstStockMasterFile As ListView, rsStock As ADODB.Recordset)
Dim lst As ListItem
rsStock.Requery
lstStockMasterFile.ListItems.Clear
While Not rsStock.EOF
Set lst = lstStockMasterFile.ListItems.Add(, , rsStock("ProductID")) 'DISPLY IN LISTVIEW
lst.SubItems(1) = rsStock.Fields("ProductName")
lst.SubItems(2) = rsStock.Fields("Unit")
lst.SubItems(3) = rsStock.Fields("PurchasePrice")
lst.SubItems(4) = rsStock.Fields("Quantity")
lst.SubItems(5) = rsStock.Fields("Reason")
lst.SubItems(6) = rsStock.Fields("EntryDate")
lst.SubItems(7) = rsStock.Fields("ReOrderPoint")
rsStock.MoveNext
Set lst = Nothing 'Destroy the list
Wend
End Sub 'FillListView
Private Sub cmdSave_Click()
If CheckNullValue = False Then Exit Sub
Call ReplaceQuotation(txtProdName)
'check whether user has change the PK otherwise it'll not be saved
If Checker = True Then
Call msgExist("Product")
txtProdID.SetFocus
' Start highlight before first character.
txtProdID.SelStart = 0
' Highlight to end of text.
txtProdID.SelLength = Len(txtProdID.Text)
Exit Sub
End If 'Checker
On Error GoTo ErrHandler
oConn.BeginTrans
sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
"VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
"','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
' Debug.Print sSQL
oConn.Execute sSQL
oConn.CommitTrans
Call FillListView(lstStockMasterFile, rsStock)
Call ClearFunction(frmStockMasterFile, "TextBox")
txtProdID.SetFocus
Exit Sub
ErrHandler:
Call msgError(Err)
End Sub 'cmdSave_Click
'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
Checker = False
If rsTempStock.State = adStateOpen Then rsTempStock.Close
sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
"FROM tblStockMasterFile " & _
"WHERE ProductID = '" & (txtProdID.Text) & "'"
'Debug.Print sSQL1
rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly
If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function 'Function Checker
for szlamany:
I am now able to save same product in my table and have some questions about it so I opened another thread for it. Perhaps you can help.
I have a question regarding stock out....If there are 3 stockins for a product say, Prod001.
a. Prod001 10 10.50 9/30/07 1
b. Prod001 5 10.00 9/30/07 2
c. Prod001 10 10.25 9/30/07 3
If I stock out 5 I would like that the 5 will be taken from the first stock in (FIFO) which the purchase price 10.50 will be multiplied by 1.15% to get the selling price. So the first stock in will now have 5 products left.
Another issue is what if the the first stock is not enough to meet the required stock out? so the lacking part will be taken from the second stock out which has different purchase price and therefore will have different selling price.
any logic and code here for me to start with?
That is a tough one.
Your example of 5 coming from the first stock-in brings to mind a harder issue - let's say someone buys 12 - with 10 coming from the first stock-in and 2 coming from the second stock-in.
I guess you first need to determine what "date" and "sequence" of stock is still on hand.
That at least points to a particular stock-in row as being the "active and available one".
I'm putting together a point-of-sale system right now that's going to have to do similar stuff - maybe as I get through that myself I'll post back here (I'm just getting the sales screen and cash register and thermal receipt printer stuff working today - inventory tracking in regard to these sales is the next challenge for me).
Actually the harder issue is already mentioned in my post. I do hope you can finish that earlier so that you can tell me how to do it.Quote:
Originally Posted by szlamany