none
Open Form with subform datasheet filtered by date field RRS feed

  • Question

  • Hello,

    I have a Button (cmdOpen) on a form (FormA). I have another form (FormB) that has a subform (subFormB) (continuous). Subform has a date field (dtDate)

    I'd like to use a macro with cmdOpen to open FormB with subFormB date field filtered and displays only todays records.

    -- I tried: in macro's Where Condition = [Forms]![FormB]![subFormB]![dtDate] = Date() which does displays all records.


    Any input is greatly appreciated.

    Savalan


    • Edited by Savalan Saturday, June 23, 2018 3:48 PM
    Saturday, June 23, 2018 3:42 PM

Answers

  • Why a macro?  Try to add the following lines to the open event of the subform"

    Me.Filter ="[dtDate] = #" & Date() & "#"

    Me.FilterOn=True

    Cheers,

    Vlad

    • Marked as answer by Savalan Saturday, June 23, 2018 5:17 PM
    Saturday, June 23, 2018 4:47 PM

All replies

  • Hi Savalan,

    Could you modify the query for the subform to include a criteria for the date? If you filter the query to display today's date, then all you need to do is open the form/subform. For example, the criteria under the date field could be something like:

    Date()

    Hope it helps...

    Saturday, June 23, 2018 4:45 PM
  • Why a macro?  Try to add the following lines to the open event of the subform"

    Me.Filter ="[dtDate] = #" & Date() & "#"

    Me.FilterOn=True

    Cheers,

    Vlad

    • Marked as answer by Savalan Saturday, June 23, 2018 5:17 PM
    Saturday, June 23, 2018 4:47 PM
  • Hi DBguy,

    Thanks for quick reply. 

    I had the condition in Query Designer's criteria and it was working. After form is loaded, I am also trying to filter the date so I can change the records according to date selected with a date picker in a TextBox [txtDate]. In criteria I am using:

    { Like "*" & txtDate & "*" }

    The problem is that there are like 60k records and growing. I want to only display a few of them when [FormB] with [subFormB] loads initially. 

    Saturday, June 23, 2018 5:03 PM
  • Hi Vlad,

    Thanks for reply. I will try it and let you know.

    Savalan

    Saturday, June 23, 2018 5:05 PM
  • Hi Vlad,

    It's working. I  just have to reuse this snippet in other conditions like when the TextBox cleared it does not display all the records, but just today's recs. by creating a sub and calling it when needed.

    Thank you.

    Saturday, June 23, 2018 5:16 PM