Results 1 to 8 of 8
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    Wondering Why On Change Event Won't Fire


    I have a combo box with codes in the After Update, On Change, and On Not In List events. The After Update and Not In List events work appropriately, but the code I've put in the On Change event just doesn't fire. No error code is triggered and the compiler/debugger find nothing wrong. I'm wondering if anyone can shed light as to why the On Change event isn't working since I use this On Change event code successfully all over my database... The On Change event is a filter to allow the user to type in characters in any order to find what they're looking for rather than sequentially like the default. Here are the codes:

    Code:
    Private Sub RawMaterial_AfterUpdate()
    On Error GoTo errHandler
    Dim sSQL As String
    
    
    If Me.Dirty Then Me.Dirty = False
    
    
        'Update: MISCINFO, POTENCY, PUoM, CUoM, COST, COSTUoM
        sSQL = "UPDATE tmp_Formula INNER JOIN tbl_RawMaterial ON tmp_Formula.RawMaterial = tbl_RawMaterial.RawMaterial " _
             & "SET tmp_Formula.MiscInfo = [tbl_RawMaterial].[MiscInfo], tmp_Formula.Potency = [tbl_RawMaterial].[Potency], " _
             & "tmp_Formula.PUoM = [tbl_RawMaterial].[PUoM], tmp_Formula.CUoM = [tbl_RawMaterial].[ClaimUoM], " _
             & "tmp_Formula.Cost = [tbl_RawMaterial].[Cost], tmp_Formula.CostUoM = [Tbl_RawMaterial].[CostUoM] " _
             & "WHERE (((tmp_Formula.RawMaterial)='" & RawMaterial & "'));"
        CurrentDb.Execute sSQL, dbFailOnError
        CurrentDb.TableDefs.Refresh
        Me.Refresh
    
    
        'Update: BP, ITEM, BILLTYPE, UoM (Subingredients)
        sSQL = "INSERT INTO tmp_Formula (BP, Item, BillType, RawMaterial, UoM) " _
             & "SELECT '" & [Forms]![frm_Formulation]![BP] & "' AS BP, '" & [Forms]![frm_Formulation]![Item] & "' AS Item, " _
             & "'" & [Forms]![frm_Formulation]![Bill Type] & "' AS BillType, tbl_RawMaterial.RawMaterial, '' AS UoM " _
             & "FROM tbl_RawMaterial " _
             & "WHERE (((tbl_RawMaterial.RawMaterial) Like Left('" & RawMaterial & "',InStr('" & RawMaterial & "', ' - ')-1) & 'S*'));"
        CurrentDb.Execute sSQL, dbFailOnError
    
    
    Me.Requery
    
    
    DoCmd.GoToRecord , , acLast
    MiscInfo.SetFocus
    
    
    Exit Sub
    
    
    errHandler:
        MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
        
    End Sub
    
    
    Private Sub RawMaterial_Change()
    Dim strText, strFind
    
    
    ' Get the text that the user has typed into the combo box editable field.
    strText = Me.RawMaterial.Text
    ' If the user has typed something in, then filter the combobox
    ' list to limit the visible records to those that contain the
    ' typed letters.
    ' Otherwise (if the field is blank), the user has deleted whatever
    ' text they typed, so show the entire (unfiltered) list
    If Len(Trim(strText)) > 0 Then
        ' Show the list with only those items containing the typed letters.
        ' Create an SQL query string for the WHERE clause of the SQL
        ' SELECT statement.
        strFind = "RawMaterial Like '"
        For i = 1 To Len(Trim(strText))
            If (Right(strFind, 1) = "*") Then
                strFind = Left(strFind, Len(strFind) - 1)
            End If
            strFind = strFind & "*" & Mid(strText, i, 1) & "*"
        Next
        strFind = strFind & "'"
        ' Create the full SQL SELECT string for the combo box's
        ' .RowSource property.
        strSQL = "SELECT tbl_RawMaterial.RawMaterial FROM tbl_RawMaterial Where " & _
        strFind & " ORDER BY RawMaterial "
        ' Filter the combo list records using the new SQL statement.
        Me.RawMaterial.RowSource = strSQL
    Else
        ' Show the entire list.
        strSQL = "SELECT tbl_RawMaterial.RawMaterial FROM tbl_RawMaterial;"
        Me.RawMaterial.RowSource = strSQL
    End If
    ' Make sure the combobox is open so the user
    ' can see the items available on list.
    Me.RawMaterial.Dropdown
    
    
    End Sub
    
    
    Private Sub RawMaterial_NotInList(NewData As String, Response As Integer)
        strSQL = "INSERT INTO tbl_RawMaterial([RawMaterial]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        Response = acDataErrAdded
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you put a breakpoint in your code to see if it is triggered
    And have you tried debug.print to check the generated string is a valid sql string?
    And have you added option explicit to the top of the module - you will find at least one compile error (I is not declared for example)

    not sure how your code allows users to type 'in any order'

    looking for '*a*b*' will not find 'ball' for example

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thanks for the reply Ajax, I'm sorry for the confusion. By any order I meant for "123 - Ball" the user can type in "Ball" and it will populate instead of the default where you have to type in 123 first.

    I'm about to leave for the day and they don't let me take my work home with me :/ I'll try your suggestions in the am.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    By any order I meant for "123 - Ball" the user can type in "Ball" and it will populate instead of the default where you have to type in 123 first.
    simply *ball* would do the trick, but if users a looking for *2*a* then I understand.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Simplest way to verify that the event is firing is to add something like this at the top of your RawMaterial_Change sub:

    Msgbox "Change Event Popped!"

    then test and see whether the Messagebox appears. If it does, and I expect that it will, the event is firing as it should, and the problem is with the code within the event is simply not working as you expected it to.

    If the Messagebox doesn't appear...I'd suspect that the Combobox Control was corrupted.

    Although we usually think of Forms, and even entire Databases, when we speak of corruption, Controls, such as Comboboxes, can and do become corrupted, and are particularly susceptible to this during app development.

    The test/cure for this is short and simple...delete and then re-create the Control.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Good point - other thing to check is you have [event procedure] against the combo change event property

  7. #7
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thank you for the response Linq, I tried your troubleshooting method. The msg box appears so I guess that means there is something in the code that's not functioning. That raises a question for me: Why does this code work in other combo boxes I have in my database?

    I literally use the exact same code on maybe 15 other combo boxes (each having the same name too) on other various forms. The only thing that is different about this particular combo box is that it also has an After Update and Not In List event attached. The others do not.

    I'm at a loss for how to get this functioning now.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems odd that you say the event doesn't fire, but then it does (because of the message box) yet you invoke the dropdown method on a combo. Didn't the combo ever drop down before the message box was added? If it did, then your problem isn't that the event wasn't firing. Possibly your problem is that the rowsource isn't correct? If so, then I suggest debug.print the beginning, WHERE part (which is a variant, not a string' because you didn't assign a type to strFind) and any ORDER by or other clauses, and copy to a new query in sql view. The sql may be wrong.

    Seems odd to me that your procedure adds a trailing * then removes it upon each following iteration. I think I'd make it part of the concatenated sql, such as
    Beginning Part & "*" & strFind & "*" unless you think users will type in * at the end. Even if they do, I suspect the extra * won't matter, but my recollection on that might be fuzzy.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 11
    Last Post: 06-02-2018, 02:48 PM
  2. Fire off event on another form
    By Ruegen in forum Access
    Replies: 4
    Last Post: 04-12-2015, 11:50 PM
  3. Replies: 6
    Last Post: 01-26-2015, 02:11 PM
  4. Replies: 7
    Last Post: 02-23-2014, 06:06 PM
  5. Trying to fire event on record change
    By danielhowden in forum Forms
    Replies: 3
    Last Post: 05-13-2011, 06:30 AM

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