none
Run-time error 2467 received by one app user and not by another RRS feed

  • Question

  • I just completed upsizing an old legacy Access application to SQL Server as the back-end.  The application piece seems to be working fine - except, one user is getting the message "Run-time error '2467'" when she opens a certain form that contains a subform.   This error does not occur when I run the application.  We're both running Windows 7 SP1, although I'm running a 64-bit version; regardless, I'm running 32-bit Office 2010.

    The code that Access highlights when you click the Debug button on the error message window is:

    Me!sfrmQueryTheData.Form.RecordSource = "Select * from queQueryTheData where 0 <> 0
    The code is in the main form's Open event handler.  The programmer who wrote the application apparently put it in there to clear the subform of any records prior to the user selecting search criteria on the main form and pressing the Query button.  As noted in my Title, the main form opens just fine when I open it, but when a colleague runs the app and opens that particular form (via a button on a menu), she gets the aforementioned "Run-time error '2467'  The expression you entered refers to an object that is closed or doesn't exist".  Assuming the object in question is the subform, it certainly exists, and it seems to open just fine when I open the main form.  Any ideas would be greatly appreciated.
    Wednesday, July 12, 2017 5:57 PM

Answers

  • Found the answer (no thanks to the error message).  I had the problem user open the subform directly, upon which she got an access (as opposed to Access) error message saying she did not have authority to SELECT on one of the form's query's underlying tables.  Once I granted the SELECT permission to the table, the 2467 error message was no more.

    Thanks for the suggestions.

    • Edited by Tim Peters Wednesday, July 12, 2017 7:20 PM
    • Marked as answer by Tim Peters Wednesday, July 12, 2017 7:20 PM
    Wednesday, July 12, 2017 7:19 PM

All replies

  • Hi Tim,

    Just a guess but try moving the code from the Open event to the Load event to see if it makes any difference.

    Wednesday, July 12, 2017 6:33 PM
  • Also, make sure their Office installation is up-to-date, validate that the VBA project compiles without any errors on their system.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, July 12, 2017 6:41 PM
  • Worth a try, but it did not resolve the issue.  Thanks anyway.
    Wednesday, July 12, 2017 6:44 PM
  • Worth a try, but it did not resolve the issue.  Thanks anyway.

    Okay, I haven't given up. One more try:

    Move the code from the Open or Load event to the Timer event but add a line to reset the Timer Interval=0 and then set the Timer Interval, in Design View, to say 10 or 100.

    Hope it helps...

    Wednesday, July 12, 2017 6:56 PM
  • Found the answer (no thanks to the error message).  I had the problem user open the subform directly, upon which she got an access (as opposed to Access) error message saying she did not have authority to SELECT on one of the form's query's underlying tables.  Once I granted the SELECT permission to the table, the 2467 error message was no more.

    Thanks for the suggestions.

    • Edited by Tim Peters Wednesday, July 12, 2017 7:20 PM
    • Marked as answer by Tim Peters Wednesday, July 12, 2017 7:20 PM
    Wednesday, July 12, 2017 7:19 PM
  • Good to hear and thank you for sharing your final solution.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, July 12, 2017 7:26 PM
  • Hi Tim,

    Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, July 12, 2017 7:26 PM
  • It doesn't sound like there's a good reason to send that useless SELECT off to the server in the first place.  Could you not instead use 

    Me!sfrmQueryTheData.Form.RecordSource = vbNullString

    ?

    -Bruce

    Wednesday, July 12, 2017 7:57 PM
  • Hi Bruce,

    I didn't write this app, and don't know that much about Access or, for that matter, VB coding; I was just trying to get the tables out of Access and into SQL for various reasons (easy for me to query tables, backup with other SQL databases, etc.) - so I was looking to keep the app functioning with as few changes as possible.  That said, I thought it looked like a strange way to ensure an empty subform, and I'll give your suggestion a try and let you know how it goes.  Thanks for that.

    Thursday, July 13, 2017 2:58 PM
  • Okay, tried setting the recordsource to vbNullString.  The result is that the first row in the subform is displayed with #Name? error indicators in each column.

     
    Thursday, July 13, 2017 3:05 PM
  • ...which makes perfect sense now that I think about it. The code using the "select...where 0<>0" seems to work fine for you and probably isn't worth changing but apparently it does invoke a round trip to the server, hence your permission problem.  If performance was an issue you could probably replace "select...where 0<>0" with the SQL string "select null as Ticket, null as UserID, null as CallDescription, ..., null as OpenDate" with no FROM clause which should eliminate Access trying to send it to the server.  Sorry for steering you wrong!

    -Bruce

    Thursday, July 13, 2017 11:35 PM
  • No worries, I appreciate the help.  Using the SELECT with no FROM clause is a good idea that I've implemented (wish I could do SELECT with no FROM in DB2).
    Friday, July 14, 2017 10:58 AM