Using a Date filtered value from a SubForm on the MainForm

Question

I am trying to get the last entry of an allready filtered SubForm and place that single value on the MainForm. The entry should also be between a Date_0 and Date_1 alltough Date_1 is not allways specified (in that case simply get the last entry from the SubForm). So you get a more graphical idea (this is a simplification of the real Form):

MainForm:

Date_0	Date_1	ValueToGet
2020/12/23	2021/02/27	Value from Subform

SubForm:

Dates	Values
2020/12/20	1200
2020/12/23	1189
2021/01/25	1173
2021/02/20	1165
2021/03/12	1333

The value to collect from the SubForm would be, in this example, the fourth entry (between Date_0 and Date_1, and last entry in that Date range). The value is then stored in a txtBox in the MainForm as the Form Loads. I know the solution is probably setting a SQL filter on the txtBox, but I do not know how to do this. Any help would be greatly appreciated. Thanks in advance!

Answer 1

You can try below sub-

Private Sub cmdGetResult_Click()
Dim strFilter As String
Dim rs As DAO.Recordset
    
    Me.Refresh
    
    strFilter = "[Dates] BETWEEN #" & Me.Date_0 & "# AND #" & Me.Date_1 & "#"

    Forms![Form1]![subformTest].Form.Filter = strFilter
    Forms![Form1]![subformTest].Form.FilterOn = True
    
    Set rs = Me.subformTest.Form.RecordsetClone
    
    rs.MoveLast
    Me.txtValueToGet = rs!values
    
    Forms![Form1]![subformTest].Form.FilterOn = False
    Set rs = Nothing
    
End Sub