multiple commands in loop

rick roberts

Registered User.
Local time
Today, 23:41
Joined
Jan 22, 2003
Messages
160
i have a form that contains records regarding overdue payments and a button that prints a letter to all records on that form - this works fine. i want to add another command after the printing command to tick a box indicating that a letter has been sent but when i add the command (straight after the print comand) it only ticks the first record - it doesnt seem to stay within the loop. ive tried just using the tick command on its own with the same result so i guess the problem is the way im commanding the tick? CheckLetter1 being the tickbox in question. any suggestions please? thanks in anticipation.

'If only one record
If reccount = 1 Then
DoCmd.OpenReport "rptOutstandingLetter1", acViewPreview
Exit Sub

Else
a = 1
Do Until a = reccount
DoCmd.OpenReport "rptOutstandingLetter1", acViewPreview
CheckLetter1.Value = 1
a = a + 1
Loop
DoCmd.OpenReport "rptOutstandingLetter1", acViewPreview

End If
 
Is this the actual code?
From this code you would only change the current record because you're not changing records.
Your email would contain the same info for all listed records as the're is no reportfilter.
I would do it by looping a recordset like (untested code):
Code:
Dim rs as doa.recordset
Dim stDocName  as string
set rs = Me.Form.RecordsetClone
if rs.count > 0 then
   while not rs.eof
       stDocName = "rptOutstandingLetter1"
       DoCmd.OpenReport stDocName, acViewPreview, , "[COLOR="DarkRed"]Idfield = " & Me.Idfield[/COLOR]
       DoCmd.SendObject acReport, stDocName, acFormatSNP, rs!mailadres, , , _
        "[COLOR="DarkRed"]Mailheader[/COLOR]", _
        "[COLOR="darkred"]Mailbody[/COLOR]", False 'The false means direct sending
      DoCmd.Close acReport, stDocName
      rs.edit
      rs!CheckLetter1 = True
      rs.Update
      rs.movenext
   Wend
End If
rs.close
set rs = Nothing

The part in red needs to be changed to the name of your ID field or to the desired message header or footer.

** edit, added rs.movenext, see post 7
 
Last edited:
i changed a few minor things to get the code to run for me but it stops at the line in red saying "item not found in this collection" i dont know if its significant but the actual name of the box has a lower case c but when i change it vb automatically gives it an upper case. i tried removing the "rs!" from the command but that seems to give it a never ending circle and it just runs forever. i hope this makes some sort of sense and thanks for your help so far

Option Compare Database
Dim rs As DAO.Recordset


Private Sub Command13_Click()

Dim stDocName As String
Set rs = Me.Form.RecordsetClone
If rs.RecordCount > 0 Then
While Not rs.EOF
stDocName = "rptOutstandingLetter1"
DoCmd.OpenReport stDocName, acViewPreview, , "ID = " & Me.ID
DoCmd.Close acReport, stDocName
rs.Edit
rs!CheckLetter1 = True
rs.Update
Wend
End If
rs.Close
Set rs = Nothing
End Sub
 
Without seeing the form itself and the text box names I can only guess. There are a few things that come to mind however.

First. After you clone the recordset, cycle through the records to get an accurate RECORDCOUNT. This wont solve your problem but DAO requires it. And the While .eof statement right after it will do the same thing as the if. This is just extra code unless you are going to add and ELSE in later.

Second. Check the query that the form is built on to make sure that CHECKLETTER1 is in the field list.

Third. Make sure that your text box names arent competing with your field names. Just to make everything clear I name all the text fields with a 'tb' prefix rather than have to keep checking to make sure i am referencing a field name rather than a text box.

Fourth. You dont have a MOVENEXT before the WEND. This will merrily cycle through the first record of the recordset until all the electrons have died of exhaustion. When you remove the rs!, VB just treats this as a variable.

Fifth. I am not sure what you are filtering by in the DoCmd statement. You are cycling through a cloned recordset rs but filtering by the ME.ID which is based on the recordset you cloned from. If you dont move the bookmark in the underlying recordset you will get the same ID every time.
 
thanks for your help with this matter - i think the main problem is the missing MoveNext statement as your explanation does point to the problem im experiencing but after inserting it before the WEND i get a compile error stating "Sub or Function not defined"
as for your fifth suggestion im not sure what is meant -- im not filtering anything - thats done in the query already - i am using every record - does this make some of the DoCmd statement obsolete?
thanks again for your assistance
 
From the error you're getting you put "Movenext" before the wend, it should be rs.movenext (sory forgot it in my sample).
I think you're preview won't work for multiple records as the code wil be running when showing the preview. That's the reason my sample uses shapshots and mail. In access2007 it's possible to use PDF as output.
 
i thought i was there - seemed to be working fine and i changed the view to normal rather than preview -- turned out that all printing was for the first record - but it ticked all the boxes - i guess thats what you meant - if you have a better way of approaching this id be glad to listen - you mentioned a sample?
client wanted the output in something that the customer cant pretend not to be able to open in email i.e. a format that comes with windows thanks for your assistance
 
You're code has no mail sending in it, testing on screen will only show one record. Printing should give more if you're not using subforms.
I don't know what version of access you are using but a PDF is reader is on most systems available. From access 2007 output to PDF is supported.
Native windows would mean .rtf or .txt, just change "acFormatSNP" from my sample in post 2 in "acFormatRTF" or "acFormatTXT"
 
i did change from preview to print for that very reason - to see in black and white what the results were and it prints only the first record for each pass of the loop.
maybe i should try loopng the two seperately after all ive had both procedures working at some stage -- just not together.
i originally did use .RTF - im not even sure now what format SNP represents.
i took out the mail sending to simplify the coding with a view of adding it when my problm is solved - it looks for an email in the appropriate textbox and if it exists - it emails - if not - it prints
thanks again
 
the plot thickens - it works ok now except that i end up with twice the printing - it prints all the records in order - and then repeats - i cant se anything in the code that might make it do that

Option Compare Database
Dim rs As DAO.Recordset

Private Sub Command13_Click()
Dim stDocName As String
Set rs = Me.Form.RecordsetClone
While Not rs.EOF
stDocName = "rptOutstandingLetter1"
DoCmd.OpenReport stDocName, acViewNormal, "ID = " & Me.ID
DoCmd.Close acReport, stDocName
rs.Edit
rs!CheckLetter1 = True
rs.Update
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
 
Your code seems okay, put a break in your code and step trough to see if the code is triggered twice.
If it's triggered twice you can put a if statement around the printing and check if it's already send.
Code:
if not(rs!CheckLetter1) then
 
the program has decided to revert to just printing the first record for each pass again without me changing any of the code so its 1 step forward and 2 steps back it seems
thanks for all your help with this sorry to have wasted your time
 

Users who are viewing this thread

Back
Top Bottom