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