Full Version: Referring To Previous Record
UtterAccess Forums > MicrosoftŪ Access > Access Forms
jimmy0305
In my Control's OnClick Event I have:
It only returns "0" instead of the previous data. Can someone please tell me what am I doing wrong?
In my table (tblProgressNotes), I have:
Field Name :ProgressNote_ID
Data Type : AutoNumber (PK)
Field Name :Vital_ID
Data Type : Integer (FK)
Indexed : Yes (Duplicates OK)
Field Name :PriorTerm
Data Type : Text
Field Name :PresentTerm
Data Type : Text
These controls are inside the subform --> frmProgressNotes_Sub
And my main form is --> frmProgressNotes_Main
The function resides in the subform.
Many Thanks... Jim

CODE
    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
MikeLyons
A couple of things...
Onotice your code seems to assume the FindFirst operation will always be successful, without checking the NoMatch property.
You have added an On Error directive but the error handler will always silently fail the function. I would add at the least a MsgBox call to display the value of Err.Description -- it's quite possible your code is hitting a runtime error preventing you from getting the result you expect. If you add a way to see what the error is, that should help you troubleshoot.
Mike
BruceM
What identifies the record as the previous record? Is it a value in a field, or what exactly?
don't think passing Form as the Form argument is going to do anything useful. You could pass Me.Name or Form.Name, but even so you can't get at the recordsetclone of a form unless it is opened, AFAIK.
jimmy0305
Thanks Mike for your quick reply...
tried everything & so far, I have no idea what to do next... even tried to create a simple db to simulate it & it works perfectly fine but when I apply it to my db, it doesn't work...
jimmy0305
I got it...
used Dlookup & DMax instead.... in the (PriorTermtxt) Control Default Value ...
CODE
=Dlookup("PresentTermtxt","tblProgressNotes","[ProgressNotes_ID] = " & Dmax("ProgressNotes_ID","tblProgressNotes"))

Thanks again Mike & Bruce...
Hope somebody will find this useful...

Jim
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.