ListBox multiselect problem.

[Lee Harwell]

Problem: I am trying to get the multiselect records chosen
from a Listbox (named: NameListBox)(Multiselect property
is set to "Extended") to open up in a form named
NameDetailForm. The following code appears to work up to
the point where the DoCmd OpenForm call breaks down,
causing an error message " Form not open".


After a failed attempt I have opened up the NameDetail
Form in Design view and the correct ComputerNo's have been
added to the Filter. Can you tell why the NameDetailForm
will not open displaying those records?

If you can suggest alternative code that allows me to
accomplish same I would be eternally grateful.


Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!NameListBox.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!NameListBox.ItemsSelected
' Grab the ISBN column for each selected item
strWhere = strWhere & Chr$(34) & Me!
NameListBox.Column(0, varItem) & Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the books form filtered on the selected books
gstrWhereBook = "[ComputerNo] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="NameDetailForm",
WhereCondition:=gstrWhereBook
End Sub

 

[Helen Trim]

I have tested your code on a sample database and it works
correctly. I suspect that there is something else wrong
with the form. Does it have any code in On Open or On
Activate?

HLH
Helen

 

[Lee Harwell]

Thanks, Helen
I created a new NameDetailForm with absolutely no Event
properties added. Still, after making choices in the
Listbox form, the NameDetailForm refuses to open.
This time I got the following message: Compile error,
variable not defined : gstrwherebook =

Furthermore, could my problem be a missing Library?
the following are in use:
1. Visual Basic for Applications.
2. MS Access 9.0 object library.
3. MS DA0 3.6 object library.
4. Ole Automation.
5. MS Visual Basic for Applications extensibility 5.3
6. MS ActiveX Data Object 2.1 library.

Thanks,

Lee

-----Original Message-----

I have tested your code on a sample database and it works
correctly. I suspect that there is something else wrong
with the form. Does it have any code in On Open or On
Activate?

HLH
Helen

-----Original Message-----
Problem: I am trying to get the multiselect records chosen
from a Listbox (named: NameListBox)(Multiselect property
is set to "Extended") to open up in a form named
NameDetailForm. The following code appears to work up to
the point where the DoCmd OpenForm call breaks down,
causing an error message " Form not open".


After a failed attempt I have opened up the NameDetail
Form in Design view and the correct ComputerNo's have been
added to the Filter. Can you tell why the NameDetailForm
will not open displaying those records?

If you can suggest alternative code that allows me to
accomplish same I would be eternally grateful.


Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!NameListBox.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!NameListBox.ItemsSelected
' Grab the ISBN column for each selected item
strWhere = strWhere & Chr$(34) & Me!
NameListBox.Column(0, varItem) & Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the books form filtered on the selected books
gstrWhereBook = "[ComputerNo] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="NameDetailForm",
WhereCondition:=gstrWhereBook
End Sub

.

.