Multi-Select LIstbox - Am I missing the obvious? 1

[NKA]

(Access 97)I have a listbox for parts and would like to be able to open a report based on the selected part(s) and for the life of me cannot do it!

I was hoping to have a command button which would open a report based on the selected items.

I have tried looking at several pieces of code people have posted on here - but just can't get them to work - where do I attach the code and how does it open my report?

I assume my report has to be based on a query with the criteria set to read the listbox selection?

Please can anyone help - it's driving me mad!!!

NKA

The answer's always easy - if you know it! ;-)

 

[DreamerZ]

You're on the right track.

If you're using a button, you would attach the code to OnClick property of the button. Select [Event Procedure], click the ... to go into the coding section and add code there.

What you want to do is create your query and report without any criteria for the parts field. At the beginning of the OnClick code for the button, set your criteria for the query. Then open the report (docmd.OpenReport...)

You can if you want, use the RunSQL and create the query in code. If you have a lot of fields, however, that could get cumbersome.

Hope that helps.


DreamerZ
simplesolutions04@sbcglobal.net
[ignore]

http://www.geocities.com/simplesolutions04@sbcglobal.net

[/ignore]

 

[NKA]

Still confused!

This is the code I picked up from the FAQ's (adapted for my table/queries/forms etc) and put it in the On Click of my button.

[blue]
'run query based on selected item(s) in list box

Dim ctlList
Dim sSql As String

Set ctlList = Me.LST_Location
'****************************
sSql = "select * from TBL_TempStockTrans where Stock_code in('"

For Each lmnt In ctlList.ItemsSelected
sSql = sSql & ctlList.ItemData(lmnt) & "','"
Next

sSql = Left(sSql, Len(sSql) - 3) & ")" 'remove last comma and single quote and add closing bracket
'***************************
DoCmd.RunSQL sSql[/blue]

What now? Have a query behind my report? Shouldn't I somewhere tell this button to open the report? At the moment, nothing happens (of course it won't)!

arrrrrrgh!



NKA

The answer's always easy - if you know it! ;-)

 

[matethreat]

Hello,

I just finished doing something very similar to you.

What you first want to do is create a query with the information you want to collect.

In the Criteria row, for the fields you want filtered by the information in the form you need to enter:

[Forms]![YourFormName]![YourComboBoxName]

Then you want to create a report that uses that query for it's information.

Then on the form you want to add a Command Button. Select Report Operation - Preview Report.

Now when the button is pressed it will run the query based on the information selected in your form. You should not need to write any code to do this.

Hope This Helps,

Let me know

 

[NKA]

Hi matethreat,

Does this work for multiple selections in a Listbox? You mention combo box - that is only singular selection.

I am about 75% there - just gotta work out how to open the damned report with the multiple selections!!

Thanks for your post.

NKA

The answer's always easy - if you know it! ;-)

 

[NKA]

I have added the line

[blue]DoCmd.OpenReport "RPT_StockMovement", acViewPreview[/blue]

before this code

[blue]'run query based on selected item(s) in list box

Dim ctlList
Dim sSql As String
Dim Lmnt As Variant


Set ctlList = Me.LST_Location
'****************************
sSql = "select * from TBL_TempStockTrans where Stock_code in('"

For Each Lmnt In ctlList.ItemsSelected
sSql = sSql & ctlList.ItemData(Lmnt) & "','"
Next

'remove last comma and single quote and add closing bracket
sSql = Left(sSql, Len(sSql) - 3) & ")"
'***************************[/blue]
[red]DoCmd.RunSQL sSql[/red]

Now, when I click the button, the code runs OK up until the last line (highlighted in red) then I get the following error message

------------------------------------------------------------------------------------------------------
Run-time error '2342':
A RunSQL action requires an argument consisting of an SQL statement

For example, an action query that appends records starts with INSERT INTO. A data-definition query that creates a table starts with CREATE TABLE.
------------------------------------------------------------------------------------------------------

So I am getting somewhere...

I have a feeling I need some sort of filter on my report too - at the moment (even though the code is falling over at the last hurdle), it is opening my report with EVERYTHING in it.

Any more suggestions would be great!

NKA

The answer's always easy - if you know it! ;-)

 

[matethreat]

I have never used multiple selections in a list box, I didn't know that it was possible.

But, I guess another solution would be to have multiple list boxes that look up the same information.

then in the query for that item you could put:

Between[Forms]![YourFormName]![YourListBoxName1]and [Forms]![YourFormName]![YourListBoxName2]

or if you are just looking for 2 records just eliminate the "Between" command.

 

[RoyVidar]

Hi!

Some alterations to the existing code. Just putting together the "where condition" of the report (a valid sql where clause without the keyword Where). Try something like this:

[tt]Set ctlList = Me.LST_Location
sSql = "Stock_code in ('"
For Each Lmnt In ctlList.ItemsSelected
sSql = sSql & ctlList.ItemData(Lmnt) & "','"
Next Lmnt
sSql = Left(sSql, Len(sSql) - 3) & ")"
DoCmd.OpenReport "RPT_StockMovement", acViewPreview,,sSql[/tt]

- the report would have to be based on the TBL_TempStockTrans table
- no need to run the query

(and I didn't bother repeating your variable declarations;-)).

Roy-Vidar

 

[NKA]

Roy!

You absolute beauty! Works a treat - star for you!

(So much hair loss is not good for my image I can tell ya!)

Cheers

NKA

The answer's always easy - if you know it! ;-)

 

[NKA]

It's me again...

I am trying to put some sort of validation on the form now and want a message box to appear if nothing is selected from the listbox.

This is the code I have at the moment (which is positioned after the SQL has looked at the records selected - think it could be in the wrong place):

[blue]If IsNull(Lmnt) Then
Beep
MsgBox "You must select an item from the list to run the report", vbExclamation, "No Data Selected"
Exit Sub
Else
DoCmd.OpenReport "RPT_StockMovement_TEST", acViewPreview, , sSql
End If[/blue]

If I select something - sweet as... if I don't select anything, I am getting the error message In operator without () in query expression '{Loaction in ))'.

Any clues? I have tried looking at Me.LST_Location (the listbox itself) and the same thing.

Cheers for any help - you guys/gals are just the biz!

NKA

The answer's always easy - if you know it! ;-)

 

[RoyVidar]

Haven't used multiselect listboxes, so others might perhaps have more elegant solutions, but at least this one works (assigning the "startvalue" of the sql string after the loop, testing for the length):

[tt]Set ctlList = Me.LST_Location
For Each Lmnt In ctlList.ItemsSelected
sSql = sSql & ctlList.ItemData(Lmnt) & "','"
Next Lmnt
if len(sSql)>0 then
sSql = "Stock_code in ('" & sSql
sSql = Left(sSql, Len(sSql) - 3) & ")"
DoCmd.OpenReport "RPT_StockMovement", acViewPreview,,sSql
else
MsgBox "You must select an item from the list to run the report", vbExclamation, "No Data Selected"
end if[/tt]

Roy-Vidar

 

[tizwaz]

Hi there - I am also trying to do something like this but have a couple of questions. The table I need to base my report on contains a company code but I would want tthe company name (from another table in my list box) At present I have my report based on a query and have 2 combo boxes do select either all companies or 1 company and all categories or 1 category. However my users would like the opportunity to select either all, one or a number or companies and/or categories.
Your code sounds like it will get me someway towards what I need but how would I need to modify it

 

[NKA]

Thanks Roy! It's working a dream now.

NKA

The answer's always easy - if you know it! ;-)