Letter system

[pop]

Tables:
I have a table which is linked to our company's client list (about 12,000 entries). I then have another table which add information for to the other table (a check box, invoice number and the amount). Then I make a query which put the two tables together.

Form:
This query is then used in a form. The A/R can then choose the type of letter they want to send(payment confirmation, welcome letter, past due invoice ...). Then below there is the query with all the information. The A/R can then put a check mark to the client they want to send the letter to. They must then click on print the letter in order to print them.

VBA:
What i want VBA to do is to check where there are check marks and produce the report with the right client. I have a report for all the type of letter. So the only thing missing is the code to go through all the data. I tried one which was working, but to go through all the data it was taking about 30 min, which is kind a long.

Private Sub Command5_Click()
Dim dernier As Long
Dim vcount
Dim reqstr
Dim nbrpage
Dim Rptname

dernier = Me.sfrmList.Form.RecordsetClone.RecordCount

reqstr = "("
nbrpage = 1

Me.sfrmList.Form.Recordset.MoveFirst
vcount = 1
Do While vcount <= dernier     ' Inner loop.
   vcount = vcount + 1    ' Increment Counter.
   If sfrmList!inclure Then
      If nbrpage = 1 Then
         reqstr = reqstr & sfrmList!ID
      Else
         reqstr = reqstr & ", " & sfrmList!ID
      End If
      nbrpage = nbrpage + 1
   End If
   Me.sfrmList.Form.Recordset.MoveNext
Loop
reqstr = reqstr & ")"
tt = "lucie fer"
If reqstr <> "()" Then
Select Case cboType.Column(0)
        Case 1
           Rptname = "PastDueLetter"
        Case 2
           Rptname = "ConfirmLetterVisa"
        Case 3
           Rptname = "CreditLetter"
        Case 4
           Rptname = "TaxExemptLetter"
        Case 5
           Rptname = "WelLetter"
        Case 6
           Rptname = "ConfirmLetterMasterCard"
        Case 7
           Rptname = "FinalNotice"
End Select

   DoCmd.OpenReport Rptname, acViewPreview, , "list.ID in " & reqstr
End If

End Sub

Here was my vba code:
[CODE]Private Sub Command5_Click()
Dim last As Long
Dim vcount
Dim reqstr
Dim nbrpage
Dim Rptname

last = Me.sfrmList.Form.RecordsetClone.RecordCount

reqstr = "("
nbrpage = 1

Me.sfrmList.Form.Recordset.MoveFirst
vcount = 1
Do While vcount <= last     ' Inner loop.
   vcount = vcount + 1    ' Increment Counter.
   If sfrmList!inclure Then
      If nbrpage = 1 Then
         reqstr = reqstr & sfrmList!ID
      Else
         reqstr = reqstr & ", " & sfrmList!ID
      End If
      nbrpage = nbrpage + 1
   End If
   Me.sfrmList.Form.Recordset.MoveNext
Loop
reqstr = reqstr & ")"
If reqstr <> "()" Then
Select Case cboType.Column(0)
        Case 1
           Rptname = "PastDueLetter"
        Case 2
           Rptname = "ConfirmLetterVisa"
        Case 3
           Rptname = "CreditLetter"
        Case 4
           Rptname = "TaxExemptLetter"
        Case 5
           Rptname = "WelLetter"
        Case 6
           Rptname = "ConfirmLetterMasterCard"
        Case 7
           Rptname = "FinalNotice"
End Select

   DoCmd.OpenReport Rptname, acViewPreview, , "list.ID in " & reqstr
End If

End Sub

If there are any information you would like to know please let me know.

Thanks,

Pop

 

[pop]

bump!!!!!!!!!!!

 

[DCrake]

Not entirely sure but the reason why your vba is taking a long time is that it is enumerating through the forms recordset and repainting the record to the form then checking the contents of the form controls.

What may be better is to use a straightforward recordset based on the underlying query of the forms recordsource.

Then enumerate through the recordset referring to the actual field names in the recordset.

Dim last As Long
Dim vcount
Dim reqstr
Dim nbrpage
Dim Rptname
[COLOR="Red"]Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("[B]QueryNameHere[/B]")[/COLOR]

[COLOR="red"]Rs.MoveLast[/COLOR]

[COLOR="red"]last = Rs.RecordCount
Rs.MoveFirst[/COLOR]

reqstr = "("
nbrpage = 1


vcount = 1
Do While vcount <= last     ' Inner loop.
   vcount = vcount + 1    ' Increment Counter.
   If [COLOR="red"]RS("inclure") [/COLOR]Then
      If nbrpage = 1 Then
         reqstr = reqstr & [COLOR="red"]Rs("ID")[/COLOR]
      Else
         reqstr = reqstr & ", " & [COLOR="red"]Rs("ID")[/COLOR]
      End If
      nbrpage = nbrpage + 1
   End If
   Rs.MoveNext
Loop
reqstr = reqstr & ")"
If reqstr <> "()" Then
Select Case cboType.Column(0)
        Case 1
           Rptname = "PastDueLetter"
        Case 2
           Rptname = "ConfirmLetterVisa"
        Case 3
           Rptname = "CreditLetter"
        Case 4
           Rptname = "TaxExemptLetter"
        Case 5
           Rptname = "WelLetter"
        Case 6
           Rptname = "ConfirmLetterMasterCard"
        Case 7
           Rptname = "FinalNotice"
End Select

   DoCmd.OpenReport Rptname, acViewPreview, , "list.ID in " & reqstr
End If
[COLOR="red"]Rs.Close
Set Rs = Nothing[/COLOR]

 

[pop]

Workout great with little adjustment, thanks a lot.

But now I have a minor problem with the external database. If the field has a max char of 50 char and the field has 10 char, the other 40 char are replaced by 40 spaces. I was wondering if there is a way to completely remove those spaces in my query or simply erasing when there are two spaces in a row.

Thanks again,


Cope

 

[DCrake]

Sound slike you are linked to a SQL database and the type of character fild is padding out the spaces. Try using Trim() anround the field.

David

 

[pop]

Thank you, work out really well.

Pop