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):
Code:
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:
Code:
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
Code:
In (84578,85635,85645)
)
What am i doing wrong that this isn't working? the ticketnumber field is a number