Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2012
    Posts
    8

    Unanswered: From from List box to a paramter query

    Im having serious problems getting my head around thisI want to pass a list box (which is called Pract_List) to a paramter query called testquery but for some reason it wont work. The query passes but doesnt produce any results so confused . Please could anyone help

    Private Sub Command_4_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("TESTQUERY")
    For Each varItem In Me!Pract_List.ItemsSelected
    strCriteria = strCriteria & "," & Me!Pract_List.ItemData(varItem) & ""
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, " & _
    "[Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], " & _
    "[Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], " & _
    "[Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], " & _
    "[Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] " & _
    "WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(" & strCriteria & "));"

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Immediately after the instruction that composes the query (strSQL = "SELECT ... IN(" & strCriteria & "));"), add a line to display the contents of strSQL in the Immediate window:
    Code:
    Debug.Print strSQL: Stop
    2. When the code stops executing, open the Immediate window (Ctrl+G), select and copy the string.

    3. Open a new Query, switch to SQL view, paste the copied line, switch to Datasheet view and see what happens.
    Have a nice day!

  3. #3
    Join Date
    Nov 2012
    Posts
    8
    Hey nothing happens just a blank query shows

  4. #4
    Join Date
    Nov 2012
    Posts
    8
    However would it not be blank if i never selected anything from the list box in my form im so confused

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This means that there is something wrong with the way you compose the query SQL string or that there is no match for the criteria in the table.
    a) Does the query builder report any error?
    b) Is [PRACTICE CODE] defined as a Numeric data type ?
    c) Are there rows into [Tbl GMS Assets V2 8th Nov] that match the criteria?
    Have a nice day!

  6. #6
    Join Date
    Nov 2012
    Posts
    8
    ) Does the query builder report any error?
    b) Is [PRACTICE CODE] defined as a Numeric data type ?
    c) Are there rows into [Tbl GMS Assets V2 8th Nov] that match the criteria?

    No it just runs blank records when I pasted it.

    Practice code is a text field. I will give you a bit of information of the task i am undertaking;

    Currently I have a database with assets in businesses around ireland. the practice code is like a branch number. There are lots of different ict equipment in each branch. But from a list box in a form i want to select all branches in around the same area click ok which will then run my query and produce the results. Does that make sense?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If [PRACTICE CODE] is defined as Text, you should use:
    Code:
    For Each varItem In Me!Pract_List.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!Pract_List.ItemData(varItem) & "'"
    Next varItem
    Have a nice day!

  8. #8
    Join Date
    Nov 2012
    Posts
    8
    It didnt even run the query now i copied and pasted it over the previous bit is the following correct i hate vb its so fustrating im literally pulling my hair out now


    Private Sub Command_4_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String, strSQL As String

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

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

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

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    strSQL = "SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, " & _
    "[Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], " & _
    "[Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], " & _
    "[Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], " & _
    "[Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] " & _
    "WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(" & strCriteria & "));"



    Set db = Nothing
    Set qdf = Nothing
    End Sub

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It seems to be, except for:
    Code:
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    that must become:
    Code:
    strCriteria = Right(strCriteria, Len(strCriteria) - 2)
    as you add an supplemental extra character in front of each value, including the first one (,' instead of ,).
    Have a nice day!

  10. #10
    Join Date
    Nov 2012
    Posts
    8
    I dont know whats wrong now it just doesnt work

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Can you post the contents of strSQL?
    Have a nice day!

  12. #12
    Join Date
    Nov 2012
    Posts
    8
    SELECT [Tbl GMS Assets V2 8th Nov].ID, [Tbl GMS Assets V2 8th Nov].[Asset Id], [Tbl GMS Assets V2 8th Nov].Description, [Tbl GMS Assets V2 8th Nov].Manufacturer, [Tbl GMS Assets V2 8th Nov].Model, [Tbl GMS Assets V2 8th Nov].[Serial No], [Tbl GMS Assets V2 8th Nov].[Location Details], [Tbl GMS Assets V2 8th Nov].[Asset verified], [Tbl GMS Assets V2 8th Nov].[Old asset Id], [Tbl GMS Assets V2 8th Nov].[Additional information], [Tbl GMS Assets V2 8th Nov].[PRACTICE CODE], [Tbl GMS Assets V2 8th Nov].[purchase date], [Tbl GMS Assets V2 8th Nov].[Date Asset Checked] FROM [Tbl GMS Assets V2 8th Nov] WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN(1','3'));

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    My mistake, sorry !
    Keep:
    Code:
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    It should be:
    Code:
    WHERE(([Tbl GMS Assets V2 8th Nov].[PRACTICE CODE]) IN('1','3'));
    Have a nice day!

  14. #14
    Join Date
    Nov 2012
    Posts
    8
    its just running up an error now dear god i hate this

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •