Solved

Loop through Report Records Update table

Posted on 2013-11-13
6
286 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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
<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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:gogetsome
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now