CODEWITHSUNDEEP
sqlvbams-access
Fil
Fil

Reputation: 471

How to use DAO.Recordset to update a table using a non-Updateable Query

I have a fairly small table tblFunding with 20 records. I want to update its field Payment using results of a non-updateable query ie:

"SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1))) Between -7 And 4)) GROUP BY UserName") I know it is bad practice to store this type of data but the user wants to have a glance at the data from time to time since the table is bound to a form for his convenience. We came out with a method that uses DAO which works but it leaves nulls for records that do not exist for UserName field in the updated table i.e tblFunding. We would prefer the value to be 0 in case the fields do not match. The code runs before the form opens which means the table is updated before the form is launched. Is there a way to politely accomplish this task? Please see the code below and advise wherever you can. Thank you!

Private Sub btnGlance_Click()

Dim rs1 As DAO.Recordset
Dim rs 2 As DAO.Recordset

Set rs1 = CurrentDb.OpenRecordset("SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1))) Between -7 And 4)) GROUP BY UserName")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM tblFunding")

rs1.MoveFirst
  Do Until rs1.EOF
  rs2.MoveFirst
  Do Until rs2.EOF
  If rs1.Fields("UserName") = rs2.Fields("UserName") Then
  rs2.Edit
  rs2.Fields("Payment").Value = rs1.Fields("Payment").Value
  rs2.Update
  End If      
  rs2.MoveNext
  Loop
rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

Docmd.OpenForm "frmUserGlance"

End Sub

Upvotes: 1

Views: 383

Answers (1)

June7
June7

Reputation: 21379

Could run an UPDATE action to change all Null to 0.

CurrentDb.Execute "UPDATE tblFunding SET Payment = 0 WHERE Payment Is Null"

Or consider alternate code:

rs2.MoveFirst
Do Until rs2.EOF
  rs1.MoveFirst
  rs1.FindFirst "UserName = '" & rs2!UserName & "'"
  rs2.Edit
  If Not rs1.NoMatch Then
     rs2!Payment =  rs1!Payment
  Else
     rs2!Payment = 0
  End If
  rs2.Update
  rs2.MoveNext
Loop

Alternative to display this summary data on form could use domain aggregate function. Build a query object that does the summation then use DLookup to pull a particular value. Or use DSum() directly on source table.

Could avoid all this code if just did a query that JOINED filtered summation query to dataset of all user names.

Upvotes: 1

Related Questions