Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Loop through Report Records Update table

Posted on 2013-11-13
6
Medium Priority
?
296 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
Industry Leaders: 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!

 

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

916 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