Help Printing multiselected records

[rockyiii]

Hi all;
I have a form that will show records in a table. I need to be able to
multiselect records in that table and print based on a criteria (Print only
the selected records). I need some code guidance please.

Should I use a subform in a form, or maybe a list . The backend tables
reside on SQL. I thought of a dding a check box, and tell it to print the
ones with the checked boxes only, which would be sweet. But not sure how to
implement it in a sql table since the tables do not have a check box method.
Not sure if Binary would work.

Thanks for your help

Martin.

 

[Klatuu]

Okay, here is how you do it.

First, you need to create a multi select list box that will show the items
you want to include in the report.

Then you need a command button to run the report. In the Click event of the
command button, you can build a Where string. It is should have the same
structure as an SQL WHERE statement without the word WHERE. Included below
is a function that will return a string formatted for just this purpose. All
you have to do is put the name of the field you want to filter on at the
beginning of the string. Note that when running the report, if the user
makes no selections from the list box, it assumes you want all records. Then
when it opens the report, it uses the OpenReport method's Where argument.

When you call the BuildWhereCondition function, pass the name of the List
box to it.

Private Sub cmdRunReport_Click()
Dim strFilter As String

strFilter = BuildWhereCondition("ListBoxName")

If len(strFilter) > 0 Then
strFilter = "[YourFieldName]" & strFilter
End If

Docmd.OpenReport "ReportName", , , strFilter
End Sub
**********************

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = " = """ & _
ctl.ItemData(ctl.ItemsSelected(0)) & """"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & """" & .ItemData(varItem) & """, "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[NKTower]

Since the database backend is SQL Server, I'll presume that there is a good
chance that you have a multi-user environment. If that's the case, then
having a field in the record to support the checkbox control on the form
could lead to multi-user interference.

If the user is NOT to be permitted to change data in the selected records
then I'd suggest creating a temporary table consisting of a bit-field to
handle the Yes/No, and those columns from the table that you need to display
to the user for selective printing. Be sure to initialize the bit field to 0
(false) - otherwise you get the misleading "Another user has updated the
record" error message when the user tries to set the check box. The
temporary table could be on the server, or if you aren't running an Access
Data Project (ADP) it could be on the local front end. Bind the field to a
checkbox control on your form.

 

[Michael Gramelspacher]

Since the database backend is SQL Server, I'll presume that there is a good
chance that you have a multi-user environment. If that's the case, then
having a field in the record to support the checkbox control on the form
could lead to multi-user interference.

If the user is NOT to be permitted to change data in the selected records
then I'd suggest creating a temporary table consisting of a bit-field to
handle the Yes/No, and those columns from the table that you need to display
to the user for selective printing. Be sure to initialize the bit field to 0
(false) - otherwise you get the misleading "Another user has updated the
record" error message when the user tries to set the check box. The
temporary table could be on the server, or if you aren't running an Access
Data Project (ADP) it could be on the local front end. Bind the field to a
checkbox control on your form.

This works fine for me using Access with SQL Server Express on the same machine.
I cannot speak to how it would work on a network.

Create a local table with the key column and a YesNo column. Fill the key column
with the key values from the main table and set the YesNo colun to zero.

Inner Join the small table to the main table or query Make this new query the
recordsource of the form where you want to select records to print in a report .
Create a bound checkbox on the form.

I am thinking that the form is a continuous form and shows all records so as to
check the one you want to print a report for.

Here is an image to illustrate: http://www.psci.net/gramelsp/temp/Anzeiger1.png

 

[rockyiii]

Michael;

Thanks for your reply and to all who replied as well.

I forgot to mention that the table that contains the records to be printed
is constantly changing.
I thought about creating a temp table in the front end which will house the
check box ,and link the keyfield to that, but that will be a problem as I
will have to run an update let's say device tag field with the current list
from the main table to the temp table fields on form open or something like
that.

I like your form by the way.

Rocky

 

[rockyiii]

Wanted to ask you :

Bind the field to a checkbox control on your form. (I am assuming binding
the equal fields from the main table to the one with check box table Right?
or did I misunderstood?

Rocky

 

[Michael Gramelspacher]

Wanted to ask you :

Bind the field to a checkbox control on your form. (I am assuming binding
the equal fields from the main table to the one with check box table Right?
or did I misunderstood?

Rocky

Just to be clear. The form is based on a query joining the main table and the
check table. The The checkbox on the form is bound to the checked column of the
query.

If records are continuously be added to the table, then for each record added,
the key must be insert into the checked table.