Controls to update Queries on a Form? 1

[BLSguy]

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?

 

[kjv1611]

The basics of what you want to do are:
[ol 1]
[li]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.[/li]
[li]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.[/li]
[li]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.[/li]
[/ol]


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

 

[BLSguy]

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!

 

[kjv1611]

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