Set textbox to be a multiple criteria calculated field, for current recordset. (1 Viewer)

[5hadow]

Good day everyone

I have a textbox "fldDocReviewDate"
First variable is subform "subfrmWIAudit" field "fldAuditTypeID"
Second variable is subform "subfrmWIAudit" field "fldAuditDate"
Third variable is +365 days

I want my textbox "fldDocReviewDate" to do this:
1. Locate the latest record in my subform "subfrmWIAudit" (based on a table: tblWIAudit) where "fldAuditTypeID" = 3
2. Check the date of that record in "fldAuditDate" and add +365 days to it.

This is what I have so far:

Private Sub Form_Load()
    Dim DueDate As String
    DueDate = DLast("[fldauditdate]", "tblwiaudit", "fldaudittypeid= 3")
    Me.fldDocReviewDate.Value = DueDate
End Sub

Problem is that it takes latest "3" record from my subform/table from all records, not the one associated to current record set. How to make it target the current recordset?
subform link is: Link master field: fldDocID
Link child field: tblWI.fldDocID

 

[arnelgp]

can you use dMax()

Private Sub Form_Load()
    Dim DueDate As String
    DueDate = DMax("[fldauditdate]", "tblwiaudit", "fldaudittypeid= 3 And fldDocID = " & Me!fldDocID)
    Me.fldDocReviewDate.Value = DueDate
End Sub

you can also try moving the code to the form's Current event?

 

[5hadow]

can you use dMax()

Private Sub Form_Load()
    Dim DueDate As String
    DueDate = DMax("[fldauditdate]", "tblwiaudit", "fldaudittypeid= 3 And fldDocID = " & Me!fldDocID)
    Me.fldDocReviewDate.Value = DueDate
End Sub

you can also try moving the code to the form's Current event?

Great!
Thanks.
Now, here's what I've got so far:

Private Sub Form_Load()
    Dim DueDate As String
    DueDate = DateAdd("yyyy", 1, DMax("[fldauditdate]", "tblwiaudit", "fldaudittypeid= 3 And fldDocID = " & Me!fldDocID))
    Me.fldDocReviewDate.Value = DueDate
End Sub

 

[Pat Hartman]

"Last" and "First" do not have the meaning you think. They refer to position in the recordset which also might not be what you expect if the recordset doesn't have an order by clause.

Assuming you want the highest value rather than the "last" value, DMax() is what you want as arnel suggested.