listbox selected items - Where

[knownote]

PHV helped me in a previous thread with solution at bottom of this post. I since added to it
to open query results in a report as well as a form when press spacebar to display
records selected in a listbox. Sometimes both form and report open to selected records;
sometimes both (when make choice between form or reports) result in:

Run-time error '3075':
Syntax error (missing operator) in query expression 'tblClients.Subgect
IN ('1Sample1-Bank','1Sample2-Credit Card')'.

CustomerID is defined as AutoNumber in table. Primary key is text (Subgect).
1Sample1-Bank is the text primary key Subgect of the sample record.

(Subgect is text primary key). CustomerID is still used elsewhere, I think, so I
still keep it in the backend tables tblClients and tblContacts.


Closing and reopening the form will then solve temporarily the problem, but there must be
a better way to display query results in either a form or reports.

Portion I changed from PHV's solution at bottom (I should know better than to improve on
PHV's solution, I guess):


If strList <> "" Then
If MsgBox("View results in form (Yes) or reports (No) view?", vbYesNo, "Format of Results") = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
Else
DoCmd.OpenReport "rptSubjects2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
DoCmd.OpenReport "rptSubtopics2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If
End If

Is this the wrong way to open reports from listbox selected items as far as Where criteria?
"tblClients.Subgect IN (" & Mid(strList, 2) & ")"

---- CODE I modified

Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
With Forms!frmAndOr!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
End With
' strList = Left(strList, Len(strList) - 2)
If strList <> "" Then
If MsgBox("View results in form (Yes) or reports (No) view?", vbYesNo, "Format of Results") = vbYes Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
Else
DoCmd.OpenReport "rptSubjects2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
DoCmd.OpenReport "rptSubtopics2", acPreview, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If
End If

Case Else ' see key code generated by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select

End Sub

---- CODE END


---- PHV helped me in a previous thread with this solution:

PHV
...
Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox
Dim varItm As Variant
Dim strList As String
strList = ""
With Forms!frmAndOr!lstResult
For Each varItm In .ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
Next varItm
End With
If strList <> "" Then
DoCmd.OpenForm "frmCustomer", acNormal, , "Subgect IN (" & Mid(strList, 2) & ")"
End If
...
Hope This Helps, PH.


knownote
It worked! Here's the solution for Access 97. It needed
tblClients.Subgect, since Subgect is in both tblClients and tblContacts.

DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"

Not sure if it was needed, but included ctl. in
For Each varItm In ctl.ItemsSelected

John


---- code


Private Sub lstResult_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode

Case 32 ' vbKeySpace key
KeyCode = 32 ' this "revaluation" kills escape key action
'Open frmCustomer based on Subgect(s) from lstResult listbox

Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
With Forms!frmAndOr!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & ",'" & ctl.ItemData(varItm) & "'"
'Debug.Print ctl.ItemData(varItm)
Next varItm
End With
' strList = Left(strList, Len(strList) - 2)
If strList <> "" Then
DoCmd.OpenForm "frmCustomer", acNormal, , "tblClients.Subgect IN (" & Mid(strList, 2) & ")"
End If

Case Else ' see key code generated by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select

End Sub

---- code end