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

Controls to update Queries on a Form?

Controls to update Queries on a Form?

(OP)
Hello!

I'm trying to make a form with either button controls or a combo box that will allow a user to select a month and then display the results of a query on the form. I've tried using sub forms and list boxes but can't seem to figure it out, nor can I find by way of Google a tutorial to help me accomplish this. Any suggestions?

RE: Controls to update Queries on a Form?

The basics of what you want to do are:
  1. Whether you use Combo or List box is probably preference. If it were me, I'd probably go with a Combo box, but in your scenario, I can see where a list box would work possibly better for usability.
  2. Have your query reference the listbox value. So it'll be something like =MyForm!MyListBox.Value in the month column or if you don't have that, the easiest fix, I think, will be to add a month column for easy to compare to. Another choice here would be to build a calculation something like a calculated field that says MonthFilter:Month(MyCalendarDate), and then put the where criteria in that field (assuming you're using Access query design view.
  3. Back on the form, you can use the _AfterUpdate event of the ListBox to try a requery or refresh. If that doesn't work, there are other options.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Controls to update Queries on a Form?

(OP)
Those were all excellent ideas, and I think I know enough to actually employ two of them. However, for purely aesthetic purposes, is there a way I could set each of 12 button controls to requery but with the where condition or the filter set to a month dictated by each individual button? Thank you very much for your help!

RE: Controls to update Queries on a Form?

Sure. I would probably just create a public string variable to contain the code (or do it the easy way and create a Query object in Access and call it "Template" or whatever), then reference the variable or template query in each button's code, changing the template SQL to what you want, and apply that to the query behind the combo box.

In more detail, I'd probably create a public procedure/function as well, and have it called by each button, maybe feeding it the query template name, and whatever change you want, so you have less total code... especially with 12 buttons.

I know - still not a lot of detail, but hopefully you get the idea.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

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