Update query w/form not working correctly!

[breakingme10]

I'm really frustrated. I've been working on an update query to update some fields in a table based on what is entered into a form.



here is my full code from my form (taken from AllenBrowne site for filtering a report based on a list box in a form):

Private Sub cmdCriteria_Click()
On Error GoTo Err_Handler
    Dim varItem As Variant    
    Dim strWhere As String     
    Dim strDescrip As String   
    Dim lngLen As Long         
    Dim strDelim As String     
    Dim strCtrl As String      

    strCtrl = "txtCriteria"

    With Me.lstTicketNums
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ",
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
    
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        strWhere = "In (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "Tickets: " & Left$(strDescrip, lngLen)
        End If
    End If
    
    txtCriteria.Value = strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then 
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
    End If
    Resume Exit_Handler
End Sub

This works as expected, it populates a control (text field) on my form with the WHERE string
after this i have a second command button that after previewing the where string in the form i can click to actually execute the query. it is as follows:

Private Sub cmdOK_Click()
Me.Visible = False
DoCmd.OpenQuery "WaterTicketsUpdateInvoiceInformation", acViewNormal, acEdit
DoCmd.Close acForm, "UpdateInvoice"
End Sub

Ok, this code works correctly too.

But when it gets to the pop up telling me how many lines will be updated, i get 0 every time.
My query has the following for criteria on the TicketNumber column:
[Forms]![UpdateInvoice]![txtCriteria]

which is the control that the where statement is passed to. (looks like this

 In (84578,85635,85645)

)

What am i doing wrong that this isn't working? the ticketnumber field is a number

[June7]

Don't think a parameterized query can work with that input. All it sees is a single string of characters, not a set of distinct values.

I never use dynamic parameterized queries.

I would construct and execute the SQL statement in VBA

CurrentDb.Execute "UPDATE tablename SET fieldname=something WHERE somefield IN (" & strWHERE & ")"

Review: http://allenbrowne.com/ser-50.html

[breakingme10]

I don't have any parameters set, but when i did use parameters rather than the form it worked correctly. but i think you are right and i should give up on the update query and just use vba.

[June7]

You said:

"My query has the following for criteria on the TicketNumber column:
[Forms]![UpdateInvoice]![txtCriteria]"

That is a dynamic parameter.

I've never used PARAMETERS keyword in queries.

I construct and run all action SQLs in VBA.