Printing report based on selecting individual records

[blakey]

Hi

I have table that holds all payments. I need to be able to print a report for a group of payments. I do have a common value to select on eg date or supplier but rather need to select random individuals. (not random in the true sense of the word)

What I am considering is a screen that would display, say supplier key, payment date, payment amount etc for all records and then have a tick box that I could click on. The records thus marked would then be printed.

Any suggestions please

 

[ghubbell]

Your form must be unbound.
Start with your basic criteria via combos or simple fields. They will provide parameters in your reports query, like:
SupplierKey = Forms!YourFormsName!SupplierKey...

Unless you are familiar with arrays, add a field to your table of invoice? records named "selected" (True/False) default value = 0 (False).
Make ListBox1 based on a query where invoice.selected=False
Right beside it make ListBox2 based on a query where invoice.selected=True
Make Qry1 an Update query that updates invoice.selected to True where invoiceNo = Forms!YourFormsName!ListBox1
Make Qry2 an Update query that updates invoice.selected to False where invoiceNo = Forms!YourFormsName!ListBox2
Make Qry3 another query that updates every invoice.selected to False.
Running any query thru code add DoCmd.SetWarnings False before and DoCmd.SetWarnings True after.
On DoubleClick of ListBox1:
DoCmd.openQuery "Qry1"
Me.ListBox1.Requery
Me.ListBox2.Requery
On Doubleclick of ListBox2:
DoCmd.openQuery "Qry2"
Me.ListBox1.Requery
Me.ListBox2.Requery
On Form Unload:
DoCmd.openQuery "Qry3"
*The form must remain open while you do this so it should be a sizeable popup. When you click your Preview or Print Command add first:
Docmd.minimize
Try This and you'll see what happens. Base your report on a query with criteria like Where Invoice.selected=True and anything else from the form.
This is really a lot easier than it looks. You could add the same method to any other table that may contain multiple choices required and again add criteria to your reports query.
Spiff it up by adding to the reports On Close event (with essential error handling if you don't use an IsLoaded or IsOpen function):
Forms!TheNameOfYourForm.SetFocus
DoCmd.Restore

The only danger here is in multiuser environments where more than one person could be running this at the same time. If this is your case advise and I'll show you how to lock out others while this is in use. I have use this method to gradually filter criteria thru about 60 tables step by step using lots of queries and only 2 listboxes!





Gord
ghubbell@total.net

 

[rafe]

Blakey,

I’ve done this too as a main form in our DB. I’ve done things a little differently than Gord not necessarily better (can’t emphasize that enuf) but it gives you some more options.

I use just one list box & my where clause goes into the report calling function. So there is no flagging in the record itself. Please note that I started with a tickbox flag etc. but ran into problems when I added a second user to the DB.

I used a “simple” multi-select list box with multiple columns. In your case the row source for the list box would be something like…
SELECT supplier_key, payment date, payment, amount FROM myTable;

Set the column widths appropriately in the format of the list box.

Then I have a button that cycles thru all of the selected rows in the list box & builds up the “WHERE”-clause of the report…

Private Sub OKButton_Click()
Dim DocList As String 'I'm selecting documents
Dim SelectItem As Variant

If Me.SelectList.ItemsSelected.Count = 0 Then
MsgBox "You selected no items to process."
GoTo OKButton_ClickFail
End If

DocList = ""
For Each SelectItem In Me.SelectList.ItemsSelected
If DocList <> &quot;&quot; Then DocList = DocList & &quot; OR &quot;
DocList = DocList & &quot;[DocID]=&quot; _
& Me.SelectList.Column(0, SelectItem)
Next

DoCmd.OpenReport &quot;MyReportName&quot;, , , DocList

OKButton_ClickFail:
CancelButton_Click
End Sub

I also have a select all button & deselect all button & a cancel button

Private Sub CancelButton_Click()
DeselectAllButton_Click
Me.SelectList.Requery 'because i also process data (not seen in code snippets)
End Sub

Private Sub SelectAllButton_Click()
Dim i As Integer
For i = 1 To Me.SelectList.ListCount - 1
Me.SelectList.Selected(i) = True
Next
End Sub

Private Sub DeselectAllButton_Click()
Dim SelectItem As Variant
For Each SelectItem In Me.SelectList.ItemsSelected
Me.SelectList.Selected(SelectItem) = False
Next
End Sub

Hope all goes well

rafe