Solved

Loop through Report Records Update table

Posted on 2013-11-13
6
291 Views
Last Modified: 2013-11-13
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
Comment
Question by:gogetsome
  • 3
  • 2
6 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39645990
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39645993
<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
 

Author Comment

by:gogetsome
ID: 39646047
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:gogetsome
ID: 39646073
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39646122
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
 

Author Closing Comment

by:gogetsome
ID: 39646162
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question