Set textbox to be a multiple criteria calculated field, for current recordset.

[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