somewhat advanced form query (need advice)

[mbatestblrock]

I think I have a rather advanced question that I was hoping to find some good help with. I am still pretty new to VBA and I know that doesn't help my situation here. But here is what I am trying to accomplish.

I have a relatively simple database. The main points are which I have a Form with a sub form in it. This form is a customer form and the main part of the form has their name address and phone number. The sub form has sales information. Date, Price, Product, Paid Amount, Balance, and status (status is open or paid)

So pretty simple..

What I want (Ideally) is to have a form I could open and it would query which customers have Open status. I can accomplish this much, as it is quite easy.. but I want to query it by date as well. So the form would come up and have a date input (not like the parameter query, which only lets you manually input date instead of using calendar) start date and end date. so it would query any Status of open from those dates. Then what I also couldn't even begin to figure out is: I want each result it brings back to have a check box next to it.. I want to be able to check which ever ones I desire then have a button that well send only the selected to a report template.


The only solutions I have come up with this far is to pop open a query with all open status and have the parameter query prompt me for dates. But the only downfall is I cannot select the ones I want to send to the report, it will only send all. I would love to have the check boxes but have no idea where to start.

I tried searching google to no avail before posting... may the Gods of access PLEASE help me out!

Thanks everyone in advance

[jyoung2]

In regaurd to the Check box, You can't have an unbound checkbox in a continuous form. What I have done in the past is create a tempary table with a yes/no(bit) field in it. Change your query to an append query and append that table. You will need to change your subform so that it calls the temptable instead of the query
Then you can add the check mark field to the form. The Report would then be based on a query from the temp table where the checks(bit field) are true.

You will want to call the delete and make table from a button

docmd.setwarnings false
docmd.openquery "YourDeleteTempTableQryHere"
docmd.openquery "YourMakeTempTableQryHere"
docmd.setwarnings true
me.fsubyour sub Form.requery   'requry your subform so the new data shows.

For the Date you can place a start date and an end date on the form. In the query you can call the the date fields on the form.

between [Forms]![your Form Name]![DateStart] and [Forms]![your Form Name]![DateStop]