none
Update my query after the recordset is printed RRS feed

  • Question

  • Hi everyone,
    I have a temporary recorset (Données facture RequêteTEMP) that store all orders that are not print for a particular table, however, when I run the code below by clicking on a button near the table button, it prints all the invoices correctly, but after all unprinted orders are printed, I want to empty that recordset by having these fields received the TRUE or -1 value.

    [CouponCuisineImprimé] - [FactureImprimée] and [FactureImpriméeParTable]

    I tried to put this code somewhere into the original code that makes the invoices print and I have no success so far:

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

    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 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

    I verify and I can check manually these 3 fields after the printing so how can I automate this task?

    Thank you

    Claude from Québec, Canada


    Claude Larocque

    Thursday, November 5, 2015 9:49 AM

Answers

  • Hi Claude,

    After the printing of the report you can try this, assuming you want the set the flags in [Données facture Requête par table]:

    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)
    
    Do While (Not rst1.EOF)
        rst1.Edit
        rst1![CouponCuisineImprimé] = -1
        rst1![FactureImprimée] = -1
        rst1![FactureImpriméeParTable] = -1
        rst1.Update
        rst1.MoveNext
    Loop
    
    
    
    

    Of course feel free to use the With construction.

    If you want to set the flags in the TEMP recordset, then use the other set.

    I do not (yet) see the necessity to have a TEMP table apart from the "par table" table, but that is probably because i do not oversee the total project.

    Imb.


    Thursday, November 5, 2015 11:21 AM
  • I'm more than a little confused by your code.  Does the field [Réf commande] come from table [Données facture RequêteTEMP]?  If so, does this do what you need?

        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 Not rst.EOF Then
            Do Until rst.EOF
                ' Print report
                DoCmd.OpenReport "FacturePaiementsParTable", _
                                 acViewNormal, _
                                 , _
                                 "[Réf commande]=" & rst![Réf commande]
                ' Mark report as printed
                rst.Edit
                rst![CouponCuisineImprimé] = -1
                rst![FactureImprimée] = -1
                rst![FactureImpriméeParTable] = -1
                rst.Update
                
                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

    -Bruce

    Saturday, November 7, 2015 12:35 AM
  • Hi Claude.

    You have first to take the DISTINCT values of [Réf commande], and for each value you can print the report. If I remember well, I suggested that already in the other thread.

    Something like:

    strSQL = "SELECT DISTINCT [Réf commande] FROM [Données facture RequêteTEMP]" _
           & " WHERE [Réf client] = 1"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    Do While (Not rst.EOF)
    
        strSQL1 = "SELECT * FROM [Données facture Requête par table]" _
                & " WHERE [Réf commande] =  " & rst![Réf commande] _
                & " AND [Réf client] = 1"
        Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
        If ImprimerFacturesParTable = "Oui" And rst1.RecordCount > 0 Then
            DoCmd.OpenReport "FacturePaiementsParTable", acViewNormal, , "[Réf commande]=" & rst![Réf commande]
        End If
        Do While (Not rst1.EOF)
            rst1.Edit
            rst1![CouponCuisineImprimé] = -1
            rst1![FactureImprimée] = -1
            rst1![FactureImpriméeParTable] = -1
            rst1.Update
            rst1.MoveNext
        Loop
        DoCmd.RunSQL "UPDATE [Données facture RequêteTEMP] SET FactureImpriméeParTable = True WHERE [Réf commande] = " & rst![Réf commande] & " AND FactureImpriméeParTable = False AND [Réf client] = 1"
    Loop
    
    

    It is hard to fully understand your code, so perhaps I mixed a little betrween rst and rst1.

    In fact rst picks up the DISTINCT value of [Réf commande]. For each of the values you then print the report and sets the FactureImprimééParTable flag. I also inserted a loop in rst1 to set some other flags, but here is where I got lost. You should be able handle this correct.

    Imb.

    Saturday, November 7, 2015 11:47 PM

All replies

  • Just a small update, it seems that the code is stuck somewhere because I tried to put Me.visible=false and somehow the code doesn't do it... may be we need something to break the code or stop or liberate the form?

    Claude


    Claude Larocque

    Thursday, November 5, 2015 10:45 AM
  • Hi Claude,

    After the printing of the report you can try this, assuming you want the set the flags in [Données facture Requête par table]:

    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)
    
    Do While (Not rst1.EOF)
        rst1.Edit
        rst1![CouponCuisineImprimé] = -1
        rst1![FactureImprimée] = -1
        rst1![FactureImpriméeParTable] = -1
        rst1.Update
        rst1.MoveNext
    Loop
    
    
    
    

    Of course feel free to use the With construction.

    If you want to set the flags in the TEMP recordset, then use the other set.

    I do not (yet) see the necessity to have a TEMP table apart from the "par table" table, but that is probably because i do not oversee the total project.

    Imb.


    Thursday, November 5, 2015 11:21 AM
  • Update:

    I am keep trying and I almost got it, I say almost because now I receive an error 3197 "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

    In my test I have 3 invoices, it prints the first one ok, then prints the second one OK, then the error comes up and nothing prints... however all my fields are set to TRUE like it shoulds... for the 3 orders... go figure!

    I tried your code above and I still have an error 3197 and it does the samething for the printing.

    Darn!

    :)


    Claude Larocque

    Thursday, November 5, 2015 12:47 PM
  • Update:

    I am keep trying and I almost got it, I say almost because now I receive an error 3197 "The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."

    In my test I have 3 invoices, it prints the first one ok, then prints the second one OK, then the error comes up and nothing prints... however all my fields are set to TRUE like it shoulds... for the 3 orders... go figure!

    I tried your code above and I still have an error 3197 and it does the samething for the printing.

    Darn!

    Hi Claude,

    Probably there is something going on that you edit a control in your form. On that moment there will be a conflict with the record in the recordset to be edited.

    Perhaps you can place a "If (Me.Dirty) Then Me.Dirty = False" as the first line in the Click event of  ImprimerTousTable1.

    Imb.

    Thursday, November 5, 2015 1:13 PM
  • Hi Imb, it does not work the dirty stuff, I can't understand why I can print invoice 1 and 2 and the error comes up after 1 and 2... if I would have 4 invoices, I'll bet that the invoice 1, 2 ,3  would print...

    I am testing...

    Oups wrong, with 4 invoices only the 1 and 2 are printing, they're is no other user... something in my code is not ok because the field to receive a -1 value is correct, on all order détails, the field [FactureImpriméeParTable] is -1, may be the place where the following code is place:

    DoCmd.RunSQL "UPDATE [Données facture RequêteTEMP] SET [Données facture RequêteTEMP].FactureImpriméeParTable = True WHERE ((([Données facture RequêteTEMP].FactureImpriméeParTable)=False) AND (([Données facture RequêteTEMP].[Réf client])=1));"

    (The above code is on 1 line...)

    Private Sub ImprimerTousTable1_Click()
    If (Me.Dirty) Then Me.Dirty = False
    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
             DoCmd.RunSQL "UPDATE [Données facture RequêteTEMP] SET [Données facture RequêteTEMP].FactureImpriméeParTable = True WHERE ((([Données facture RequêteTEMP].FactureImpriméeParTable)=False) AND (([Données facture RequêteTEMP].[Réf client])=1));"
             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

    Hopefully you can help

    Claude


    Claude Larocque

    Friday, November 6, 2015 2:01 PM
  • I'm more than a little confused by your code.  Does the field [Réf commande] come from table [Données facture RequêteTEMP]?  If so, does this do what you need?

        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 Not rst.EOF Then
            Do Until rst.EOF
                ' Print report
                DoCmd.OpenReport "FacturePaiementsParTable", _
                                 acViewNormal, _
                                 , _
                                 "[Réf commande]=" & rst![Réf commande]
                ' Mark report as printed
                rst.Edit
                rst![CouponCuisineImprimé] = -1
                rst![FactureImprimée] = -1
                rst![FactureImpriméeParTable] = -1
                rst.Update
                
                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

    -Bruce

    Saturday, November 7, 2015 12:35 AM
  • Thanks Bruce, at least the error 3197 is gone, we are almost there, however, it prints the orders according to how many items there are in an order, for example:

    Print first: Réf commande # 3
    then      Réf commande # 2
    then      Réf commande # 2 - again
    then      Réf commande # 1
    then      Réf commande # 1 - again
    then      Réf commande # 1 - again
    then      Réf commande # 4

    The articles in these orders (Réf commande) are as follows:

    That was the reason I had created another recordset that was giving me only the orders for table 1, unfortunately that recordset can't be updated because it is group by orders:

    See rst1 and strSQL1 in original code above.

    Thanks again for your help

    Claude


    Claude Larocque

    Saturday, November 7, 2015 10:08 AM
  • Hi Claude.

    You have first to take the DISTINCT values of [Réf commande], and for each value you can print the report. If I remember well, I suggested that already in the other thread.

    Something like:

    strSQL = "SELECT DISTINCT [Réf commande] FROM [Données facture RequêteTEMP]" _
           & " WHERE [Réf client] = 1"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
    
    Do While (Not rst.EOF)
    
        strSQL1 = "SELECT * FROM [Données facture Requête par table]" _
                & " WHERE [Réf commande] =  " & rst![Réf commande] _
                & " AND [Réf client] = 1"
        Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
        If ImprimerFacturesParTable = "Oui" And rst1.RecordCount > 0 Then
            DoCmd.OpenReport "FacturePaiementsParTable", acViewNormal, , "[Réf commande]=" & rst![Réf commande]
        End If
        Do While (Not rst1.EOF)
            rst1.Edit
            rst1![CouponCuisineImprimé] = -1
            rst1![FactureImprimée] = -1
            rst1![FactureImpriméeParTable] = -1
            rst1.Update
            rst1.MoveNext
        Loop
        DoCmd.RunSQL "UPDATE [Données facture RequêteTEMP] SET FactureImpriméeParTable = True WHERE [Réf commande] = " & rst![Réf commande] & " AND FactureImpriméeParTable = False AND [Réf client] = 1"
    Loop
    
    

    It is hard to fully understand your code, so perhaps I mixed a little betrween rst and rst1.

    In fact rst picks up the DISTINCT value of [Réf commande]. For each of the values you then print the report and sets the FactureImprimééParTable flag. I also inserted a loop in rst1 to set some other flags, but here is where I got lost. You should be able handle this correct.

    Imb.

    Saturday, November 7, 2015 11:47 PM
  • Thanks Imb,

    After working with your last reply, I got it to work correctly, thanks for all your input, the distinct didn't make any difference, but the rest was ok, I just had to reverse the recordsets and it was working...

    Because of person like you, we are able to construct strong database and succeed in business.

    Thanks again..

    Claude


    Claude Larocque

    Tuesday, November 10, 2015 5:39 AM