Changing Query Criteria from Form

[dvgef2]

Good Evening Everyone,

I have a problem that I hope somebody can lead me in the right direction. I have tried to research this but it seems the issues that I read which may pertain to my problem got technical to the point that I could not follow. Anyway, I use to have a form that worked off of a parameter query where it would ask which month you want the records for and the user would put in a month number from 1-12 and then once entered the records for that month would be viewable on that form.

That worked fine until the user did not want to have to type in the number anymore but preferred to have it open up to the records of the current month. So I changed the parameter to 'Month(Now()) which works exactly how he wants it. He opens the form and it populates with all the records for the current month. Now I am trying to figure out how I can use a command button that when it is clicked will ask which month you want to view and then you would put in the month number again. Seems to be hard for me to figure this out. I tried playing around with code using the requery but I am probably not using it correctly. I think I need to somehow change that Month(Now()) that is in the query but I don't know if that can be done from the command button. If anyone has come across this type of problem before I would appreciate any suggestions you could offer. Thanks in advance!



Dave

[alansidman]

This can be done by using two command buttons. Each linked to a separate query to run. Copy the first query and paste it and rename it. Change the criteria to a parameter as it was in the original scenario before changing it to Month(Now()).

[dvgef2]

Thanks for the response Alan, I don't think that will work unless I am missing something. I guess I should have put in more information but I have several subforms in the main form and they all point to a single date control on the main form for opening and in addition to that, each subform has its own query. I actually tried to just make double of every query and form but that was a fiasco. Even though I know better, Access seemed to get confused and would try to open the wrong form/query no matter how many times I tried to get that right. I was getting all kinds of parameter requests for the wrong form...it was ugly. I must have done something wrong there and I may retry it but I was hoping there was a better way. It would be nice if there was a way to run the same form and query but feed the main query different criteria to run with. That way all the forms and queries would stay the same. Anyway, thanks again for your assistance.

Dave

[June7]

I don't use dynamic parameters in queries. I use WHERE CONDITION of DoCmd.OpenForm (or OpenReport) to open forms/reports with filtered dataset. Here is one example of using code to build filter criteria http://allenbrowne.com/ser-62code.html

[dvgef2]

Thanks June7,

I have to leave town but I will try this as soon as I am back. I may need to bother you a little to help me get started. Thanks again for your time!

Dave