Solved

Loop through Report Records Update table

Posted on 2013-11-13
6
289 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Normalization of a table 19 74
Question about DB Schema 27 56
Access datasheet - showing a number in scientific notation 4 22
Reference Controls on subforms 7 28
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
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…

772 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