The expression On Open you entered as the event property setting produced the following error: Qualifier must be collection RRS feed

  • Question

  • I added a button to a form on our Front end accdb, wrote some code for the On Click handler, and started getting this error on our switchboard:

    The expression On Open you entered as the event property setting produced the following error: Qualifier must be collection

    Still getting the error after I removed the button and new code. Tried Compact/Repair to no avail. Was able to decompile but unable to recompile as other compile errors have started popping up. However, this form was working before -- how was it working if errors exist in other parts of the code? The button on click code was working for a little bit until it started throwing this error. The code I modified wasn't even part of the switchboard however this error is being thrown on startup. Any ideas?

    I deleted my local copy of the front end accdb and moved over a new copy from our network drive, yet the issue persists. If a coworker opens that same file, however, no errors.
    • Edited by jmt_ipa Tuesday, December 3, 2019 7:18 PM
    Tuesday, December 3, 2019 7:11 PM

All replies

  • Because switchboards get all their code written via a wizard, it's really hard to trace down the cause. I'd start by importing everything into a new, blank database to see if that fixes the issue.

    If things don't get better delete the mentioned form and build it again from scratch. Don't copy anything off of it for the new form as one or more controls might be corrupt.

    Bill Mosca

    Tuesday, December 3, 2019 7:20 PM
  • Normally I would do something like that, however I've inherited a 20+ year old code base and cannot recreate most things as it would be far too time consuming. For reference we have a split front/backend accdb. Could I try importing the frontend accdb into a new file?

    I'm fairly new to Access. What does it mean for a control to be corrupt? What specifically, under the hood, gets corrupt? Also, any ideas as to why running new copies of this file on my account causes this error but the same file runs fine on other accounts?

    Tuesday, December 3, 2019 7:25 PM
  • I was able to at least partially get this working again. There was an event handler subroutine left over from a control since removed. After getting through many compile errors, one of them was the error I listed above. I simply commented out this subroutine since nothing was calling it anymore and it worked. Still having some strange issues but I'm also still working through the compile errors.
    Tuesday, December 3, 2019 8:11 PM
  • The front end is the only file you would need to import from. The back end should be OK. As to why the database is breaking only on your account, I can't say. Maybe you don't have exactly the same OS and Office version as the working accounts.

    A form and its controls are all actually hidden code. Code pages in Access can break thus becoming corrupt. Sometimes the corruption is something very small and doesn't cause any problems. Adding a control to a form can push that old corruption to where it causes trouble.

    SaveAsText can sometimes clean up corruption. Go the the immediate Window by pressing Ctrl+G. Type in this:

    SaveAsText acForm,"yourformname","fullpathFileName"

    Press Enter. Then open the saved file in NotePad. You will be able to see all the stuff Access uses to create that form. If you add a control and later delete it, it is still part of the form's composition. The only way to really get rid of the garbage left behind is to import or use SaveAsText, deleting the form and then use LoadFromText to bring in the cleaned up form.

    Bill Mosca

    Tuesday, December 3, 2019 8:19 PM
  • Thanks for this Bill! Your explanation of code pages was very helpful, had yet to encounter that phrase in my studies of Access.
    Tuesday, December 3, 2019 8:35 PM