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
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
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 & ""
Set Rs = Nothing