Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Loop through Report Records Update table

Hello, I have a report that I populate with a query that pulls all the records of a table. When I open the report I pass to the report a specific where condition like this:

DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition:=strSQL, OpenArgs:=SubmittedToAccounting

Open in new window


When the report opens I want to loop through the recordset and update rows in another table based on a primary key. The problem with my code below is that all the records in the table are being updated rather than just the records on the report that has the where condition applied.

What is wrong with my code? It is in the Report_Open of the report.

Dim SubmittedToAccounting As String
If Me.OpenArgs > "" Then
SubmittedToAccounting = Me.OpenArgs
End If

If SubmittedToAccounting = "1" Then

Dim Rs As DAO.Recordset
Set Rs = Me.Recordset

Dim DateNow As String
DateNow = Format(Now(), "mm/dd/yyyy")

If Not Rs.EOF And Not Rs.BOF Then

   Do Until Rs.EOF
       For N = 0 To Rs.Fields.Count - 1
          Dim myMDR As String
          myMDR = (Rs!MDR)

 If myMDR > "" Then
   DoCmd.RunSQL "Update [Cost of Poor Quality Table] Set [SentToAccounting]=#" & DateNow & "# where [DMR#] = " & myMDR & ""
 End If

       Next
       Rs.MoveNext
   Loop
   Rs.Close
End If

Set Rs = Nothing

End If

Open in new window

0
gogetsome
Asked:
gogetsome
  • 3
  • 2
1 Solution
 
mbizupCommented:
Try changing Line 22 to something like the following.  You will have to adjust the field names to match your own:


   DoCmd.RunSQL "Update [Cost of Poor Quality Table] Set [SentToAccounting]=#" & DateNow & "# where [DMR#] = " & myMDR & " AND ID = " & Me.ID

Open in new window


If the criteria involved a text field (the above assumes numeric):

   DoCmd.RunSQL "Update [Cost of Poor Quality Table] Set [SentToAccounting]=#" & DateNow & "# where [DMR#] = " & myMDR & " AND ID = '" & Me.ID & "'"

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
<What is wrong with my code? It is in the Report_Open of the report.>

did you try running your codes from a button click event in a FORM
before opening your report
0
 
gogetsomeAuthor Commented:
mbizup,

It says it can find the control me.MDR
     DoCmd.RunSQL "Update [Cost of Poor Quality Table] Set [SentToAccounting]=#" & DateNow & "# where [DMR#] = " & myMDR & " AND [DMR#] = '" & Me.MDR & "'"


Capricorn1,
Yes this is on a form button click:
Dim SubmittedToAccounting as string
SubmittedToAccounting = 1

 strSQL = "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"
DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition:=strSQL, OpenArgs:=SubmittedToAccounting
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
gogetsomeAuthor Commented:
mbizup, this is on a report were the me.MDR could be repeated for each row. Could that be the issue?
Is there another place to do my loop after the where condition is applied or after the report is fully rendered?
0
 
Rey Obrero (Capricorn1)Commented:
place this codes in the click event of the button in the FORM and DELETE the codes in the Open event of the report


Dim SubmittedToAccounting as string
Dim Rs As DAO.Recordset
Dim DateNow As String
Dim myMDR As String
SubmittedToAccounting = 1

 strSQL = "[Date] Between #" & Me.Start_Date.Value & "# and #" & Me.End_Date.Value & "#"
 DateNow = Format(Now(), "mm/dd/yyyy")

 
 set rs=currentdb.openrecordset("select * from TableX where " & strSQL)
 
 if rs.eof then exit sub
 
 do until rs.eof
	myMDR = (Rs!MDR)
	
	 If myMDR > "" Then
	   DoCmd.RunSQL "Update [Cost of Poor Quality Table] Set [SentToAccounting]=#" & DateNow & "# where [DMR#] = " & myMDR & ""
	 End If


       Rs.MoveNext
  Loop
 
 rs.close
 
 DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition:=strSQL, OpenArgs:=SubmittedToAccounting
                                  

Open in new window

0
 
gogetsomeAuthor Commented:
Perfect! Never thought to do it before the report. I was hammering a square peg through a round hole.
Thank you both very much for your assistance!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now