Check for Duplicates - How to Load Template Data For Records in Your Microsoft Access Database - Fitness #61


	Back to Fitness 61 Comments List

Check for Duplicates

James Hopkins

9 months ago

Hey Richard, thank you for this Series. I have learned alot for you and the Tech Helps. I have create a function similar to your "AddToLog Function". I problem I am having is when checking for Duplicate Items in the Order before Add Item to Order Detail Form, with the 'ID - P & PB' as the ProductID and ProductBundleID that is in the Query. I haven't figure out the correct way to do this.

	Richard Rost @Reply 9 months ago
I need to see more of what you got there in order to help.

James Hopkins OP @Reply

9 months ago

Details

Private Sub BtnAddProduct_Click()



    If IsNull(CboProduct) Then

        DoCmd.GoToControl "CboProduct"

        CboProduct.Dropdown

        Exit Sub

    End If

    

    If IsNull(Forms!EstimateF!EstimateID) Then

        Forms!EstimateF!EstimateCreationDate = Date

        Forms!EstimateF.Refresh

    End If

    

    Dim C As Long

    

    C = DCount("*", "EstimateDetailT", "EstimateID=" & Forms!EstimateF!EstimateID & " and ProductID=" & CboProduct)

    

    If C > 0 Then

        If MsgBox("WARNDING! This Product is Already on this Estimate. Do you want to Add it Again?", vbYesNoCancel + vbExclamation) _

            <> vbYes Then Exit Sub

        DoCmd.GoToRecord , , acNewRec

        DoCmd.GoToControl "Quantity"

        ProductID = CboProduct

        ProductName = CboProduct.Column(1)

        Quantity = 1

        UnitPrice = CboProduct.Column(2)

        MarkupPercentage = CboProduct.Column(3)

    End If



    AddToLog CboProduct

    Me.Recordset.Requery

    Quantity.SetFocus

    DoCmd.GoToRecord , , acLast

    

    Bounce Me

    

    CboProduct.SetFocus

    CboProduct = Null

    

    UpdateCboProductRowSource



End Sub

James Hopkins OP @Reply

9 months ago

Details

Public Function AddProductToLog(ProductID As Long, Optional Quantity As Long = 1, _

                       Optional ProductBundleName As String = "") As Long



    Dim db As Database, rsProduct As Recordset, rsLog As Recordset

    

    Set db = CurrentDb

    Set rsProduct = db.OpenRecordset("SELECT * FROM ProductT WHERE ProductID=" & ProductID)

    Set rsLog = db.OpenRecordset("EstimateDetailT")

        

    While Not rsProduct.EOF

        rsLog.AddNew

            AddProductToLog = rsLog!EstimateDetailID

            rsLog!ProductID = rsProduct!ProductID

            rsLog!ProductName = rsProduct!ProductName

            rsLog!ProductBundleName = ProductBundleName

            rsLog!Quantity = Quantity

            rsLog!UnitPrice = rsProduct!UnitPriceToClientCharge

            rsLog!ProductProfitMarginPercentage = rsProduct!MarkupPercentage

            rsLog!ProductSalesTaxPercentage = rsProduct!TaxesOnePercentage

            rsLog!ProductSalesTaxPercentage = rsProduct!TaxesTwoPercentage

        rsLog.Update

        rsProduct.MoveNext ' Technically don't need - Future Proof

    Wend



    rsProduct.Close

    rsLog.Close



    Set rsProduct = Nothing

    Set rsLog = Nothing



End Function



Public Sub AddBundleToLog(ProductBundleID As Long)



    ' Get Product Bundle Name - add with the FIRST item (only) 'ProductID As Long,

    Dim BundleName As String



    BundleName = Nz(DLookup("ProductBundleName", "ProductBundleT", "ProductBundleID=" & ProductBundleID), "Bundle")

    

    ' Loop thru Product in that Product Bundle

    Dim rs As Recordset



    Set rs = CurrentDb.OpenRecordset("SELECT * FROM ProductBundleDetailT WHERE ProductBundleID=" & ProductBundleID & _

                                        " ORDER BY ProductBundleDetailID")



    While Not rs.EOF

        AddProductToLog rs!ProductID, rs!Quantity, BundleName

        If BundleName <> "" Then BundleName = ""

        rs.MoveNext

    Wend



    rs.Close

    Set rs = Nothing

    

End Sub



Public Sub AddToLog(ID As String)



    Dim ItemType As String ' M or F

    Dim NativeID As Long ' ProductBundleID or ProductID

    

    ItemType = Left(ID, 1)

    NativeID = CLng(Right(ID, Len(ID) - 1))

    

    If ItemType = "P" Then ' Product Item

        AddProductToLog NativeID

    Else ' Bundle Item

        AddBundleToLog NativeID

    End If



End Sub

	James Hopkins OP @Reply 9 months ago
Maybe, I should first check the see if the Product is already there in the Form Load Event, if so Add 1 to it. If Item not there, run the Code to add it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Fitness 61.

Next Unseen