gogetsome
asked on
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:
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.
DoCmd.OpenReport "New Supplier Debit Summary", acViewPreview, WhereCondition:=strSQL, OpenArgs:=SubmittedToAccounting
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
<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
did you try running your codes from a button click event in a FORM
before opening your report
ASKER
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:=SubmittedToAccou nting
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:=SubmittedToAccou
ASKER
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?
Is there another place to do my loop after the where condition is applied or after the report is fully rendered?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thank you both very much for your assistance!
Open in new window
If the criteria involved a text field (the above assumes numeric):
Open in new window