After moving data between 2 listboxes, using the second listbox in a query.

[Brendan]

I have two listboxes on a form which are called lstOne and lstTwo.
When the form is first loaded, lstOne contains all Categories from a
table and lstTwo is blank. I have set the list boxes so that when the
user double clicks the Category, it will go over to the other listbox.
When the user clicks the ok button i want the values in the second
listbox to be used in a query for the report.

Here lies the problem:
When trying to access Forms!frm_Category!lstTwo or lstTwo.value or
lstTwo.Column(0) I get an error saying that it is null. However,
since vbcode is used as the rowsource, I can do
Forms!frm_Category!lstTwo.RowSource and I get the following: Select
ID, Category from Category Where Category in (8,6,7) Order By Category
Asc.

Obviously, I had selected three values from the first listbox in this
instance, how would I go about parsing the RowSource so that I just
had the values 8,6,7?

Thanks

 

[Andrew Smith]

You haven't said how you have accomplished the trick of moving items from
one list box to the other. I have a method for doing this which makes
creating the report simple:

- the second list box has a table as its row source*.
- the first list box has a "find unmatched" query as its row source - that
is it is based on a query that selects all the rows in the main table that
do not have entries in the table used as the source for the second list box.
- to move something from the first list box to the second you just have to
append a row to the table of the second list box and requery both list boxes
- to move something the other way you just have to delete it from the second
list box.
- to create a report on selected items just use the second table and the
main table in a query and use that as the data source for the report.

* Actually this is not quite correct. I use a single field table of primary
key values and base the list box on a query that selects the fields that I'm
interested in from the main table.

I hope this makes sense!

 

[Bob Quintal]

(e-mail address removed) (Brendan) wrote in

I have two listboxes on a form which are called lstOne and
lstTwo. When the form is first loaded, lstOne contains all
Categories from a table and lstTwo is blank. I have set the
list boxes so that when the user double clicks the Category,
it will go over to the other listbox.
When the user clicks the ok button i want the values in the
second
listbox to be used in a query for the report.

Here lies the problem:
When trying to access Forms!frm_Category!lstTwo or
lstTwo.value or lstTwo.Column(0) I get an error saying that it
is null. However, since vbcode is used as the rowsource, I
can do Forms!frm_Category!lstTwo.RowSource and I get the
following: Select ID, Category from Category Where Category
in (8,6,7) Order By Category Asc.

Obviously, I had selected three values from the first listbox
in this instance, how would I go about parsing the RowSource
so that I just had the values 8,6,7?

Thanks

Passing the results of a listbox directly to a query is a PITA.

What is much easier is to open the report with a where clause.

In the ok button that opens the report, you need to generate the
clause that filters the report, then open the report with that
clause.

DoCmd.OpenReport stDocName, acPreview,,StWhereClause

Can you figure out code to generate "Category in (8,6,7)"
and assign that to StWhereClause?

Bob Q

 

[Brendan]

Passing the results of a listbox directly to a query is a PITA.

What is much easier is to open the report with a where clause.

In the ok button that opens the report, you need to generate the
clause that filters the report, then open the report with that
clause.

DoCmd.OpenReport stDocName, acPreview,,StWhereClause

Can you figure out code to generate "Category in (8,6,7)"
and assign that to StWhereClause?

Bob Q

Bob Q,

I am trying to retrieve the Category in (8,6,7) or even just retrieve
the 8,6,7 basically all values between the parens and that is where I
am running into trouble. Do you have any ideas on how I could parse
the string and return only the values between ( and )?

 

[Bob Quintal]

(e-mail address removed) (Brendan) wrote in

Bob Q,

I am trying to retrieve the Category in (8,6,7) or even just
retrieve the 8,6,7 basically all values between the parens and
that is where I am running into trouble. Do you have any
ideas on how I could parse the string and return only the
values between ( and )?

You have 6,8,7 selected in the listbox #2. you need to loop
through the listbox_2.itemsselected

Dim ctl As Control
Dim varItm As Variant
Dim sInlist as string

Set ctl = listbox_2
For Each varItm In ctl.ItemsSelected
'version for numeric values
sinlist = sinlist & varitm & ","
' optional version if varItem is not numeric
sinlist = sinlist & "'" & varitm & "',"
Next varItm
'remove the last comma.
sinlist = left(sinlist,len(sinlist)-1)

stwhereclause = "IN (" & sinlist & ")"

Bob