Multiselect & Query Error (2002)

[Leesha]

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

[HansV]

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

[Leesha]

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

[Leesha]

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

[HansV]

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.

[Leesha]

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

[HansV]

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.

[Leesha]

Thanks Hans not only for the code but the explanations!

Leesha

[Leesha]

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

[HansV]

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.

[Leesha]

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

[HansV]

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)

[Leesha]

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 & ");"

[HansV]

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

[Leesha]

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