Product Groups 2 - How To Use ByRef and ByVal, Active Form and Control, and Product Groups in Microsoft Access


	Back to Access Developer 25 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Product Groups 2

Lesli Cochran

4 years ago

Hi, I am trying to add product groups to my order form. For a single item the ProductCombo pulls the following: ProductCombo.RowSource = "SELECT ProductID, ProductName, UnitPrice, Units, IsSalesTax, " & _
        " IsAlcoholTax, IsServiceCharge, ProductSubCategoryID, IsServiceChargeTax " & _
        " FROM ProductT " & _
        " WHERE ProductSubCategoryID = " & ProductSubCategoryCombo & _
        " ORDER BY ProductName;"

I am attempting to DLOOKUp the values for IsSalesTax, IsAlcoholTax, IsServiceCharge, and IsServiceChargeTax BUT these items are only used as a criteria for a query which determines the actual rate of tax entered on the OrderDetailT, so the system doesn't recognize the control.

I'm able to set the controls for the single combobox with the following code, but how can I pull this off in a record set?

ProductID = ProductCombo
    ProductName = ProductCombo.Column(1)
    UnitPrice = ProductCombo.Column(2)
    Units = ProductCombo.Column(3)


    If IsNull(ProductCombo.Column(4)) Then
       SalesTaxRate = 0
    End If


    If ProductCombo.Column(4) = True Then
        SalesTaxRate = Forms!OrderF!SalesTaxRate
        Else
        SalesTaxRate = 0
    End If


    If ProductCombo.Column(5) = True Then
        AlcoholTaxRate = Forms!OrderF!AlcoholTaxRate
        Else
        AlcoholTaxRate = 0
    End If


    If ProductCombo.Column(6) = True Then
        ServiceChargeRate = Forms!OrderF!ServiceChargeRate
        Else
        ServiceChargeRate = 0

    End If

    If ProductCombo.Column(8) = True Then
        ServiceChargeTaxRate = Forms!OrderF!ServiceChargeTaxRate
        Else
        ServiceChargeTaxRate = 0
    End If

Lesli Cochran OP @Reply

4 years ago

This is what I have tried:

Private Sub AddPackageButton_Click()

    Dim rsGroup As Recordset, rsGroupDetail As Recordset

    Set rsGroup = CurrentDb.OpenRecordset("OrderDetailT")

    'ADD THE PACKAGE AS A LINE ITEM
    'rsGroup.AddNew
    'rsGroup!OrderId = Forms!OrderF!OrderId
    'rsGroup!ProductID = 0
    'rsGroup!ProductName = ProductGroupCombo.Column(1)
    'rsGroup!Quantity = 1
    'rsGroup!UnitPrice = 0
    'rsGroup.Update

    'LOOP THRU DETAILS AND ADD AS PRODUCTS
    Set rsGroupDetail = CurrentDb.OpenRecordset("SELECT * FROM ProductGroupDetailT " & _
        "WHERE ProductGroupID=" & ProductGroupCombo, dbOpenSnapshot)

    While Not rsGroupDetail.EOF
        rsGroup.AddNew
        rsGroup!OrderId = Forms!OrderF!OrderId
        rsGroup!ProductID = rsGroupDetail!ProductID
        rsGroup!ProductName = Nz(DLookup("ProductName", "ProductT", "ProductID=" & rsGroupDetail!ProductID), "Unknown")
        rsGroup!Quantity = rsGroupDetail!DefaultQuantity
        rsGroup!UnitPrice = Nz(DLookup("UnitPrice", "ProductT", "ProductID=" & rsGroupDetail!ProductID), 0)
        rsGroup!Units = Nz(DLookup("Units", "ProductT", "ProductID=" & rsGroupDetail!ProductID), "Unknown")

        rsGroupDetail!IsSalesTax = DLookup("IsSalesTax", "ProductT", "ProductID=" & rsGroupDetail!ProductID)

            If rsGroupDetail!IsSalesTax = False Then
                rsGroup!SalesTaxRate = 0
            End If

            If rsGroupDetail!IsSalesTax = True Then
                rsGroup!SalesTaxRate = Forms!OrderF!SalesTaxRate
                Else
                rsGroup!SalesTaxRate = 0
            End If

            If rsGroupDetail!IsAlcoholTax = True Then
                rsGroup!AlcoholTaxRate = Forms!OrderF!AlcoholTaxRate
                Else
                rsGroup!AlcoholTaxRate = 0
            End If

            If rsGroupDetail!IsServiceCharge = True Then
                rsGroup!ServiceChargeRate = Forms!OrderF!ServiceChargeRate
                Else
                rsGroup!ServiceChargeRate = 0
            End If

            If rsGroup!IsServiceChargeTax = True Then
                rsGroup!ServiceChargeTaxRate = Forms!OrderF!ServiceChargeTaxRate
                Else
                rsGroup!ServiceChargeTaxRate = 0
            End If

        rs.Update
        rsGroupDetail.MoveNext
    Wend

    rsGroupDetail.Close
    rsGroup.Close
    Set rsGroupDetail = Nothing
    Set rsGroup = Nothing

    Me.Requery

Lesli Cochran OP @Reply

4 years ago

Ok, so just posting a problem helps :)  This code runs without errors but doesn't actually load the row, so its kinda a new question?!  I just wanted to update:

Private Sub AddPackageButton_Click()

    Dim rsGroup As Recordset, rsGroupDetail As Recordset

    Set rsGroup = CurrentDb.OpenRecordset("OrderDetailT")

    'ADD THE PACKAGE AS A LINE ITEM
    'rsGroup.AddNew
    'rsGroup!OrderId = Forms!OrderF!OrderId
    'rsGroup!ProductID = 0
    'rsGroup!ProductName = ProductGroupCombo.Column(1)
    'rsGroup!Quantity = 1
    'rsGroup!UnitPrice = 0
    'rsGroup.Update

    'LOOP THRU DETAILS AND ADD AS PRODUCTS
    Set rsGroupDetail = CurrentDb.OpenRecordset("SELECT * FROM ProductGroupDetailT " & _
        "WHERE ProductGroupID=" & ProductGroupCombo, dbOpenSnapshot)

    While Not rsGroupDetail.EOF
        rsGroup.AddNew
        rsGroup!OrderID = Forms!OrderF!OrderID
        rsGroup!ProductID = rsGroupDetail!ProductID
        rsGroup!ProductName = Nz(DLookup("ProductName", "ProductT", "ProductID=" & rsGroupDetail!ProductID), "Unknown")
        rsGroup!Quantity = rsGroupDetail!DefaultQuantity
        rsGroup!UnitPrice = Nz(DLookup("UnitPrice", "ProductT", "ProductID=" & rsGroupDetail!ProductID), 0)
        rsGroup!Units = Nz(DLookup("Units", "ProductT", "ProductID=" & rsGroupDetail!ProductID), "Unknown")

        IsSalesTax = DLookup("IsSalesTax", "ProductT", "ProductID=" & rsGroupDetail!ProductID)

            If IsSalesTax = False Then
                rsGroup!SalesTaxRate = 0
            End If

            If IsSalesTax = True Then
                rsGroup!SalesTaxRate = Forms!OrderF!SalesTaxRate
                Else
                rsGroup!SalesTaxRate = 0
            End If

        IsAlcoholTax = DLookup("IsAlcoholTax", "ProductT", "ProductID=" & rsGroupDetail!ProductID)

            If IsAlcoholTax = True Then
                rsGroup!AlcoholTaxRate = Forms!OrderF!AlcoholTaxRate
                Else
                rsGroup!AlcoholTaxRate = 0
            End If

        IsServiceCharge = DLookup("IsServiceCharge", "ProductT", "ProductID=" & rsGroupDetail!ProductID)

            If IsServiceCharge = True Then
                rsGroup!ServiceChargeRate = Forms!OrderF!ServiceChargeRate
                Else
                rsGroup!ServiceChargeRate = 0
            End If

       IsServiceChargeTax = DLookup("IsServiceChargeTax", "ProductT", "ProductID=" & rsGroupDetail!ProductID)

            If IsServiceChargeTax = True Then
                rsGroup!ServiceChargeTaxRate = Forms!OrderF!ServiceChargeTaxRate
                Else
                rsGroup!ServiceChargeTaxRate = 0
            End If

        rsGroup.Update
        rsGroupDetail.MoveNext
    Wend

    rsGroupDetail.Close
    rsGroup.Close
    Set rsGroupDetail = Nothing
    Set rsGroup = Nothing

    Me.Requery

End Sub

	Lesli Cochran OP @Reply 4 years ago
Nevermind!! It works perfectly! I needed to add a few values to the parent form to create an OrderID. YESSSSSSSSSSSSSSSSSS!!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer 25.

Next Unseen