Method to query all items from a multiselect=none listbox???

[smmaya]

Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya

 

[Douglas J. Steele]

You cannot use a multi-select list box like that as a parameter for a query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've selected all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for an
example.

 

[smmaya]

Doug,
It's not really clear to me...
I can't select multiple items because when I DblClick on one of them I
have a separate form poping up with all items details.
I just want to find a way to "order" the machine to go and get all the
items currently listed in the listbox and create a report.
I know you must be pulling your hair by now but...have you any other
ideas.

I followed the link but I don't understand how should I create a hidden
control to concatenate all listed items using a WHERE clause...
Maya

You cannot use a multi-select list box like that as a parameter for a query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've selected all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya

 

[Douglas J. Steele]

If double clicking on one causes another form to open up, you must have code
associated with the list box's DblClick event. What is that code?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
It's not really clear to me...
I can't select multiple items because when I DblClick on one of them I
have a separate form poping up with all items details.
I just want to find a way to "order" the machine to go and get all the
items currently listed in the listbox and create a report.
I know you must be pulling your hair by now but...have you any other
ideas.

I followed the link but I don't understand how should I create a hidden
control to concatenate all listed items using a WHERE clause...
Maya

You cannot use a multi-select list box like that as a parameter for a
query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've selected
all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya

 

[smmaya]

Here it is:

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_lstResults_DblClick

DoCmd.OpenForm "F_AutoIncidents", acNormal, , "[IncID] = " &
Me.lstResults

Exit_lstResults_DblClick:
Exit Sub

Err_lstResults_DblClick:
MsgBox "You must select an entry!", vbOKOnly, "Error!"

Resume Exit_lstResults_DblClick

End Sub

I have tried to set the box as Sinlge or Extended selection but then
when I Dbl click the code sends back en error...

If double clicking on one causes another form to open up, you must have code
associated with the list box's DblClick event. What is that code?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
It's not really clear to me...
I can't select multiple items because when I DblClick on one of them I
have a separate form poping up with all items details.
I just want to find a way to "order" the machine to go and get all the
items currently listed in the listbox and create a report.
I know you must be pulling your hair by now but...have you any other
ideas.

I followed the link but I don't understand how should I create a hidden
control to concatenate all listed items using a WHERE clause...
Maya

You cannot use a multi-select list box like that as a parameter for a
query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've selected
all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya

 

[Douglas J. Steele]

Okay, so what you need to do is replace "[IncID] = " & Me.lstResults with
something that looks at all of the selected values:

Dim strWhere As String
Dim varSelected As Variant

If Me.MyListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me.MyListBox.ItemsSelected
strWhere = strWhere & Me.MyListBox.ItemData(varSelected) & _
", "
Next varSelected
strWhere = [IncID] IN (" & _
Left$(strWhere, Len(strWhere) - 2) & ")"
End If

DoCmd.OpenForm "F_AutoIncidents", acNormal, , strWhere

This assumes that incID is a numeric field. If it's text, you'd need:

strWhere = strWhere & Chr$(34) & _
Me.MyListBox.ItemData(varSelected) & _
Chr$(34) & ", "

I'd suggest moving that code to a different event, though: double-clicking
on a multiselected listbox can be problematic. Put a button that the user
can click on once they've selected the appropriate rows.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here it is:

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_lstResults_DblClick

DoCmd.OpenForm "F_AutoIncidents", acNormal, , "[IncID] = " &
Me.lstResults

Exit_lstResults_DblClick:
Exit Sub

Err_lstResults_DblClick:
MsgBox "You must select an entry!", vbOKOnly, "Error!"

Resume Exit_lstResults_DblClick

End Sub

I have tried to set the box as Sinlge or Extended selection but then
when I Dbl click the code sends back en error...

If double clicking on one causes another form to open up, you must have
code
associated with the list box's DblClick event. What is that code?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
It's not really clear to me...
I can't select multiple items because when I DblClick on one of them I
have a separate form poping up with all items details.
I just want to find a way to "order" the machine to go and get all the
items currently listed in the listbox and create a report.
I know you must be pulling your hair by now but...have you any other
ideas.

I followed the link but I don't understand how should I create a hidden
control to concatenate all listed items using a WHERE clause...
Maya

Douglas J. Steele wrote:
You cannot use a multi-select list box like that as a parameter for a
query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've
selected
all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to
list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid
option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya

 

[smmaya]

Wow, that sounds complicated...
Actually I finally got it working.
I had a RefreshQuery Sub with all criterias for the SQL string.
I have replaced Sub by Fonction in my Form, declared Dim RefreshQuery
(As String)added RefreshQuery = SQL before End Function.
Then in my button to view results from research I've put:

Private Sub_NameOfButton_Click()
DoCmd.OpenReport "NameOfReport", acViewPreview, RefreshQuery
End Sub

My Report then filters the items from the listbox without having to
select any of them.

I will post all the code so anyone can benefit later on...I have to
leave the office now.
Thanks for you help and good luck, until the next...
Maya

Okay, so what you need to do is replace "[IncID] = " & Me.lstResults with
something that looks at all of the selected values:

Dim strWhere As String
Dim varSelected As Variant

If Me.MyListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me.MyListBox.ItemsSelected
strWhere = strWhere & Me.MyListBox.ItemData(varSelected) & _
", "
Next varSelected
strWhere = [IncID] IN (" & _
Left$(strWhere, Len(strWhere) - 2) & ")"
End If

DoCmd.OpenForm "F_AutoIncidents", acNormal, , strWhere

This assumes that incID is a numeric field. If it's text, you'd need:

strWhere = strWhere & Chr$(34) & _
Me.MyListBox.ItemData(varSelected) & _
Chr$(34) & ", "

I'd suggest moving that code to a different event, though: double-clicking
on a multiselected listbox can be problematic. Put a button that the user
can click on once they've selected the appropriate rows.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here it is:

Private Sub lstResults_DblClick(Cancel As Integer)
On Error GoTo Err_lstResults_DblClick

DoCmd.OpenForm "F_AutoIncidents", acNormal, , "[IncID] = " &
Me.lstResults

Exit_lstResults_DblClick:
Exit Sub

Err_lstResults_DblClick:
MsgBox "You must select an entry!", vbOKOnly, "Error!"

Resume Exit_lstResults_DblClick

End Sub

I have tried to set the box as Sinlge or Extended selection but then
when I Dbl click the code sends back en error...

If double clicking on one causes another form to open up, you must have
code
associated with the list box's DblClick event. What is that code?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
It's not really clear to me...
I can't select multiple items because when I DblClick on one of them I
have a separate form poping up with all items details.
I just want to find a way to "order" the machine to go and get all the
items currently listed in the listbox and create a report.
I know you must be pulling your hair by now but...have you any other
ideas.

I followed the link but I don't understand how should I create a hidden
control to concatenate all listed items using a WHERE clause...
Maya

Douglas J. Steele wrote:
You cannot use a multi-select list box like that as a parameter for a
query
(even if only one item is selected from the list)

What you need to do is dynamically generate the SQL once you've
selected
all
of the desired items from the list.

See http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
for
an
example.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear all,
I really need help, can't find anywhere what I need to do. I need to
keep my listbox multiselection set to "none" because of the DblClick
event that opens a form with all the data...but I want my query to
list
all items listed in the Form's listbox after search and this without
slecting them...(I can't do it anyway because it is not a valid
option)
Until now my query displays only one selected item???

My MultiResearch Form is unbound as well as all its controls
The RowSource of the listbox [lstResults] is a SQL statement

My Query has:
Field: IncID
Table: T_Incidents
Criteria: [Forms]![F_MultiResearch]![lstResults]

but it doesn't work, it returns only one selected item...

Any help will be more than welcome.
Maya