none
Print all orders from my recordset RRS feed

  • Question

  • Good morning,

    I have a temporary recorset that store all orders that are not print, however, when I run the code below by clicking on a button near the table button, it prints all the invoices, but if I have 2 records into an order, it prints twice the invoice, if I have 3 records into an order it prints 3 times and so on...

    Recordset SQL: (Please note that the field [Réf client] is in reality the table number

    Here is the code:

    Private Sub ImprimerTousTable1_Click()
    On Error GoTo Err_Handler
    
        Dim NoTable As Integer
        Dim intTableNo As Integer
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
    
        Set dbs = CurrentDb()
    
    strSQL = "SELECT * " & _
    "FROM [Données facture RequêteTEMP]" & _
    "WHERE((([Données facture RequêteTEMP].[Réf client]) = 1))"
    
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    If ImprimerFacturesParTable = "Oui" And rst.RecordCount > 0 Then
    
             rst.MoveLast
             rst.MoveFirst
     
             Do Until rst.EOF
        
                    DoCmd.OpenReport "FacturePaiementsParTable", acViewNormal, "", "[Réf commande]=" & rst![Réf commande]
                    rst.MoveNext
             Loop
         Else
             MsgBox ("Il n'y a aucune commande ou aucun items à imprimer pour cette table!")
         End If
    
    
    Closerst:
        rst.Close
        dbs.Close
        Set rst = Nothing
        Set dbs = Nothing
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
        If Err = 3021 Or Err = 3265 Then
        Resume Exit_Handler
        'Else
        'MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Erreur lorsque clique sur imprimer toutes les commandes Table1_Click()"
        'Resume Exit_Handler
        End If
    
    End Sub

    Here is an image of the button:

     


    Claude Larocque

    Monday, November 2, 2015 10:04 PM

Answers

  • Hi Edward,

    You are absolutely right, I did manage to make it work, but following your suggestion if I put the edit and update before the line “If ImprimerFacturesParTable = "Oui" And rst.RecordCount > 0 Then" what would happen is that the recordset rst will not have any data anymore because that query criteria is that the field "FactureImpriméeParTable" as to be FALSE or 0.

    As for creating another thread don't you think that everything is related but I will follow your suggestion anyway, thanks for your reply and your interest


    Claude Larocque

    Thursday, November 5, 2015 9:39 AM

All replies

  • Hi Claude. Have you tried taking your SQL statement and running it in the query designer to make sure you're getting the correct records that you wanted to print? Just a thought...
    Monday, November 2, 2015 10:07 PM
  • Hi Claude,

    Many remarks!

    - You need a space in the strSQL before the WHERE:

    strSQL = "SELECT * " & _
    "FROM [Données facture RequêteTEMP]" & _
    " WHERE((([Données facture RequêteTEMP].[Réf client]) = 1))"

    - Why all the round brackets. Easier is, with a space at beginning of a line:

    strSQL = "SELECT *" & _
                  " FROM [Données facture RequêteTEMP]" & _
                  " WHERE [Données facture RequêteTEMP].[Réf client] = 1"

    - Why do you declare dbs, and do not use it?

    - DO UNTIL _always_ enters the loop at least one time. That is why you have to check before for rst.RecordCount > 0.
    Easier is to use DO WHILE (NOT rst.EOF). With an empty set it does not enter the loop.

    - To prevent multiple prints of the same order, you can select DISTINCT orders and use those single orders to print the report.

    After this I think some more can be structured.

    Imb.


    Monday, November 2, 2015 10:57 PM
  • Hi Imb,

    My SQL statement returns the correct data, I didn't know you that you need to place a space before the WHERE, it doesn't seems to make a difference, I did anyway.

    for the DBS I do use it later in the code, I put the code for the printing only for my question...

    I want all the orders from table 1 to be printed, not a distinct order my only problem is that if 5 articles are into that order, it will print 5 exact copies of the order instead of 1.

    Can you help?

    Thanks


    Claude Larocque


    Tuesday, November 3, 2015 9:19 AM
  • Hi DB guy,

    Yes I have and the records are exactly what I need...

    Claude


    Claude Larocque

    Tuesday, November 3, 2015 9:20 AM
  • Hi DB guy,

    Yes I have and the records are exactly what I need...

    Claude


    Claude Larocque

    Hi Claude,

    So, if your query shows five records, do you get exactly five reports printed, or more? The next step I would suggest to troubleshoot the problem is to "step through" your code. Have you done that?

    Tuesday, November 3, 2015 3:53 PM
  • Hi,

    With some changes in the code, I was able to print the 3 invoices correctly, however, I try to get some fields from the recordset (rst) to receive a true value (or -1)

    I try to put this code anywhere into the following code and it doesn't work... any idea?

    With rst
    While Not .EOF
    rst.Edit
    ![CouponCuisineImprimé] = -1
    ![FactureImprimée] = -1
    ![FactureImpriméeParTable] = -1
    .Update
    .MoveNext
    Wend
    End With

    Code that works:

    Private Sub ImprimerTousTable1_Click()
    On Error GoTo Err_Handler
    
        Dim NoTable As Integer
        Dim intTableNo As Integer
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim rst1 As DAO.Recordset
        Dim strSQL As String
        Dim strSQL1 As String
        Dim i As Integer
    
        Set dbs = CurrentDb()
    
    strSQL = "SELECT * FROM [Données facture RequêteTEMP] WHERE[Données facture RequêteTEMP].[Réf client] = 1"
    
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    strSQL1 = "SELECT * FROM [Données facture Requête par table] WHERE [Données facture Requête par table].[Réf client] = 1"
    Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
    If ImprimerFacturesParTable = "Oui" And rst.RecordCount > 0 Then
    
             rst.MoveLast
             rst.MoveFirst
     
             Do Until rst.EOF
        
             DoCmd.OpenReport "FacturePaiementsParTable", acViewNormal, , "[Réf commande]=" & rst1![Réf commande]
    With rst
        For i = 0 To rst1![Réf commande]
             .Edit
             .Update
        Next
    End With
                 rst1.MoveNext
             Loop
    
         Else
             MsgBox ("Il n'y a aucune commande ou aucun items à imprimer pour cette table!")
         End If
    
    Closerst:
        rst.Close
        rst1.Close
        dbs.Close
        Set rst = Nothing
        Set rst1 = Nothing
        Set dbs = Nothing
    
    Exit_Handler:
        Exit Sub
    
    Err_Handler:
        If Err = 3021 Or Err = 3265 Then
        Resume Exit_Handler
        Else
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Erreur lorsque clique sur imprimer toutes les commandes Table1_Click()"
        Resume Exit_Handler
        End If
    
    End Sub

    So where in the code above can I have the recordset so it works, I tried on all possible events... can't successfully achieve it...

    The goal is to have the "Coupon imprimé", "FactureImprimée" and "FactureImpriméeParTable" receive a value of TRUE or -1, if it would work, then my recorset will have no more records because my query is based on a criteria that mention that the field "FactureImpriméeParTable" = false

    Thanks


    Claude Larocque

    Tuesday, November 3, 2015 9:22 PM
  • Hi Claude,

    >> The goal is to have the "Coupon imprimé", "FactureImprimée" and "FactureImpriméeParTable" receive a value of TRUE or -1,

    Which Recordset did you want to edit, rst or rst1? In my option, you could put the edit code before this line “If ImprimerFacturesParTable = "Oui" And rst.RecordCount > 0 Then”, but you need to check which Recordset you want to edit.

    Based on your description, it seems your original issue about printing all reports has been resolved and now you have issues about editing the recordset, am I right? If so, and you still have issues about editing the recordset with my suggestion. I suggest you mark the helpful reply as answer to close this thread for your original issue, and then post a new thread for new issue about editing recordset.

    If I misunderstood you, please feel free to let me know.

    Best Regards,

    Edward.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, November 5, 2015 9:29 AM
  • Hi Edward,

    You are absolutely right, I did manage to make it work, but following your suggestion if I put the edit and update before the line “If ImprimerFacturesParTable = "Oui" And rst.RecordCount > 0 Then" what would happen is that the recordset rst will not have any data anymore because that query criteria is that the field "FactureImpriméeParTable" as to be FALSE or 0.

    As for creating another thread don't you think that everything is related but I will follow your suggestion anyway, thanks for your reply and your interest


    Claude Larocque

    Thursday, November 5, 2015 9:39 AM