I need to use a List Box that a user is capable of selecting multiple 
selections and then pass those selections to a crosstab query.  I create a 
form and a list box on this form then in the Xtab query, I tried to 
reference that List box under the criteria and it won't work.  What am i 
doing wrong????

I don't know what your process is to get the values into a crosstab (not 
knowing what your code is) but here is how you can extract what has been 
selected from the list box.

I am assuming the list box is allowing you to select multiple items (this 
is a property setting if it is not). To gather the information from the 
listbox of the selected items you need to do something on this order:

Dim intListCt as Integer            
dim strItem as String

For intListCt = 0 To Forms!frmName!lstListBoxName.ListCount - 1
   If Forms!frmName!lstListBoxName.Selected(intListCt) = True Then
       strItem=Forms!frmName!lstListBoxName.Column(0, intListCt)
       'Item is selected and the first column value is what we want
       ...code used to extract or store the value of the selected item
   End If
Next intListCt

Hope this helps,

Amy

> I need to use a List Box that a user is capable of selecting multiple 
selections and then pass those selections to a crosstab query.  I create a 
form and a list box on this form then in the Xtab query, I tried to 
reference that List box under the criteria and it won't work.  What am i 
doing wrong????

Does this code put a comma between the entries?  

How then do you use the strItem in the query criteria?

I am also having problems creating a report based on this crosstab query.  I want it to be 
dynamic.  I don't want to have fixed columns.

> I don't know what your process is to get the values into a crosstab (not 
k> nowing what your code is) but here is how you can extract what has been 
s> elected from the list box.

> I am assuming the list box is allowing you to select multiple items (this 
i> s a property setting if it is not). To gather the information from the 
l> istbox of the selected items you need to do something on this order:

> Dim intListCt as Integer            
d> im strItem as String

> For intListCt = 0 To Forms!frmName!lstListBoxName.ListCount - 1
 >   If Forms!frmName!lstListBoxName.Selected(intListCt) = True Then
 >       strItem=Forms!frmName!lstListBoxName.Column(0, intListCt)
 >       'Item is selected and the first column value is what we want
 >       ...code used to extract or store the value of the selected item
 >   End If
N> ext intListCt

> Hope this helps,

> Amy

> > I need to use a List Box that a user is capable of selecting multiple 
s> elections and then pass those selections to a crosstab query.  I create 
a 
f> orm and a list box on this form then in the Xtab query, I tried to 
r> eference that List box under the criteria and it won't work.  What am i 
d> oing wrong????

> 

--0-556799843-1025189110=:93417
Content-Type: text/plain; charset=us-ascii


 When multiple items are selected in a list box, the list box doesn't have a value but a collection of values. You will need to
iterate thru your selected items and build a variable to be used in your query. One way I do it is to use the ItemsSelected property
of the list box. Access has an example of this...
Sub AllSelectedData()
 Dim frm As Form, ctl As Control
 Dim varItm As Variant, intI As Integer
 Set frm = Forms!Contacts
 Set ctl = frm!Names
 For Each varItm In ctl.ItemsSelected
  For intI = 0 To ctl.ColumnCount - 1
   Msgbox ctl.Column(intI, varItm)
  Next intI
  Debug.Print
 Next varItm
End Sub
In this example you should get a messgae box showing each item selected. You could modify this to build a string of criteria in a
variable, then set an invisible text box equal to this variable and read that from your query. I'm sure others may more examples or
paths for you. I hope this helps. Let me know if you want more explanation. I think I have an demo of how this works that I could
send you also.
 
  Pam McMillin <pmcmillin@m...> wrote: I need to use a List Box that a user is capable of selecting multiple 
selections and then pass those selections to a crosstab query. I create a 
form and a list box on this form then in the Xtab query, I tried to 
reference that List box under the criteria and it won't work. What am i 
doing wrong????




Lonnie Johnson 
ProDev, Builders of MS Access Databases
http://www.galaxymall.com/software/PRODEV 




---------------------------------
Do You Yahoo!?
Sign-up for Video Highlights of 2002 FIFA World Cup

I did not include that part because I was not sure how you were creating 
your crosstab query and what you were doing with the selection. If you are 
creating criteria for the querie you would have to create a Where type 
string from the loop through the list box selected items like this

If Forms!frmName!lstListBoxName.Selected(intListCt) = True Then
     If strItem="" Then
         strItem=" WHERE FieldName='" _
                & Forms!frmName!lstListBoxName.Column(0, intListCt) & "'"
     Else 
         strItem=strItem & " Or FieldName='" _
              & Forms!frmName!lstListBoxName.Column(0,intListCt) & "'"
     End if
End If

This is, of course, if the value from the listbox is a string. If it is a 
number you need to remove the ' marks and the & "'" at the end, if it is a 
date you would have to replace the ' with #. You would then have to add 
the where statement strItem to the SQL of the query and bind that to the 
report.

If the crosstab is being created correctly you can then use the following 
code to bind the report to the query values:

Private Sub Report_Open(Cancel As Integer)
    Dim intCount As Integer
    Dim rstList As ADODB.Recordset
    Dim strLable As String
    Dim strText As String
    Dim strColumnName As String
    
    Set rstList = New ADODB.Recordset
    rstList.Open Me.RecordSource, CurrentProject.Connection, _
                           adOpenStatic, adLockReadOnly, adCmdTable
    
    For intCount = 1 To 3
        strLable = "lbl" & intCount
        strText = "txt" & intCount
        strColumnName = rstList.Fields(intCount + 1).Name
        Me(strLable).Caption = strColumnName
        Me(strText).ControlSource = strColumnName
    Next intCount
    
    rstList.Close
    Set rstList = Nothing
End Sub

I name the lables something that can me incremented (ie. lbl1, lbl2, lbl3) 
and each label would have a corresponding textbox for the data (ie. txt1, 
txt2, txt3)... you get the idea. You have to make sure you skip the number 
of row values (in my case it is 2) which I do in the 
line 'strColumnName=rstList.Fields(intcount+1).Name (remember that the 
fields collection starts with and index of 0, that is why I am using +1 
instead of +2). This will not only bind the correct fields to the report 
but it will label them also.

You could probably do all this in one procedure but you don't have to. If 
you need more help or something doesn't make sense, let me know.

Hope this helps,

Amy



> Does this code put a comma between the entries?  

> How then do you use the strItem in the query criteria?

> I am also having problems creating a report based on this crosstab 
query.  I want it to be 
d> ynamic.  I don't want to have fixed columns.

> > I don't know what your process is to get the values into a crosstab 
(not 
k> > nowing what your code is) but here is how you can extract what has 
been 
s> > elected from the list box.

> > I am assuming the list box is allowing you to select multiple items 
(this 
i> > s a property setting if it is not). To gather the information from 
the 
l> > istbox of the selected items you need to do something on this order:

> > Dim intListCt as Integer            
d> > im strItem as String

> > For intListCt = 0 To Forms!frmName!lstListBoxName.ListCount - 1
 > >   If Forms!frmName!lstListBoxName.Selected(intListCt) = True Then
 > >       strItem=Forms!frmName!lstListBoxName.Column(0, intListCt)
 > >       'Item is selected and the first column value is what we want
 > >       ...code used to extract or store the value of the selected item
 > >   End If
N> > ext intListCt

> > Hope this helps,

> > Amy

> > > I need to use a List Box that a user is capable of selecting 
multiple 
s> > elections and then pass those selections to a crosstab query.  I 
create 
a>  
f> > orm and a list box on this form then in the Xtab query, I tried to 
r> > eference that List box under the criteria and it won't work.  What am 
i 
d> > oing wrong????

> >