Sql Count

[Simply Me]

Hello everybody! My code below will INSERT records in my two tables namely "tblStockMasterFile and tblStockFile". When the Save button is clicked it checks whether a product ID entered already exists or not. If product exists then the records will be save in tblStocktransFile Only otherwise to both tables. I attached the table for reference.

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)

On Error GoTo ErrHandler
  If Checker = True Then  'Product ID already Exist then save in tblStocktransFile Only
    
    sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason,Sequence)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','" & txtReason.Text & "','" & txtSEQ.Text & "')"
                 '  Debug.Print sSQL
    oConn.Execute sSQL
  
  Else  'Product ID does not Exist so save in both tblStockMasterFile and tblStockTransFile
    
    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,Sequence)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','" & txtReason.Text & "','" & txtSEQ.Text & "')"
                 '  Debug.Print sSQL
      oConn.Execute sSQL

    oConn.CommitTrans
    
  End If
  
  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")

  txtProdID.SetFocus
  Exit Sub

ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

I would like instead of checking the ProductID only, I would also like to check the entry date and if the date already exists for the same product ID i would like to the program to increment the existing sequence value i have in my tblStockTransFile so that there's no need for the user to remember the last sequence value for each of the products and manually type it.

For example:
productID: 01
ProductName: Colgate
Unit: Tube
PurchasePrice: 30.00
Quantity:100
Reason: In --->StockIn
EntryDate: 9/15/07
ReOrderPoint: 10
Sequence: 1

If I add more stock to the product the program should look into the sequence value and add 1 to it.

Below is function checker. It lacks the entrycode and I dont know how or what should i add to it. Can someone please help me out please?

'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 am thinking of adding another check function for the date like the code below but I guess it's not how it is done.

'TODO : return true if Date already exist
Private Function DateChecker() As Boolean
  DateChecker = False

  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(EntryDate) as DateExist " & _
         "FROM tblStockTransFile " & _
         "WHERE EntryDate = '" & (txtEntryDate.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly

  If rsTempStock("DateExist") > 0 Then Checker = True
End Function  'Function DateChecker