Results 1 to 9 of 9
  1. #1
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113

    Question How to program different printing options depending on content of Sale Order


    Hello all,
    I am new at programming with Access 2013. I have been using as a base the Northwind Database. In this database when you make a sales order all the items keep the same OrderID number. And when you invoice all the items are included in the invoice. Now I want to make some items with a discount and ideally be able to print an invoice with the discount column included. I made a button on the form and on the click event I placed the following code:

    If Disc > 0 then
    DoCmd.OpenReport "ReportDiscount", acViewPreview

    Else
    DoCmd.OpenReport "Print Invoice",acViewPreview

    Also I made two different reports One is ReportDiscount which contains the discount column and the other Invoice.

    This works as long as the last item ordered had a discount, but it doesn't work when the last item has no discount even though the order includes items with a discount. Is there a way of programming this so that it recognizes when the order contains discounted items, no matter where they are placed? Thank you for your help

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe use domain aggregate function on the table with the discount field.

    If DSum("Discount", "tablename", "OrderID=" & Me.OrderID)=0 Then
    'invoice
    Else
    'discount
    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Thank you for your fast response. I tried what you suggested and the result is that DSum does not seem to be =0. I mean Always the Else statement gets the response but not the Dsum statement. What could be going wrong. This is what I wrote

    Private Sub Printing_Click()
    If DSum("Discount", "OrderDetails", "OrderID=" & Me.OrderID) = 0 Then
    DoCmd.OpenReport "InvoiceReportNoDisc", acViewPreview, , "InvoiceNumber=" & Me.InvoiceNumber
    Else
    DoCmd.OpenReport "InvoiceReport", acViewPreview, , "InvoiceNumber=" & Me.InvoiceNumber
    End If

    Even when there is no discount the InvoiceReport opens up. thanks again for your help and your time.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, turns out DSum returns Null if no records match the criteria. So try instead:

    If IsNull(DSum("Discount", "OrderDetails", "OrderID=" & Me.OrderID)) Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    I tried the If IsNull suggestion without success. I always get the "invoiceReport" opening no matter what. Thank you for your suggestions.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I need better understanding of data structure and report design. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    Attachment 21947 I hope the attachment works. Go to New Sale Order button . The non-working part are the two buttons labeled Print Invoice, and Print Packing List. Customers 6 to 9 get discounts for products 1 to 7. All the other customers and products do not get discounts. You will find the code we talked about in the Click event of these two buttons. Thank you for all your help.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The field name in OrderDetails is fkOrderID, not OrderID. So change the DSum to look at fkOrderID for the criteria.

    "fkOrderID=" & Me.OrderID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ManuelLavesa is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    113
    You are correct It works now.... Thank you very much for your help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Show Sale and No-Sale Accounts
    By jgalich in forum Queries
    Replies: 1
    Last Post: 07-08-2015, 09:21 AM
  2. Replies: 7
    Last Post: 04-12-2014, 04:08 PM
  3. Need advice on report printing program
    By mejia.j88 in forum Programming
    Replies: 3
    Last Post: 03-18-2014, 08:42 AM
  4. Days since last sale
    By bandmolson in forum Queries
    Replies: 1
    Last Post: 03-17-2014, 01:01 PM
  5. Program a 30-day trial into my Access Program?
    By genghiscomm in forum Programming
    Replies: 1
    Last Post: 05-26-2011, 02:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums