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

5hadow

Member
Local time
Today, 14:54
Joined
Apr 26, 2021
Messages
84
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:
Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:54
Joined
May 7, 2009
Messages
16,810
can you use dMax()
Code:
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

Member
Local time
Today, 14:54
Joined
Apr 26, 2021
Messages
84
can you use dMax()
Code:
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:

Code:
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

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
36,871
"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.
 

Users who are viewing this thread

Top Bottom