apply filter when opening form 1

[monkeysee]

I have a form based on a table that allows one to schedule appointments. I would like the form to open and only show the appointments for the current date or any future dates. I do not wish the form to show any past dates. How would this be accomplished?
Thanks in advance!

 

[dhookom]

You could change the form's record source to a query that has the date field >=Date()

The other solution is to use the Where condition of the DoCmd.OpenForm method (my preferred solution).

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

The "Where" condition is what I think I would prefer.
Could you advise me as to how the statement would read?
Thanks Again!

 

[dhookom]

You haven't provided the form or field names. Try something like:

DoCmd.OpenForm "Your Form Name",acNormal,,"[AppointmentDate]>=Date()"

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Hi Duane,
Thanks! But for some reason, it is not filtering the old dates out.
This is what I have: DoCmd.OpenForm "subform", acNormal, , "[AppointDate]>=Date()"
and I have it in the OnLoad event of the form - is this correct?.

name of my form is subform and the field name is AppointDate
Thanks again for your help!

 

[dhookom]

I always open forms by clicking a button or link from some other form. Most of the time this "some other form" is a switchboard with buttons to navigate to various forms and reports. A button on the switchboard form would have code like I suggested. There would be no code in the OnLoad event of the form "subform".

In the future you might want to try give your objects more descriptive names like "frmNewAppointments"

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

In your OnLoad event, you may try:
[tt]Me.RecordSource = "AppointmentDate >= " & Date()[/tt]
but I would rather use Duane's suggestion.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Andy,
I think your code should reference the Filter property rather than the record source. Also the FilterOn would need to be set to True.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

I Got It! Thanks for your input! What I ended up doing was putting the code: (((tblAppointments.AppointDate)>=Date()))
in the filter property of the subform. Works great!

 

[dhookom]

So the "form" with the data/records is actually a subform?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

yes, the appointment dates are in the subform. I should have mentioned that, sorry. Is it possible to have it in the button function that opens the main form?

 

[dhookom]

You have found probably the standard solution regarding the filter. I would also consider updating the Sql property of a saved query that is used as the record source of the subform.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016