DoCmd.Close produces an error

[Paul]

I'm using some VBA code to open another form when you double click on a text
box in a continuous form. I'm also trying to close the first form by using
the DoCmd.Close method at the beginning of the double click event . While
this command usually works fine, in this particular case I keep getting the
following error message:

"The expression you entered refers to an object that is closed or
doesn't exist."

The thing that puzzles me about this is that I use DoCmd.Close in a number
of other forms in this application to navigate from one form to the other
(and closing the first form when the second one opens), but for some reason,
I can't do it in this form.

I am able to get the form to close if I put a command button in the form
footer of this continuous form, but then the user doesn't get the
convenience of double-clicking on a particular record to open the next form
to the same record.

Any ideas on why I can't get DoCmd.Close to work in the Double Click event
of a control in the detal section, and how I can get it to work as intended?

Thanks in advance,

Paul

 

[Arvin Meyer [MVP]]

Try this. Write a little standard module function like:

Public Function OpenClose(strFormToOpen As String, strFormToClose As String)

On Error Resume Next
DoCmd.OpenForm strFormToOpen
DoCmd.Close acForm, strFormToClose

End Function

Then in the double-click event of the textbox's property sheet, use:

=OpenClose("FormNameToOpen", "FormNameToClose")

 

[Paul]

Thanks, Arvin. The function you provided works great.

I couldn't get the function call to compile with the equal sign in front of
it, and it produces the Compile Error "Expected Line Number or Label or end
of statement." So I replaced it with the "Call" command and it works fine.

I also added an additional parameter for the link criteria:

Public Function OpenClose(strFormToOpen As String, strFormToClose As String,
stLinkCriteria As String)
On Error Resume Next
DoCmd.OpenForm strFormToOpen, , , stLinkCriteria
DoCmd.Close acForm, strFormToClose
End Function

and I call it with

Call OpenClose("frmActivity", "frm_supervisor_open", "[ProjectID]=" &
Me![ProjectID])


Thanks again for giving me a solution to the problem.

BTW, do you have any idea why the DoCmd.Close works in some forms but not in
others?

Paul

 

[Arvin Meyer [MVP]]

One should use the = sign in the property sheet, or the Call syntax in a
code window. Functions, since they return a value, can be called directly
from the property sheet, just like a macro. Further, if they are called from
the property sheet they are object oriented and will follow the button or
control if it is copied and pasted into another form. This is only true of
functions, not subs. For an example see my sample Calendar form database:

http://www.datastrat.com/Download/Calendar2K.zip

 

[Paul]

Thanks for the explanation, Arvin. And thanks for the calendar form - it's
a nice applet. Is it ok to use it in my applications?

Paul

 

[Arvin Meyer [MVP]]

Sure, that's what it's there for.