VBA code to determine the previous value.

[spenz]

I know that:

DMin("DatePaid","tblTransactions","TranID = " & me.TranID)
-This will give me the first payment date from tbltransactions based on form's TransactionID.

DMax("DatePaid","tblTransactions","TranID = " & me.TranID)
-This will give me the last payment date from tbltransactions based on form's TransactionID.

How about getting the DatePaid value of the previous record based from the last record? Any code for this one? Thanks in advance.

 

[ajetrumpet]

I know that:

DMin("DatePaid","tblTransactions","TranID = " & me.TranID)
-This will give me the first payment date from tbltransactions based on form's TransactionID.

DMax("DatePaid","tblTransactions","TranID = " & me.TranID)
-This will give me the last payment date from tbltransactions based on form's TransactionID.

How about getting the DatePaid value of the previous record based from the last record? Any code for this one? Thanks in advance.

you might have to do it through a recordset. either use the absolute position of the 2nd to last records. it depends on how you need the table to be sorted really. here is an example:

dim rs as recordset
set rs=currentdb.openrecordset("tablename", dbopendynaset)

with rs
   .movelast
   .moveprevious
      debug.print !datefield
end with

rs.close
set rs=nothing

 

[DCrake]

If the row you are currently on contains a primary key then you can use the following code

DMax("Field","Table","PrimaryKey<" & PrimaryKey)

So lets say we are on row 20 what we are saying is get me the highest value from the table where the primary key is less than 20. So what it should return is what is in the field for row 19.

David

 

[spenz]

David and Adam,
Thanks a lot guys, I tried both of your suggestions and it worked great. By the way, I also tried..


dim rs as recordset
set rs=currentdb.openrecordset("tablename", dbopendynaset)

with rs
.movelast
rs.findprevious "TransactionID = " & me.TransactionID
debug.print !datefield
end with
rs.close
set rs=nothing

This also worked the same for me, just like the .MovePrevious adam had suggested earlier.

 

[Banana]

Just to add that if this is from a form, there's no need for a 2nd recordset or a separate lookup/query:

With Me.RecordsetClone
    .MovePrevious
    'Get the fields you want here...
    ....

    'Not strictly necessary, but helpful if the code
    'may get called more than once by keeping the
    'clone synchronized.
    .Bookmark = Me.Recordset.Bookmark
End With

Also to be explicit - MovePrevious is fine as long as you don't care what the previous record is for. If you need to be sure that you're getting a certain value for say, group of records based on a client, FindPrevious may be a better choice.

 

[spenz]

Thanks banana. Iam actually using the .FindPrevious as of the moment because i encountered an error when using .MovePrevious earlier. Specifically, when there is no datepaid value found(if client has not yet paid). so with .findprevious it sort of made sure that there is an existing value before proceeding with code.

Well, i'm happy with so much choices you all provided. This website really rocks! Thanks again ^_^

 

[Banana]

Ah, yes.

Whenever you use either Move or Find you have to check that you are on a valid record because of how the recordset pointer works. It's possible for the pointer to be upon nonexistent record so we have to write in usual checks:

With rs
  .MovePrevious
  If Not .BOF Then
     'Do something...
  End If

  .MoveNext
  If Not .EOF Then
     'Do something...
  End If

  .FindFirst ...
  If Not .NoMatch Then
     'Do something...
  End If

  .Edit
     'Do something
  .Update
  .Bookmark = .LastModified

End With

There's probably more but hopefully this gives you an idea of what you need to do everytime you do anything with Move or Find so you don't try to do something illegal such as editing a record whent he pointer is after the last record (kind like trying to writing to a book that's already flipped all way over and is now laying on its left cover, showing you the right side of the desk and you're writing on the desk.. nonsense!)

 

[spenz]

Ah, yes.

Whenever you use either Move or Find you have to check that you are on a valid record because of how the recordset pointer works. It's possible for the pointer to be upon nonexistent record so we have to write in usual checks:

With rs
  .MovePrevious
  If Not .BOF Then
     'Do something...
  End If

  .MoveNext
  If Not .EOF Then
     'Do something...
  End If

  .FindFirst ...
  If Not .NoMatch Then
     'Do something...
  End If

  .Edit
     'Do something
  .Update
  .Bookmark = .LastModified

End With

There's probably more but hopefully this gives you an idea of what you need to do everytime you do anything with Move or Find so you don't try to do something illegal such as editing a record whent he pointer is after the last record (kind like trying to writing to a book that's already flipped all way over and is now laying on its left cover, showing you the right side of the desk and you're writing on the desk.. nonsense!)

Thanks for this tip banana.

As for that .Bookmark = .LastModified I don't know how that actually works. I tried adding it to my code and it just worked the same as not having to put it. So maybe there's a special purpose for that.

 

[Banana]

In DAO, when you execute AddNew, the pointer may not be right over the newly added record, and attempt to retrieve values may not be correct. Thus we set the pointer (e.g. Bookmark) to that of what was modified (LastModified).

While it's not always required for the task at hand, it's important to be aware of possibilities where we may have a pointer over nonexistent record and protect from this error. Checking the appropriate property is usually more robust than doing error handling anyway.

 

[spenz]

In DAO, when you execute AddNew, the pointer may not be right over the newly added record, and attempt to retrieve values may not be correct. Thus we set the pointer (e.g. Bookmark) to that of what was modified (LastModified).

While it's not always required for the task at hand, it's important to be aware of possibilities where we may have a pointer over nonexistent record and protect from this error. Checking the appropriate property is usually more robust than doing error handling anyway.

Oh i see! I remember i have this lngTrankey an autonumber Key ID which i extracted from a recordset. I was puzzled, because i know it's already been extracted but somehow i still can't use that key ID. So i thought i was doing it wrong. I just didn't continue that code because i thought iam not doing it right anyway and i know there's something lacking. So this must be it, use bookmark to retrieve the newest value extracted from the recordset. Thanks for this banana you're a great teacher and so is every veteran here who is tireless helping us newbies. Thanks all.