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