INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

apply filter when opening form

apply filter when opening form

(OP)
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!

RE: apply filter when opening form

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

RE: apply filter when opening form

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

RE: apply filter when opening form

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

CODE --> vba

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

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

RE: apply filter when opening form

(OP)
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!

RE: apply filter when opening form

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

RE: apply filter when opening form

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: apply filter when opening form

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

RE: apply filter when opening form

(OP)
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!

RE: apply filter when opening form

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

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

RE: apply filter when opening form

(OP)
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?

RE: apply filter when opening form

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close