Multi-selection List Box 1

[rafal444]

I would like to have a way to select multiple Products from the list. How do I make the report to show only records selected in the list box?

 

[ChanceD2]

This is definitely a multipart answer.

1. You need to put a multi-selectable listbox on your form.
2. You need to make sure that the BOUND column is set to the correct column (i.e. – the ID field in the table)
3. You need to create a report in which the SQL statement has a WHERE statement that is bound to the field you link the column (in step 2) to.
4. You need to create a form or a way for the user to click a button that will execute the report.

OK, so try it out and let me/us know what number(s) you need help with.

HTH

C-D2

 

[PHV]

Anf have a look at the ItemsSelected collection of a multiselect ListBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya rafal444 . . .

The following idea uses a subform (subform can be made to look just like a listbox) and two buttons instead:
[ol][li]Add a [blue]Checkbox[/blue] (yes/no field) to the [blue]Products[/blue] table. We'll call the checkbox [purple]PrtSel[/purple][/li]
[li]Set a recordsource for the subform that includes [purple]PrtSel[/purple]. This will allow you to go down the list and [blue]simply checkoff what you want printed![/blue][/li]
[li]One button is for [blue]printing the report[/blue] which is based on a query that includes all fields of interest and criteria based on [purple]PrtSel[/purple] = True.[/li]
[li]The other button is to [blue]clear all selections[/blue] so you can start over.[/li][/ol]
The above can be done with a listbox but with the extra step of the selections updating the checkboxes in the table.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[DrillMonkey]

Hi AceMan1....

I like your suggestion on the check boxes, I used it on one of my forms, works great! But I need help setting up the button to clear the checks. Thanks

 

[TheAceMan1]

DrillMonkey . . .

[blue]An Update SQL[/blue] should do the trick . . .

In the [blue]Click[/blue] event of the button, copy/paste the following:

[blue]   DoCmd.RunSQL "UPDATE [purple][b]ProductsTableName[/b][/purple] SET [PrtSel] = False;"
   Me.Requery[/blue]

See Ya! . . . . . .

 

[DrillMonkey]

You are the Man!! many thanks

 

[DrillMonkey]

TheAceMan1

Is there a way to suppress the "Warning message" Microsoft Access You are about to update row(s).

 

[TheAceMan1]

DrillMonkey . . .

Certainly is and permanent! . . .

From the menubar - [blue]Tools[/blue] - [blue]Options[/blue] - [blue]Edit/Find Tab[/blue] - [blue]Confirm Section[/blue] - uncheck [purple]Action queries![/purple]

See Ya! . . . . . .

 

[danvlas]

It's not a good idea in a multi-user environment, as it would be a 'server-wide' selection. One user makes a selection, another makes another selection and both open the report at the same time. What will earch user get?
What if the first user opens the report after the second has cleared the checkboxes?

ItemsSelected and a In clause would be a proper way to solve this, plus the WhereCondition argument of OpenReport


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

danvlas . . .

in that case try:

[blue]   DoCmd.[purple][b]SetWarnings[/b][/purple] False
   DoCmd.RunSQL "UPDATE ProductsTableName SET [PrtSel] = False;"
   DoCmd.[purple][b]SetWarnings[/b][/purple] True
   Me.Requery[/blue]

See Ya! . . . . . .

 

[danvlas]

Sorry, TheAceman1, but I don't see the connection...
What I meant was that when a table is updated, all users will be provided with the same data, not only the one who made the selection. And the result will be either cumulative or nothing, depending on whether the report is opened before or after clearing the selection.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

danvlas . . .

Agree with you and yes . . . I understand. However there's no indication that were dealing with a multiuser envrionment! I would've expected [blue]DrillMonkey[/blue] to mention this.

In any case, if it turns out to be multiuser why not offer your version of code . . .

See Ya! . . . . . .

 

[DrillMonkey]

Hi Ace, Danvlas

This could turn into a multi-user app down the road so I wouldn't mind having the additional code for my tool box.
thanks.

 

[danvlas]

Sure,

Suppose the listbox has the values of the product ID(ie the rowsource of it contains the ID field and it is also the bound column) - reference: Chance-D2.
First step would be to build a string containing the selected values, from the ItemsSelected collection - reference: PHV:

Dim strFltr As String
Dim itmItem
'Make sure there is something selected
If Me("ListBoxName").ItemsSelected.Count>0 Then
    For Each itmItem in Me("ListBoxName").ItemsSelected
        strFltr=strFltr & Me("ListBoxName").ItemData(itmItem) & ","
    Next
    strFltr="[IDFieldName] In(" & Left(strFltr,Len(strFltr)-1) & ")"
End If
'The result should look like: [IDFieldName] In(7,22,1975)

Now, there may be 2 choices:

1. The report is based on a query that contains the corresponding ProductID field, either the PK from the Products table or the FK from a related table. I'm almost sure this is the case.
Then:

DoCmd.OpenReport "ReportName", acViewPreview, , strFltr

Note that if there is no selection made in the listbox, this will open the report for ALL records
[/code]

2. The report is based on a groups/totals query that does NOT contain the ProductID field.
This is a little more complicated, because the basic structure of SQL requires the Where clause to be defined BEFORE the Group By clause:
Select Field1, Field2
From TableName
Where 1=1
Group By Field1, Field2
Having Field1=25;
Therefore, you need to 'inject' the Where Clause into the SQL definition, and in this case I would like to find out if you use a query (and what's its SQL) or directly an SQL statement as recordsource of the report.
It would be also nice to know what Access version you use.

In any case, user filtering should be client-side built and processed, this way any user will be independant from others.
SQL Server provides the notion of 'temporary tables', but there is no such thing in Access.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html