Update my query after the recordset is printed

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

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

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