Placing a previous date in the current row

[Lilli]

SOS!

I have a form with the following 3 fields:

1) ID
2) TheDate
3) PreviousDate

I'm trying to put the previous date in the same row with the current date for easier comparison but I've struggled for over a week now.

Please show me what's wrong with this code. I entered

=PrevRecVal(Form,"ID",[ID],"TheDate") in the control source of PreviousDate, but I keep getting #Name? when I switch to Form view.

What am I doing wrong? Please, someone show the proper way with a quick example if possible. Much appreciation and thanks.




Function PrevRecValue(F As Form, ID As String, TheDate As String)
Dim RS As DAO.Recordset

' The default value is zero.
PrevRecValue = 0

' Get the form recordset.
Set RS = Me.RecordsetClone

' Find the current record.
Select Case RS.Fields(TheDate).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & ID & "] = " & ID
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecValue = RS(TheDate)

End Function

 

[mhartman]

Hello:

I believe you want to use the PreviousControl method to obtain that value: Below is the syntax

Screen.PreviousControl.OldValue

Search online help for complete example

Regards
Mark

 

[Lilli]

Hello:

I believe you want to use the PreviousControl method to obtain that value: Below is the syntax

Screen.PreviousControl.OldValue

Search online help for complete example

Regards
Mark

Thank you mhartman for the response. I'll look up your suggestion.

Hopefully it will work a lot easier. By the way, there are JUST a few parts

that I am not understanding what to do with in this Microsoft formula for

previous dates. I have underlined them:



' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Function PrevRecVal(F As Form, KeyName As String, KeyValue, _
FieldNameToGet As String)
Dim RS As DAO.Recordset

On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0

' Get the form recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
'Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)

Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function


What is actually supposed to replace the "keyvalue" word? My keyname is "intID" and I couldn't figure out if an actual number was required in place of "keyvalue" in the actual code.

By the way, my "Id" field is a Long datatype, but I was also confused about which Case to select, being that the infor in the "FieldNameToGet" is of a Date type.

What do I put in place of KeyValue in the opening declaration and in the Case statement?

Also, what is meant by the word "Type" in the the select Case statement? Am I supposed to replace it with my keyfield (intID)'s datatype or does it stay the same?

Lastly, is F (as in form) meant to be a form name?

Please help interpret those few parts of the code sample that I have underlined. You don't have to answer all the questions.

 

[Lilli]

Does this problem have an easier solution?

 

[boblarson]

I think part of the problem comes in that how do you determine what is the previous date? If you are trying to rely on the table being ordered in some way, then you are going to frustrate yourself to no end as a table, while it may LOOK at times to be ordered, is not stored in any particular order. If you want to be able to guarantee an order, you will have to have some field that guarantees that (autonumber doesn't guarantee that - it can for the most part move upward, but if you ever got into replication that would fly out the window), so perhaps a date/time stamp of when the record was entered. Then, a query, or a DMax can pull the latest date that is there and you could populate your information like that. However, you should not be storing information that you can gather in other ways.

 

[Lilli]

Thank you very much for the response, Bob. I appreciated it a lot.

Is there a simpler way to place a next value in the current row?

Nothing seems to be working - not even the dmax. All I got when I

attempted to create the autonumber-like count you suggested in my query

expression was the maximum ID value to repeat itself in every row. Or am

I missing something. I just wish Access would allow an easy way to create

an autonumber-like count in a query, either that or have an in-built method

to subtract adjacent rows. I've gained about 5 lbs locked in my computer

chair trying to figure either one out. Please help.

 

[Lilli]

Does anyone know how to do either of the two above?