Filter sub forms on a form by a selected date?

[mjanalyst]

Good Afternoon,

I have a new issue that I would like to know if it possible to fix and if so how.

In my database I would like to select a date on a form that is connected to a table that just records the date. I would like then to click on a button to open a form that has several subforms on it. I would like to filter those subforms as per the date I selected on the first form. I have been fooling around with several filtering methods, but am still new to access and it isn't work. Is it possiable to do what I want and if so how do I do it? Thanks for you help in advance.

Regards,

Mike

 

[VilaRestal]

Yes it surely is possible but we'd need to know the relevant tables, fields and forms to say exactly how.

I wonder about "select a date on a form that is connected to a table that just records the date". The table records the date? Any particular date? The form is bound to the table? Is the combobox bound to a field? Any reason why?

It just sounds like all that (the form and the combobox) should be unbound. If the dates listed in the combobox need to be stored in a table then ok but that doesn't mean either it or the form it's on should be bound. It just means it's rowsource is a table/query rather than a value list.

But anyway, there are various possible ways to pass this date to the second form so that its subforms will be filtered by it. Which is most appropriate depends on the details of the form and its subforms (the record sources behind them and the Master/Child relationships) but this is most likely to be the best way I think:
Pass the Date as OpenArgs to the form and then read the OpenArgs in its Load event and change each subforms recordsource to a "SELECT... WHERE date = ..."

 

[mjanalyst]

View attachment Database-example.accdb

VilaRestal,

Thank you for the response. I have uploaded a example datebase with the genral idea of what I want to do. Unlike this example when I click the filter button it will open a form with sub forms. I like your OpenArg suggestion, but I have no idea how to do that. So if you could please give me further information how to do that.

As per yoru other question, The dates in the seperate forms are not connected to eachother. In my actual database the date will filter out data on a query for a specific date that was selected. I am sorry for the confusion and if I am not making sense. Hopefully my example will help. Thanks.

Mike

 

[VilaRestal]

Ok first I'me uploading the database to do what it was trying to do: open the Names form filtered to a date selected on the dates form.

Notice I've removed the Dates table, undound the Dates form, renamed the Date textbox to Dates, set Command3 OnClick to Event Procedure and changed that procedure to work.

Next I'll upload a version that has the Names form as a subform to an unbound form and filter that using the OpenArgs method I mentioned.

 

[mjanalyst]

VilaRestal,

Thanks so much. It looks great. I am going to try and intergrate this into my other datebase. I might replay back on Monday if I have any questions. Thank you again.

Mike

 

[VilaRestal]

And here is that demo.

Note the Names subform shows the Dates field so it can be entered and if it is filtered (by opening via the Dates form) then it will set the default value of that field to the Date selected on the Dates form as well as filtering the records for that date.

Note the code opening Form2 from the Dates form: just passes the date as on OpenArgs
And the code in Form2's load event that checks for OpenArgs, checks it's a date and if so sets the recordsource of the Names subform to filter for that date and sets the default value of the Dates control to that date.

I hope that's helpful. If you have any questions don't hesitate to ask.