1

I believe that an Office update was installed on my work computer this week. Since then I am seeing an error message in a database I built.

PC = Windows 10 Enterprise OS Build 17763.557
Office = 365 ProPlus Version 1808 Build 10730.20348

I have a button on a form that runs a SQL search for a record and populates the form. This code has worked for over a year.

When clicking the button the code fails with the error message

"The expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events."

If I open Visual Basic alongside the form, the query runs without error.

I am not sure if the Office update and the error message are related. I do not believe any changes have been made to the code or database structure. No other error messages have been reported from other users.

Private Sub Command360_Click()
    If IsNull(Me.SEARCH_TEXT.Value) Or Me.SEARCH_TEXT.Value = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.SEARCH_TEXT.BackColor = vbYellow
        Me.SEARCH_TEXT.SetFocus
    Else
        strsearch = Me.SEARCH_TEXT.Value
        Me.SEARCH_TEXT.BackColor = vbWhite
        SearchTask = "SELECT * FROM [tMASTER_BOREHOLE_LIST] WHERE ( ([HOLEID] Like ""*" & strsearch & "*"") OR ([TYPE] Like ""*" & strsearch & "*"") OR ([ALTERNATE_NAME1] Like ""*" & strsearch & "*"") OR ([ALTERNATE_NAME2] Like ""*" & strsearch & "*"") OR ([ALTERNATE_NAME3] Like ""*" & strsearch & "*"") OR ([Hole_Location] Like ""*" & strsearch & "*"") OR ([ALTERNATE_NAME4] Like ""*" & strsearch & "*"") OR ([Collar_Site_No] Like ""*" & strsearch & "*"") OR ([Site_ID] Like ""*" & strsearch & "*"") OR ([HOLE_NAME] Like ""*" & strsearch & "*"") OR ([Hole_Number] Like ""*" & strsearch & "*"") OR ([Design_Point_Number] Like ""*" & strsearch & "*"") OR ([STAKED_Point_Number] Like ""*" & strsearch & "*"") OR ([As_Drilled_Point_Number] Like ""*" & strsearch & "*"") OR ([COLLAR_LOCATION1] Like ""*" & strsearch & "*"") OR ([COLLAR_LOCATION] Like ""*" & strsearch & "*"") OR ([LW_FOR_SEALUP] Like ""*" & strsearch & "*""))"
        Me.RecordSource = SearchTask
    End If
End Sub
Community
  • 1
  • 1
LMORSE
  • 35
  • 2
  • 6
  • 2
    Try to [Decompile](https://stackoverflow.com/a/3268188/3820271) and recompile your code. Make sure there is `Option Explicit` at the top of all modules. – Andre Jul 03 '19 at 12:00
  • 1
    AT what line does the error pop? – Doug Coats Jul 03 '19 at 13:57
  • "AT what line does the error pop?" - that's the tricky part, I can't step through to find the error because as soon as I open Visual Basic the error does not happen. Interestingly, I just made a copy of the database to decompile and recompile; before doing anything I ran the database copy to check and the error did not happen, but it still happens in the original...??? – LMORSE Jul 03 '19 at 19:29

2 Answers2

3

Apparently this error is common in databases that use foreign characters. From personal experience it picks events at random to error on, irrespective of the code inside the event.

Anyway, the solution that saved my database (and sanity) is decompiling, as explained on sourcedaddy, but I'll list the instructions here to save you having to follow yet another link.

  • Press Win + R to open the Run window, enter msaccess /decompile, and press Run
  • When Access opens, hold down shift and open your database
  • Once the database is open, exit Access fully
  • Restart Access and open your database again
  • Open the code editor, click Debug >> Compile
  • After the db compiles, close the code editor and click Compact and Repair Database in Database Tools

And magic!

Hope this saves someone else the hours I expended trying to fix this bug...

half of a glazier
  • 1,864
  • 2
  • 15
  • 45
  • Better link for *the steps required for a decompile to be worth doing": [Decompile](https://stackoverflow.com/a/3268188/3820271) – Andre Jul 21 '20 at 14:15
  • it seems to me that it breaks when you add to the form a field with foreign character in its name and control with such character in its ID appears in the tree; it should be enough to add field manually (with ascii-only ID) – Kodak Jun 14 '23 at 13:18
0

I have also encountered this error before and it drove me insane. The issue seemed to be that we had two versions of Access installed on one computer.

Uninstalling all versions of office on our computers and then reinstalling them resolved this issue.

ChufferMcDonald
  • 13
  • 5