Listbox ItemsSelected question 1

[knownote]

Hello all,

The following code works for 1 form but not this form (frmAndOr). Get error when try to open a query results form (frmCustomer) to show records selected in listbox (extended selection).
Thanks
John

---- code

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

Select Case KeyCode

Case 32 ' vbKeySpace key
KeyCode = 32 ' this is the "revaluation"
' that kills the escape key action
'Open frmCustomer based on the ID from lstResult listbox

Dim stDocName As String
stDocName = "frmCustomer"
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmAndOr
Set ctl = frm!lstResult
For Each varItm In ctl.ItemsSelected
strList = strList & "'" & ctl.ItemData(varItm) & "', "
'Debug.Print ctl.ItemData(varItm)
Next varItm
strList = Left(strList, Len(strList) - 2)
If IsLoaded("frmCustomer") = False Then
DoCmd.OpenForm stDocName, acNormal, , "[CustomerID] IN (" & strList & ")"
End If

Case Else ' just in case you want to
' take a look at the key code generated
' by other keystrokes
'MsgBox KeyCode ' comment this out when it annoys you
End Select

End Sub

----

Private Sub cmdExit_Click()

If IsLoaded("frmCustomer") = True Then
Forms!frmCustomer.RecordSource = "qdfAll"
End If
Me.SetFocus
DoCmd.Close acForm, Me.Name
End Sub

 

[knownote]

How can a string variable be substituted for the where line's IN criteria so the query results form (frmCustomer) shows records selected in listbox?
Thanks
John

 

[Zion7]

WHERE DOES THE ERROR OCCUR???

This is wriong,
If IsLoaded("frmCustomer") = True Then
should be
If CurrentProject.AllForms("frmCustomer").IsLoaded Then

 

[PHV]

Get error when try to open a query
Any chance you could post the error message ?
My guess: If CustomerID is defined as numeric in the table, then get rid of the single quotes in strList.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knownote]

Recap: In 2 prior posts, no 1 responded to the second. In the first, Golom's suggestion
changed my error message from 3075 Syntax error to OpenForm action error 2501.

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) Forum
where error - IN operator
thread705-1255609

no response

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) Forum
IN operator - error '3075'
thread705-1255130

Run-time error '3075':
Syntax error (missing operator) in query expression '[CustomerID] IN ({selected items})

I have extended selection turned on; I can select listbox results with shift, control, but
when press spacebar to open selected records, error occurs.


JPMontreal (Programmer) 17 Jul 06 11:57
The apostrophes are missing for the string.
...
strList = Left(strList, Len(strList) - 2)
DoCmd.OpenForm stDocName, acNormal, , "[CustomerID] IN (‘" & strList & "’)"

...


Golom (Programmer) 17 Jul 06 12:01
Or possibly from the individual values within the string

CODE
strList = strList & "'" & ctl.ItemData(varItm) & "', "


knownote (TechnicalUser) 17 Jul 06 16:55
Golom

Your suggestion got closer to working. Another error is
occurring that involves the Where in opening the results form.

run-time error '2501':
The OpenForm action was canceled.

You used a method of the DoCmd object to carry out an action in Visual Basic, but
then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked Cancel
in the dialog box that asks if you want to save the changes you made to the form.

 

[PHV]

Could you please simply answer my 2 questions posted 24 Jul 06 9:12 ?

 

[knownote]

CustomerID is defined as AutoNumber in table. Primary key is text (Subgect).

Code below gives:
Run-time error '3075':
Syntax error (missing operator) in query expression '[CustomerID] IN ({selected items})

---- 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 ID 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
For Each varItm In ctl.ItemsSelected
strList = strList & " " & ctl.ItemData(varItm) & ", "
'Debug.Print ctl.ItemData(varItm)
Next varItm
strList = Left(strList, Len(strList) - 2)

DoCmd.OpenForm "frmCustomer", acNormal, , "[CustomerID] IN (" & strList & ")"

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

End Sub

 

[PHV]

Could you please post the whole EXACT error message (as {selected items} is not a list of numbers ...) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knownote]

CustomerID is defined as AutoNumber in table. Primary key is text (Subgect).

Run-time error '3075':
Syntax error (missing operator) in query expression '[CustomerID] IN (1Sample1-Bank)

1Sample1-Bank is the text primary key Subgect of the sample record.

5 pages of threads for ItemsSelected, this didn't work:

For Each varItm In ctl.ItemsSelected
strList = strList & ctl.ItemData(varItm) & ", "

from
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Forms Forum
Print selected records from Multi Select List Box
thread702-787778

 

[PHV]

Why are you building a list of Subgect values to compare against CustomerID ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knownote]

That's just how the code worked for another form I want to drop.
Can I just reverse it to
DoCmd.OpenForm "frmCustomer", acNormal, , "[Subgect] IN (" & strList & ")"

(Subgect is text primary key). Not being a programmer, I need help with syntax, the reason I usually start a thread. CustomerID is still used elsewhere, I think, so I still keep it in the backend tables tblClients tblContacts.

I'll try
DoCmd.OpenForm "frmCustomer", acNormal, , "['Subgect"']" IN ('" & strList & "')"

 

[knownote]

I'll never understand code syntax.

I gave up at
DoCmd.OpenForm "frmCustomer", acNormal, , "['Subgect']" IN (‘" & strList & "’)

 

[PHV]

From your snippet posted 24 Jul 06 19:51

...
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.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[knownote]

It worked! Thanks for your patience, PHV. I usually write more directly than I did in this thread. I got lost searching and trying different combinations.

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