Results 1 to 4 of 4
  1. #1
    breakingme10 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153

    Question Update query w/form not working correctly!

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    breakingme10 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculated field on a form not working correctly
    By danielrogers1 in forum Access
    Replies: 5
    Last Post: 07-28-2014, 01:24 PM
  2. Replies: 15
    Last Post: 12-10-2012, 06:37 PM
  3. Condtion not working correctly
    By hawkins in forum Access
    Replies: 3
    Last Post: 09-07-2011, 02:59 PM
  4. Replies: 5
    Last Post: 03-17-2011, 06:21 AM
  5. Split Form not working correctly
    By Brian62 in forum Access
    Replies: 29
    Last Post: 02-16-2010, 05:43 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums