UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> List Box Multiple Selections Type Mismatch, Access 2016    
 
   
rramsey
post Jan 16 2020, 02:49 PM
Post#1



Posts: 24
Joined: 3-September 19



Hello All,

I'm using a form with a list box for my employees to be able to search for multiple sub accounts of a customers base account. (example: Base account 5959 and sub accounts 10, 14, and 17) I want to use a form with a list box where my employees can select numerous sub accounts and search for the open orders for those specifically selected sub accounts. I've searched the internet for some kind of VBA on this, and I have the following tied to a search button:

CODE
Private Sub Search_Click()
  On Error GoTo Err_Search_Click
  
  Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant

  'make sure a selection has been made
  If Me.NamesList.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Sub Account"
    Exit Sub
  End If

  'add selected values to string
  Set ctl = Me.NamesList
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
  DoCmd.OpenQuery "OO PRINT INTERNAL SUB ", , "CUSTOMER_SUBACCOUNT IN(" & strWhere & ")"

Exit_Search_Click:
  Exit Sub

Err_Search_Click:
  MsgBox Err.Description
  Resume Exit_Search_Click

End Sub


This keeps giving me a "Type Mismatch" error, and I think it is probably because my sub account field is set as a number, but I don't know what to change or how to get this code to claim the String is a number.

Help smile.gif
Go to the top of the page
 
theDBguy
post Jan 16 2020, 02:56 PM
Post#2


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi. Is CUSTOMER_SUBACCOUNT a number or a text field?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Jan 16 2020, 03:00 PM
Post#3



Posts: 24
Joined: 3-September 19



It is a number field
Go to the top of the page
 
theDBguy
post Jan 16 2020, 03:12 PM
Post#4


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Okay. Thanks. I think the problem here is the approach. You would normally build the arguments for the In() clause using a loop, like you're doing now, but to apply it as a criteria, you will have to modify the SQL statement of the query, instead of just passing it like that. Right now, you are passing a String value, and you're trying to compare it against a numeric field.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Jan 16 2020, 03:17 PM
Post#5



Posts: 24
Joined: 3-September 19



Okay, how would i state it in my SQL? I'm pretty sure it would be in the WHERE clause but I don't know how to properly state that for this scenario...
Go to the top of the page
 
theDBguy
post Jan 16 2020, 03:28 PM
Post#6


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Yes, you would use the WHERE clause. For example:

SELECT * FROM TableName WHERE FieldName In(List,Of,IDs,Here)

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Jan 16 2020, 03:34 PM
Post#7



Posts: 24
Joined: 3-September 19



What do you mean by (List,Of,IDs,Here)
Sorry, I'm just not getting it.
Go to the top of the page
 
theDBguy
post Jan 16 2020, 03:43 PM
Post#8


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


That would be the list of items selected from the listbox. Let's say you have the following list:

1
2
3
4

and the user selected 1, 3, and 4; then your SQL would/should look like this:

SELECT * FROM TableName WHERE ID In(1,3,4)

Hope that makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Larry Larsen
post Jan 16 2020, 04:39 PM
Post#9


UA Editor + Utterly Certified
Posts: 24,444
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


HI
PMFJI.. Hoping this small example and structured piece of code that loops through and builds that string criteria helps..

CODE
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourqueryname")

For Each varItem In Me!cboyourcontrolname.ItemsSelected
    strCriteria = strCriteria & "," & Me!cboyourcontrolname.ItemData(varItem) & ""
Next varItem

If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
        , vbExclamation, "Nothing to find!"

    Exit Sub
End If

'/ Trim
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT* FROM tblname " & _
    "WHERE tblname .fieldname IN(" & strCriteria & ");"
qdf.SQL = strSQL

DoCmd.OpenQuery "yourqueryname"

Set db = Nothing
Set qdf = Nothing

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
theDBguy
post Jan 16 2020, 04:48 PM
Post#10


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi Larry. Yes, that should do it. Thanks for the assist.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rramsey
post Feb 11 2020, 10:11 AM
Post#11



Posts: 24
Joined: 3-September 19



CODE
Private Sub Search_Click()
  On Error GoTo Err_Search_Click

    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim varItem     As Variant
    Dim strCriteria As String
    Dim strSQL      As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("OO PRINT INTERNAL SUB")

For Each varItem In Me!NamesList.ItemsSelected
    strCriteria = strCriteria & "," & Me!NamesList.ItemData(varItem) & ""
Next varItem

If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
        , vbExclamation, "Nothing to find!"

    Exit Sub
End If

'/ Trim
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT* FROM qry R001-12 Sub Acct Listing " & _
    "WHERE R001-12 Sub Acct Listing.Sub-Acct IN(" & strCriteria & ");"
qdf.SQL = strSQL

DoCmd.OpenQuery "OO PRINT INTERNAL SUB"

Set db = Nothing
Set qdf = Nothing
Exit_Search_Click:
  Exit Sub

Err_Search_Click:
  MsgBox Err.Description
  Resume Exit_Search_Click

End Sub


This is what I've tried using your idea, but it gives me a "Syntax error in FROM clause."

This is the SQL of the qry I am trying to get it to open with the selected criteria, any idea what I'm doing wrong?

SELECT [OO PRINT INTERNAL].CUSTOMER_BASE, [qry R001-12 Sub Acct Listing].[Sub-Acct], [OO PRINT INTERNAL].CUSTOMER_NUMBER, [OO PRINT INTERNAL].RECORD_SOURCE, [OO PRINT INTERNAL].ORDER_NUMBER, [OO PRINT INTERNAL].PROCESSING_CODE, [OO PRINT INTERNAL].DATE_ON_FILE, [OO PRINT INTERNAL].DateOnFileDateValue, [OO PRINT INTERNAL].ISBN_13, [OO PRINT INTERNAL].TITLE, [OO PRINT INTERNAL].FULL_LC_CALL_NUMBER, [OO PRINT INTERNAL].FUND_CODE, [OO PRINT INTERNAL].CUST_ID_SEQ, [OO PRINT INTERNAL].CUST_PO_NUMBER, [OO PRINT INTERNAL].LIST_PRICE, [OO PRINT INTERNAL].UK_LIST_PRICE, [OO PRINT INTERNAL].WAREHOUSE_NUMBER, [OO PRINT INTERNAL].QUANTITY_ORDERED, [OO PRINT INTERNAL].QUANTITY_ALOCATED, [OO PRINT INTERNAL].ALLOCATION_DATE, [OO PRINT INTERNAL].PICKLIST_DATE, [OO PRINT INTERNAL].PICKLIST_NUMBER, [OO PRINT INTERNAL].YBP_ORDER_KEY, [OO PRINT INTERNAL].PAPER_CLOTH, [OO PRINT INTERNAL].[US List Price], [OO PRINT INTERNAL].[UK List Price]
FROM [OO PRINT INTERNAL] INNER JOIN [qry R001-12 Sub Acct Listing] ON [OO PRINT INTERNAL].CUSTOMER_BASE = [qry R001-12 Sub Acct Listing].[Base Acct];

Go to the top of the page
 
Larry Larsen
post Feb 11 2020, 11:03 AM
Post#12


UA Editor + Utterly Certified
Posts: 24,444
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Do you actually have a query called "OO PRINT INTERNAL SUB"..??

It may be of help if you also placed a Debug.Print after the string build to see if what's be built is ok..
CODE
For Each varItem In Me!NamesList.ItemsSelected
    strCriteria = strCriteria & "," & Me!NamesList.ItemData(varItem) & ""
Next varItem

Debug.Print strCriteria

Ctrl G to view the result (Immediate Window)

A slight alternative may also help:
CODE
Set db = CurrentDb
    db.QueryDefs.Delete "OO PRINT INTERNAL SUB" '/ If you have one...
        
    strSQL = "YourSQLStatementHere In(" & strcriteria & ");"

    Set qrydef = db.CreateQueryDef("OO PRINT INTERNAL SUB", strSQL)

  DoCmd.OpenQuery "OO PRINT INTERNAL SUB"

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
rramsey
post Feb 11 2020, 11:11 AM
Post#13



Posts: 24
Joined: 3-September 19



That is the name of my qry yes (I didn't build that one haha) but neithe rof those seemed to work, the first solution gave me the same result of a syntax error, and the second wouldn't work at all.
Go to the top of the page
 
Larry Larsen
post Feb 11 2020, 11:26 AM
Post#14


UA Editor + Utterly Certified
Posts: 24,444
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Sorry to hear that, I have a test rig that I and most of use here have..

The snippets that I use as examples come from a working model of code..

Here is the full example I used to try and emulate your situation..

There will be different control names but the process is the same.

See if you can extract and work in a solution..
CODE
Dim strSQL As String
    Dim introw As Integer
    Dim vlist As String
    Dim mysql  As String
    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef

    On Error GoTo ErrorHandler

    Dim varItem As String
    Dim txtSQL As String
    Dim i As Variant
    
    For i = 0 To Me.lstSelected.ListCount - 1
        vlist = vlist & Me.lstSelected.Column(0, i) & ","
    Next i
    '/ Lets trim..
    vlist = Left(vlist, Len(vlist) - 1)
    
    Set db = CurrentDb
    db.QueryDefs.Delete "qrytest"
        
    mysql = "SELECT * FROM tblEmployees WHERE tblEmployees.EmployeeID In(" & vlist & ");"
    Set qrydef = db.CreateQueryDef("qrytest", mysql)
    DoCmd.OpenQuery "qrytest"

Cleanup:
    Set qrydef = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If


HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
rramsey
post Feb 11 2020, 12:48 PM
Post#15



Posts: 24
Joined: 3-September 19



CODE
Private Sub Search_Click()
  On Error GoTo ErrorHandler
    Dim strSQL As String
    Dim introw As Integer
    Dim vlist As String
    Dim mysql  As String
    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef

    

    Dim varItem As String
    Dim txtSQL As String
    Dim i As Variant
    
    For i = 0 To Me.NamesList.ListCount - 1
        vlist = vlist & Me.NamesList.Column(0, i) & ","
    Next i
    '/ Lets trim..
    vlist = Left(vlist, Len(vlist) - 1)
    
    Set db = CurrentDb
    db.QueryDefs.Delete "OO PRINT INTERNAL SUB"
        
    mysql = "SELECT * FROM OO PRINT INTERNAL WHERE OO PRINT INTERNAL.BUSTOMER_SUBACCOUNT In(" & vlist & ");"
    Set qrydef = db.CreateQueryDef("OO PRINT INTERNAL SUB", mysql)
    DoCmd.OpenQuery "OO PRINT INTERNAL SUB"

Cleanup:
    Set qrydef = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If
    
End Sub


This is only deleting my qry, it is not creating a new one with the results of the selected. Where is it getting stuck? when I click my search button, nothing on screen happens, but upon going into the back end of things I see the "OO PRINT INTERNAL SUB" is gone
Go to the top of the page
 
cheekybuddha
post Feb 11 2020, 01:36 PM
Post#16


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


This is why we always recommend to avoid spaces in object names:
CODE
' ...
    mysql = "SELECT * FROM [OO PRINT INTERNAL] WHERE [OO PRINT INTERNAL].BUSTOMER_SUBACCOUNT In (" & vlist & ");"
' ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
rramsey
post Feb 11 2020, 03:40 PM
Post#17



Posts: 24
Joined: 3-September 19



This now runs the query but it isnt giving me only the sub accounts selected in the list, it is giving me all
Go to the top of the page
 
cheekybuddha
post Feb 11 2020, 04:15 PM
Post#18


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


Looking at your code it doesn't appear that you have tested whether an item is selected or not.

I suggest, to make this easier, that you copy in to a new standard module the function found in this post.

Then you can do:
CODE
Private Sub Search_Click()
  On Error GoTo ErrorHandler

    Dim vlist As String
    Dim mysql  As String
    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef

    vlist = fSelected(Me.NamesList, True)
    If Len(vlist) = 0 Then
      MsgBox "Nothing selected"
      Exit Sub
    End If
    Set db = CurrentDb
    Set qrydef = db.QueryDefs("OO PRINT INTERNAL SUB")
    qrydef.SQL = "SELECT * FROM [OO PRINT INTERNAL] WHERE BUSTOMER_SUBACCOUNT IN " & vlist & ";"
    DoCmd.OpenQuery "OO PRINT INTERNAL SUB"

Cleanup:
    Set qrydef = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If
    
End Sub


There's no point deleting and recreating the same QueryDef - just alter its SQL.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
rramsey
post Feb 12 2020, 10:44 AM
Post#19



Posts: 24
Joined: 3-September 19



CODE
Private Sub NamesList_Click()
Dim strSelected As String
    Dim varItem As Variant

    With Me.NamesList
        For Each varItem In .ItemsSelected
            strSelected = strSelected & ", " & .ItemData(varItem)
        Next varItem
        Me.SubAccts = Mid(strSelected, 2)
    End With
End Sub

Private Sub Search_Click()
  On Error GoTo ErrorHandler

    Dim vlist As String
    Dim mysql  As String
    Dim db As DAO.Database
    Dim qrydef As DAO.QueryDef

    vlist = fSelected(Me.NamesList, True)
    If Len(vlist) = 0 Then
      MsgBox "Nothing selected"
      Exit Sub
    End If
    Set db = CurrentDb
    Set qrydef = db.QueryDefs("OO PRINT INTERNAL SUB")
    qrydef.SQL = "SELECT * FROM [OO PRINT INTERNAL] WHERE BUSTOMER_SUBACCOUNT IN " & vlist & ";"
    DoCmd.OpenQuery "OO PRINT INTERNAL SUB"

Cleanup:
    Set qrydef = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Err.Number = 3265 Then
        ' Item not found in this collection.
        ' query does not exist
        Debug.Print "Caught " & Err.Number & ": " & Err.Description
        Resume Next
    Else
        Debug.Print Err.Number & ": " & Err.Description
        Resume Cleanup
    End If
    
End Sub


This is my code for both, but right now it is returning an error on the "fSelected" part. It says Compile Error: Sub or Function not defined"
Go to the top of the page
 
cheekybuddha
post Feb 12 2020, 10:46 AM
Post#20


UtterAccess Moderator
Posts: 12,291
Joined: 6-December 03
From: Telegraph Hill


Did you do as I suggested in the previous post and copy the function in the linked thread into a new standard module in your db?

--------------------


Regards,

David Marten
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 10:49 AM