Update Values in Previous Row in Continuous Subform 2

[larrydavid]

Hello,

Using Access 2003 sp3 with lnked tables in SQL Server 2008.

I have a subform which uses a vba module (courtesy of Allen Browne) to carry over the values from the current record to a new record. Everything works fine except now I need to update a couple of fields that we're in the current row (now the previous row). I have tried many things over the past couple of days and cannot get this to happen. I've tried setting variables in Before and AfterUpdate events, tried grabbing the values of the current row and putting them in variables in the Form_Current(), but no go.

I have hardly any hair left after having pulled most of it out over this, so any help would be much appreciated!

Thanks,
Larry

 

[MajP]

You will have to explain this much more, there is not enough detail to provide a recommended solution. However the solution will likely involve an update query.

Public Sub updateValues
  dim strSql as string
  dim PK
  dim val1, val2, val3....
  val1 = me.somefield
  val2 = me.somefield2
  val3 = me.somefield3
  PK = some Function or means to determine the Primary key of the "previous record"
  strSql = "UPDATE tableName SET column1 = " & val1 & " column2 = " & val2... &"
  strSql = strSql & " WHERE some_column = " & PK
  currentdb.execute strSql
end sub

 

[larrydavid]

Here is what I have so far:

Private Sub UpdatePrevious()

Dim dbs As DATABASE
Dim rs As DAO.Recordset
Dim sqlMax As String
Dim result As Integer
Dim strSQL As String

sqlMax = "select max(id) from dbo_CA_EFT_Posting"

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset(sqlMax, dbOpenDynaset, dbSeeChanges)

If rs.Fields.Count = 1 Then
result = rs.Fields(0)
End If

strSQL = "UPDATE dbo_CA_EFT_Posting SET [Amount_to_Distribute] = '" & Me.[Amount_to_Distribute] & "' - '" & Me.[PSTED_AMT] & _
"' WHERE ID = '" & result & "'"
dbs.Execute strSQL, dbFailOnError + dbSeeChanges

Set dbs = Nothing

End Sub

I am trying to subtract the [PSTED_AMT] value being inserted, from the [Amount_to_Distribute] value in the existing (current) row. The result I need is for the [Amount_to_Distribute] in the current row/record to update to the new result.

Hope this makes sense.

Thanks,
Larry

 

[PHV]

Something like this ?
strSQL = "UPDATE dbo_CA_EFT_Posting SET Amount_to_Distribute=Amount_to_Distribute - " & Me![PSTED_AMT] & _
" WHERE ID=" & DMax("id", "dbo_CA_EFT_Posting")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[larrydavid]

OMG it worked! And I've been beating my head against the wall on this for IDK how long! THANK YOU PHV!!!