Hello everyone,
Please i need your help on the following :
i am trying to check that my subform has record and perform the following relevant actions :
* if no records on subform the do insert
* if there are record, then check if those records don't already exist.
- if record already exist the do update
- else do insert
So far i manged to have everything working. But i am not happy using DCOUNT for this routine. Simply because i believe everything might get slower as the db tables grow in size due to data insertion.
I believe there could be a more clever way to do this, especially by doing all the checkings only at the forms level (not involving tables check). Below is my working code, can someone help me tweak it properly ?
Thank you in advance
Code:
Private Sub CmdGoToData_Click()
Dim rst As DAO.Recordset
' We check the available stok first
If Me.ProdStock < 1 Then
Dialog.box "Ooops !!! stock épuisé!", vbOKOnly + vbExclamation, "Erreur!"
Exit Sub
Else
DAO.DBEngine.BeginTrans
Me.Parent!SaleDate = Now()
Me.Dirty = False
Me.Parent.Refresh
' We Check if there are sale entries on the subform SubSalePOS
Set rst = Me.Parent.SubSalePOS.Form.RecordsetClone
If rst.RecordCount = 0 Then
' Set the field value for deletion purpose
Me.Parent.SubSalePOS.Form.TxtHold_Qty = 1
' Insert the product and a minimum quantity of 1 into sale details
CurrentProject.Connection.Execute "INSERT INTO Tbl_SaleDetail ([SaleID_FK], [ProdID_FK], [SalePrice], [SaleQty]) " & _
"VALUES (" & Nz(Me.Parent!SaleID_PK, 0) & ", " & Nz(Me.CboProduct.Column(0), 0) & ", " & Nz(Me.CboProduct.Column(5), 0) & ", 1);"
' update the product stock
CurrentProject.Connection.Execute "UPDATE Tbl_Product SET ProdStock = ProdStock - 1 WHERE ProdID_PK=" & Nz(Me.CboProduct.Column(0), 0) & ""
Else
If DCount("SaleID_FK", "Tbl_SaleDetail", "[SaleID_FK] = " & Nz(Me.Parent!SaleID_PK, 0) & " And [ProdID_FK]=" & Nz(Me.CboProduct.Column(0), 0) & "") <> 0 Then
' Set the field value for deletion purpose
Me.Parent.SubSalePOS.Form.TxtHold_Qty = 1
' Update the ordered quantity
CurrentProject.Connection.Execute "UPDATE Tbl_SaleDetail SET SaleQty = SaleQty + 1 WHERE ProdID_FK=" & Nz(Me.CboProduct.Column(0), 0) & ""
' update the product stock
CurrentProject.Connection.Execute "UPDATE Tbl_Product SET ProdStock = ProdStock - 1 WHERE ProdID_PK=" & Nz(Me.CboProduct.Column(0), 0) & ""
Else
' Set the field value for deletion purpose
Me.Parent.SubSalePOS.Form.TxtHold_Qty = 1
' Insert the ordered product and quantity into sale details
CurrentProject.Connection.Execute "INSERT INTO Tbl_SaleDetail ([SaleID_FK], [ProdID_FK], [SalePrice], [SaleQty]) " & _
"VALUES (" & Nz(Me.Parent!SaleID_PK, 0) & ", " & Nz(Me.CboProduct.Column(0), 0) & ", " & Nz(Me.CboProduct.Column(5), 0) & ", 1);"
' update the product stock
CurrentProject.Connection.Execute "UPDATE Tbl_Product SET ProdStock = ProdStock - 1 WHERE ProdID_PK=" & Nz(Me.CboProduct.Column(0), 0) & ""
End If
End If
DAO.DBEngine.CommitTrans
End If
End Sub