check if subform has records, then do insert or update

[ezybusy]

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

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

[Welshgasman]

Check the subform recordset count?

[ezybusy]

Thank you,
how do i do that ?

[Welshgasman]

Thank you,
how do i do that ?

You start with a Google.

https://www.google.com/search?q=chec...hrome&ie=UTF-8

Try the first link.

[ezybusy]

Hello,
i have tried searching from Google using the link you provided, yet i can't get the right way to do what i have in mind. Maybe i did not explain myself enough. Please below are more details about what i am trying to achieve :

Loop through all the records on the subform, then if the specific Product_ID already exist, then do UPDATE stuffs, else do INSERT stuffs. However, as i said earlier on i prefer do to do all the checkings at form level if possible, not involving checking through tables.

Do you think it can be done ? If yes, can you kindly give me a starting guideline as to how the code should look like ?.

Thank you in advance.

[Gicu]

You need to use the subform's RecordsetClone FindFIrst method to attempt to find the desired product id; if the recordset's NoMatch property returns true then you insert else you update.

https://learn.microsoft.com/en-us/of...recordsetclone

Cheers,