Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Developer > D25 > D25 Lessons < D24 Lessons | D26 Lessons >
Back to Access Developer 25 Lessons    Comments List
Product Groups 2 Upload Images   Link   Email  
Lesli Cochran     
2 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     
2 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     
2 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     
2 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 Lessons.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 7/19/2024 8:16:06 AM. PLT: 0s