Looping Through DAO Record-set, How can I update table while it loops?

[g4tv4life]

Hey everyone, I recently had some help getting this code working, but now I need to add functions to it. The Code is used to export each page of a report as a separate pdf.
Now what I need to do is add to the loop so that it 1. Changes a yes/no field to yes 2. Save the Path of the PDF to a field



How can I go about adding that into this code? thanks guys

Private Sub Command16_Click()


Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
 
strRptName = "Report"
strSQL = "Select * From [Police Departments Query];"


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)


With MyRS


Do While Not MyRS.EOF
   DoCmd.OpenReport strRptName, acViewPreview, , "[ID]=" & ![ID], acHidden
   DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"
   DoCmd.Close acReport, strRptName, acSaveNo
   
   .MoveNext
Loop


End With
MyRS.Close
Set MyRS = Nothing
End Sub

Edit: I've been looking into the rs.edit function which should serve my purposes but I can't get the syntax right, Anyone familiar with this?

[June7]

Couple ways.

1. If the fields are in the recordset, edit the recordset then run Update to commit edits to table
Do While Not .EOF
...
.Edit
.[yes/no field name] = True
.[filename field name] = [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"
.Update
...
Loop

2. If the fields are not in the recordset, send the edit directly to table
CurrentDb.Execute "UPDATE tablename SET [yes/no field name]=True, [filename field name]='" & [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf' WHERE ID=" & !ID

[g4tv4life]

I tried using the 2nd option and it looped through and created the pdfs, however it only updated the first record, not the others. Can you see why its not looping properly?

Private Sub Command16_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
 
strRptName = "Report"
strSQL = "Select * From [Police Departments Query];"


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
With MyRS


Do While Not MyRS.EOF
   DoCmd.OpenReport strRptName, acViewPreview, , "[ID]=" & ![ID], acHidden
   DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"
   DoCmd.Close acReport, strRptName, acSaveNo
   CurrentDb.Execute "UPDATE [Main Table] SET [PDF Created and Emailed]=True, [Path to PDF]='" & [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf' WHERE ID=" & ID
  
   .MoveNext
Loop
End With
MyRS.Close
Set MyRS = Nothing
End Sub

I also tried the first method, and the fields are in the recordset but its saying they aren't being found. Could the fact a query is the recordset be affecting this?

.Edit
    .[PDF Created and Emailed] = True
    .[Path to PDF] = [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"
    .Update

[ItsMe]

I would suggest adding a movefirst method.
https://www.accessforums.net/program...tml#post210214

[June7]

The first method should work if the query is updatable.

The code you show for the second approach is referencing ID on the form, not from the recordset.

I also had error in my code. I showed . instead of !.

WHERE ID=" & !ID

[g4tv4life]

The first method should work if the query is updatable.

The code you show for the second approach is referencing ID on the form, not from the recordset.

I also had error in my code. I showed . instead of !.

WHERE ID=" & !ID

That fixed it, I also added a .movefirst method to the code as per ItsMe's Suggestion. Here's the final working code if anyone else who sees this thread would like to use it. June, yet again you've been a great help.
(and yes, I'm actually learning from these mistakes so I don't have to keep coming back for the same)

Private Sub Command16_Click()


Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
 
strRptName = "Report"
strSQL = "Select * From [Police Departments Query];"


Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
With MyRS
.MoveFirst
Do While Not MyRS.EOF
   DoCmd.OpenReport strRptName, acViewPreview, , "[ID]=" & ![ID], acHidden
   DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf"
   DoCmd.Close acReport, strRptName, acSaveNo
   CurrentDb.Execute "UPDATE [Main Table] SET [Archived]=True, [Path to PDF]='" & [Application].[CurrentProject].[Path] & "\Aed letters\" & "Aed Letter " & ![Officer Name] & " " & Format(![Date of Incident], "MMMM dd, yyyy") & ".pdf' WHERE ID=" & !ID
    
   .MoveNext
Loop


End With
MyRS.Close
Set MyRS = Nothing
End Sub

[ItsMe]

You have to keep coming back. The reason I joined this forum and one of the main reasons I participate is to keep learning.

[g4tv4life]

Good Point, I plan to keep coming back. I still have a multiuide of things to code out and get working. My next step is to try to insert into this code a automatic
Emailer for Lotus notes, I have the code for it and I'm positive there will be issues. I like answering questions as well so you'll see me around. I prefer to instruct than to always
be the student.

[June7]

Glad it's working but am disappointed the first method fails.

The .MoveFirst never hurts but since you were getting the PDFs, really didn't make a difference. You did check the PDF's and they did show the correct data?

[g4tv4life]

Considering the database will be used by someone who barely can make a form, Any redudencies to stop errors is a plus. I had to make the error handling bulletproof. The database creates form letters, and yes they came out perfectly. I'm sure the 1st method works fine, but I had too many issues trying to getting it to work and the 2nd method works perfectly, the speed on it isn't half bad either considering I use a 32 bit old pc and the rest of the office has 64bit processors and access versions with stronger processors so I'm sure it will be even faster then.

Glad it's working but am disappointed the first method fails.

The .MoveFirst never hurts but since you were getting the PDFs, really didn't make a difference. You did check the PDF's and they did show the correct data?