I am trying to select a single record from a table using a SQL Expression in my VBA Code so that I can move the record to a future date.
My code is as follows:-
Dim NewDb As DAO.Database
Dim Newtbl2 As DAO.Recordset
Dim NewSQL As String
NewSQL = "SELECT * From Pending where [AmountPending] = " & CapAmount & " AND " & "[AccountFrom] = " & CapFrom & " AND " & "[AccountTo] = " & CapTo & " AND " & "[DatePending] = #" & DateValue(CapDate) & "#"
Set NewDb = CurrentDb
Set Newtbl2 = NewDb.OpenRecordset(NewSQL, dbOpenDynaset)
CapDate = DateAdd("ww", 52, CapDate)
Newtbl2.Edit
Newtbl2![Remaining] = CapRem - 1
Newtbl2![Datepending] = CapDate
Newtbl2![MID] = Month(CapDate)
Newtbl2![YID] = Year(CapDate)
If CapFrom = 70 And CapTo = 80 Then
Newtbl2![AmountPending] = 100
ElseIf CapFrom = 80 And (CapTo = 24 Or CapTo = 62) Then
Newtbl2![AmountPending] = 50
End If
Newtbl2.Update
Newtbl2.Close
However, when the code gets to "Newtbl2.Edit", I get an error message come up saying "No Current Record".
Where am I going wrong with the syntax?