Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiselect & Query Error (2002)

    Hi,

    I've spent the last 3 hours trying to work past this error with no luck, so I'm posting the stripped down version of the database to see what the problem is. The code is copied from the multiselect article mentioned in post 402675. I got it to work last night on a different in order to get a form to open, and in that instance I played with and changed the code. I simply substituted my field names in this one so I'm really confused as to why its blowing up. First I get an error message "You canceled the previous action". Then when I go to check the query I get the error "Data type mismatch in criteria expression". I don't understand or know where to begin.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    When the error occurs, you can click Debug; you will be taken to the Visual Basic Editor so that you can inspect the value of variables. If you hover the mouse above Q.SQL, you will see something like this (actual value depends on what you selected in the list box):

    Select * From tblISFTemp Where [ID] In("16355","16356","16357");

    As you see, the values for ID are enclosed in quotes. This is appropriate for a text field, but not for a number field such as ID. The quotes are inserted in the code as Chr(34); you can omit them. The resulting code will look like this:

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Thanks Hans!

    I knew about the debug piece and did that but for the life of me didn't know what the error meant. I didn't know about the hovering. Not that it would've helped me in this case as I still wouldn't have known what I'd done wrong. I appreciate the code pointing me in the right direction. It works great!

    Leesha

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Hi Hans,

    On testing the code, it works fine if I only select one record. If I multiselect records I get an error re a syntax error and a missing operator. When I debugged the following code was yellow:

    Q.SQL = "Select * From tblISFTemp Where [ID] In(" & Criteria & _
    ");"

    When I hovered over the code I saw the message but didn't for the life of me know how to fix it. What am I missing when I multiselect?

    Thanks,
    Leesha

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    Did you change something else beside removing the Chr(34)? In the database you attached in the first post in this thread, it works OK for both a single selection and a multiple selection, after removing the Chr(34). I have attached the database with this modification.

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    I must've changed something without realizing it because when I inserted you example it ran fine. Thanks!

    I had been trying to make the code actually delete the records from the list instead of run the query. First I made the query a delete query but got all messed up when I tried to add the code to actually run vs just open the query. So then I tried to add code to delete the records themselves and in doing so must've changed the code above it without realizing it. The code I'm using to delete the records (don't laugh now) is as follows:

    Dim Q As QueryDef, DB As Database
    Dim Criteria As String
    Dim ctl As Control
    Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me![lstISFRecords]

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

    If Len(Criteria) = 0 Then
    Itm = MsgBox("You must select one or more items in the" & _
    " list box!", 0, "No Selection Made")
    Exit Sub
    End If

    ' Modify the Query.
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryDeleteISFSelectedRecords")
    Q.SQL = "Select * From tblISFTemp Where [ID] In(" & Criteria & _
    ");"
    Q.Close


    'Deletes selected Records

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    'Requeries lstISFRecords

    Me.lstISFRecords.Requery

    'Goes to first record

    DoCmd.GoToRecord , , acFirst

    It works fine the first time if I select one record. When I go to do another single record I'm told there is no current record (which was my rationale for putting in the code to go to the first record after a record is deleted). If I try to delete multiple records, I'm still prompted that I'm about to delete "1" record and then I get the errror that there is no current record. In this instance nothing is deleted. Where did I go wrong this time?

    Leesha

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    Your code

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    doesn't delete the items selected in the list box, it deletes only the current record in the form. I would replace this part:

    <img src=/w3timages/blueline.gif width=33% height=2>
    ' Modify the Query.
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryDeleteISFSelectedRecords")
    Q.SQL = "Select * From tblISFTemp Where [ID] In(" & Criteria & _
    ");"
    Q.Close

    'Deletes selected Records

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    <img src=/w3timages/blueline.gif width=33% height=2>

    by

    DoCmd.RunSQL "DELETE * FROM tblISFTemp WHERE [ID] In(" & Criteria & ");"

    Note that SELECT has been replaced by DELETE in the SQL statement. Instead of modifying the SQL of a stored query, the SQL is executed directly using DoCmd.RunSQL. If you want to avoid the warning "You are about to delete ... records", you can use

    CurrentDb.Execute "DELETE * FROM tblISFTemp WHERE [ID] In(" & Criteria & ");"

    instead.

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Thanks Hans not only for the code but the explanations!

    Leesha

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Back again. I'm now trying to open a report based on the records selected in the table but of course am getting an error that I can't figure out. Believe it or not I had 2 other errors but worked those through on my own! I simply don't understand what this one is looking for.

    On the forms page the option I'm selecting is "Print Selected Records".

    Thanks,
    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    If you look at the code for cmdPrint_Click. and compare it to the code for cmdDeleteRecords_Click, you will see that you haven't copied the code that fills the Criteria variable correctly.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Hi Hans,

    Bear with me. I printed out the code for both and didnt' see the difference between the two (except one deletes records and one prints them). So, tired and frustrated, I copied and pasted the code from the one that works into the one that doesn't and the error is still same. I'm stuck.

    Leesha

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    In cmdDeleteRecords_Click, you have

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

    This is correct. In cmdPrint_Click, you have

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

    This is clearly incomplete (there is nothing between Then and Else)

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Hans,

    I just checked the code that I had copied (and still get an error message for and it contains the missing line so I'm really confused. The code for the print command is:

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

    If Len(Criteria) = 0 Then
    Itm = MsgBox("You must select one or more items in the" & _
    " list box!", 0, "No Selection Made")
    Exit Sub
    End If



    'Prints report with selected names

    DoCmd.OpenReport "rptISFForm", acViewPreview
    __________

    The code for the delete button is:

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = ctl.ItemData(Itm)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    End If
    Next Itm

    If Len(Criteria) = 0 Then
    Itm = MsgBox("You must select one or more items in the" & _
    " list box!", 0, "No Selection Made")
    Exit Sub
    End If

    _________

    I know I'm going bling from being at this all day, but the code looks the same to me and yet I get the error that there is a syntax error and a missing operator and the code to open the report is hightlighted.

    I apologize, but I'm lost.

    Leesha
    'Deletes selected Records

    CurrentDb.Execute "DELETE * FROM tblISFTemp WHERE [ID] In(" & Criteria & ");"

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Multiselect & Query Error (2002)

    Here you go. See attached database. It really was as simple as what I wrote. I added code for printing all records.

  15. #15
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiselect & Query Error (2002)

    Thank you so much. I still don't see the difference but you can be sure when my brain clears I will study it again.

    I appreciate you patience!

    Leesha

Page 1 of 2 12 LastLast

Posting Permissions

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