Allowing users to select records arbitrarily to include in a report

[WH2O]

Hi Everyone,

Here is what I'm trying to figure out how to do...

Currently: Via a form, a user generates a report from a query against the database (the user selects a date range and 1 or 2 other criteria).

Required: The users want to be able to decide which records from the list generated above to include in their report -- the selection would be arbitrary so they would need to see a list of the records from the first query, and then select via a check box (or something like that) which ones they want in their report.

In my struggle to figure out how to do this, I can't help but think I'm missing something fairly obvious and am hoping that someone can tell me what it is.

Is this something relatively easy to do? Or, as someone who isn't a programmer by trade (although I have in the past been a hobby programmer, and know a little sql) is it something that may be more trouble than it's worth?

Thank you for any assistance that you can provide!

W-H20

 

[ajdesalvo]

Create a temp report table that has all of the same fields as the recordsource for the report, add one more field called Include as a Yes/No field.

Append all the records from the criteria the user selects into the temp table. Then the user can scroll through the records and check the records for the report.

This temp table becomes the recordsource for the report;

Select * from [TempTable] WHERE [Include] = -1 will report only the selected records.

On the close of the report you can delete all the temp records so the next query can run and populate it with the new queried records. Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[WH2O]

Thanks! I think this has gotten me on the right track

So if I understand the procedure:

1. I need to create FORM 1 in which the user selects the broader criteria that causes a MAKE TABLE query to generate TEMPTABLE.

2. I need a form that allows the user to select which records from TEMPTABLE to include

3. I need a report that prints records where the include field = YES (which can be done in another query)

4. I need a macro [?] that empties the contents of temptable once the report is closed.

Do I have this right?

 

[ajdesalvo]

Yes, you do!

Instead of a macro, either in the OnClose event of the report, or on the criteria form, place code that clears the temo table:

DoCmd.Setwarning False
DoCmd.RunSql "Delete * FROM [TempTable];"
DoCmd.SetWarning True

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[danvlas]

Another way to skin this cat:

Create a list box based on the query that returns the records (Set the bound column to the record ID).
Set the MultiSelect property to Simple.
Before opening the report, build a filter, something like

Sub YourButton_Click()
dim myfilter as string
dim itm
myfilter= "ReportIDField In (
For Each itm In ListBoxName.ItemsSelected
myfilter = Myfilter & itm.ItemData(itm) & ", "
Next
'remove the trailing comma and space
myfilter = left(myfilter, Len(myfilter)-2) & &quot"
'open the report now
DoCmd.OpenReport "repName", acViewPreview, , myfilter
End Sub

No need for 'make-trouble' queries, no need to delete anything...

Good luck


Daniel Vlas
Systems Consultant