Error 2046 with DoCmd.RunCommand acCmdFreezeColumn

I am bringing up a subform as a datasheet inside a form.  I would like to freeze the first three columns.  I do this using code like this in the Form_Open event:

  DoCmd.RunCommand acCmdFreezeColumn
  DoCmd.RunCommand acCmdFreezeColumn
  DoCmd.RunCommand acCmdFreezeColumn

This used to work.  However, I added a GotFocus event to Col3 and now after executing that code, the next time it tries to execute the DoCmd statement I get Error 2046 "command isn't available".

I have tried a number of things to try and get this to work:
1) Trapped and ignored the error:  The problem is that the columns don't come up in the right order anymore.
2) Set a boolean value so that I could skip all of the code in the GotFocus event during the Open_Form event:  This doesn't work.  It seems that just going to the event even if there is no code there causes the problem.
3) Added a pause:  even with pause as long as 10 seconds this didn't work.

Is there a solution?  The only things I can think of are:
1) Is there a way to deactivate an event completely and then reactivate it?
2) Is there a way to create the event programatically?  Then I could create it after I execute the other code.

Who is Participating?
faz136Connect With a Mentor Commented:
It may work if you remove the [Event Procedure] tag from the got focus line of the properties, then at the end of your code put the line col3.OnGotFocus = "[Event Procedure]"
I generally use this to assign functions to events at runtime
GreymanMSCConnect With a Mentor Commented:
faz136 has a good idea.  To prevent the on got focus event from running when you set focus to a control simply set the property to "".  Don't forget to put it back to "[Event Procedure]" when you are done.


  Me.Col3.OnGotFocus = ""
  DoCmd.RunCommand acCmdFreezeColumn
  Me.Col3.OnGotFocus = "[Event Procedure]"
dastrwAuthor Commented:
I'm having trouble with the syntax so when I set focus to the control it can't find the 'macro' that I created.

If my event is called Col3_GotFocus() what should I put between the double-quotes in the assignment statement?

I tried:

Me.Col3.OnGotFocus = "Col3_GotFocus"
Me.Col3.OnGotFocus = "Col3_GotFocus()"
Me.Col3.OnGotFocus = "[Col3_GotFocus]"

None of these worked.
dastrwAuthor Commented:

I put the words [Event Procedure] in and it worked!

This is a great trick that I will be able to use in a lot of places.  Thanks for the help!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.