Function call

[BruceM]

I have several forms that need to be opened from several locations within a
database. When the form is opened, the current form is to be closed. To
streamline the process I have created code in a public module to open each
form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the property
sheet)
=DeptForm()


To close the current form I have another function in the public module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if so I
can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or something
like that) when I compile the code. However, attempts to declare the
variable, either on the module level or within the DeptForm function, do not
improve the situation. The code seems to compile OK, but then I get Error
91 as a run-time error when I click the command button.

I can run both functions from the Click event line in the form's property
sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that wouldn't
answer my main questions either, which are whether I am taking a generally
reasonable approach, and if so how best to make it work.

 

[Stefan Hoffmann]

hi Bruce,

Public Function DeptForm()
DoCmd.OpenForm "frmDept"
End Function
I call this in a command button's Click event (on the line in the property
sheet)
=DeptForm()

I'd prefer a more flexible solution:

Public Function FormOpen(AFormName As String) As Boolean

On Local Error Resume Next
DoCmd.OpenForm AFormName

End Function

To close the current form I have another function in the public module:
Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function

Public Function FormClose(AFormName As String) As Boolean

On Local Error Resume Next
DoCmd.Close acForm, AFormName

End Function

I can run both functions from the Click event line in the form's property
sheet:
=DeptForm()=CloseForm([Form])

So you can use =FormClose(Form.Name)

mfG
--> stefan <--

 

[strive4peace]

Open or Close form with public functions
---


Hi Bruce,

what is the code to assign DeptForm to frm? If you are in the code
behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~

as for your CloseForm code, why not just send the form name instead of a
form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass
the name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[BruceM]

Thanks for your reply. I either don't fully understand some of your
questions, or I did not explain myself well enough in the first place. More
responses inline. I also replied to Stefan's response. I'm never quite
sure if I should just choose a thread and reply to everything there, or
reply individually.

Open or Close form with public functions

what is the code to assign DeptForm to frm?

I guess that's part of the problem. I know what I would like to do, and I'm
trying to figure out how to make it work. Is part of the problem that I
haven't assigned DeptForm (or any other form name) to frm in

If you are in the code behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~

Two reasons. I prefer to use error handling for all code. Perhaps it's
overkill, but there it is. If I use the code above from a command button's
Click event, I end up needing to use error handling on every form that has a
Close button, rather than using error handling just once in the public
function. Having said that, I like the part where I can just use Me.Name
rather than dim strFormName as String, then another line to define
strFormName.

With CloseForm in a public module, and with =CloseForm([Form].[Name]) in the
Click event line on a command button's property sheet, all I need to do is
copy the command button to any form I want to close. This is especially
helpful with navigation buttons and other buttons such as a Close button
that are present on many forms.

as for your CloseForm code, why not just send the form name instead of a
form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass the
name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function

That is exactly what I want to do. At the same time, I want to close the
form that is currently open (the one on which the command button appears).
Can I call CloseForm from within the OpenForm function?

As to your several questions "why not do this or that", the answer is
because I didn't know I could until now

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have several forms that need to be opened from several locations within
a database. When the form is opened, the current form is to be closed.
To streamline the process I have created code in a public module to open
each form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()


To close the current form I have another function in the public module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if so I
can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or
something like that) when I compile the code. However, attempts to
declare the variable, either on the module level or within the DeptForm
function, do not improve the situation. The code seems to compile OK,
but then I get Error 91 as a run-time error when I click the command
button.

I can run both functions from the Click event line in the form's property
sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that
wouldn't answer my main questions either, which are whether I am taking a
generally reasonable approach, and if so how best to make it work.

 

[BruceM]

Thanks for the reply. I have to admit I don't quite see how it works,
although it does. I like that I don't have to spell out the form name each
time, which was part of what I sought. The other thing is, every time I
open a form I want to close the current form. Can I call CloseForm from
within OpenForm, or do I need to use two separate function calls from within
the form itself?
By the way, I also replied to Crystal's response. Since the two of you made
somewhat different responses (I think) I decided to reply individually. One
question about the difference is that you suggested:

Public Function FormOpen(AFormName As String) As Boolean

whereas Crystal left out "As Boolean" and didn't use the On Local Error line
of code. Both seem to work. What is the difference between the approaches?

hi Bruce,

Public Function DeptForm()
DoCmd.OpenForm "frmDept"
End Function
I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()

I'd prefer a more flexible solution:

Public Function FormOpen(AFormName As String) As Boolean

On Local Error Resume Next
DoCmd.OpenForm AFormName

End Function

To close the current form I have another function in the public module:
Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function

Public Function FormClose(AFormName As String) As Boolean

On Local Error Resume Next
DoCmd.Close acForm, AFormName

End Function

I can run both functions from the Click event line in the form's property
sheet:
=DeptForm()=CloseForm([Form])

So you can use =FormClose(Form.Name)

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Bruce,

I like that I don't have to spell out the form name each
time, which was part of what I sought.

You do spell out the form name everytime, but you use a shorter name,
which maps 1:1 to your form names.

The other thing is, every time I
open a form I want to close the current form. Can I call CloseForm from
within OpenForm, or do I need to use two separate function calls from within
the form itself?

I'm using in my open procedure

If FormIsLoaded(AFormName) Then _
FormClose AFormName
DoCmd.OpenForm AFormName

So the answer is yes.

Public Function FormOpen(AFormName As String) As Boolean
whereas Crystal left out "As Boolean" and didn't use the On Local Error line
of code. Both seem to work. What is the difference between the approaches?

I'm used to strictly typed programming, so i declare everthing explicitly.

Public Function FormOpen()

is declared implicitly As Variant, which i don't like.

The form open or close may fail. In a runtime environment this would
cause the application to terminate, if an error is not handled.
Therefore the error handler.


mfG
--> stefan <--

 

[BruceM]

I appreciate your taking the time to reply, but I seem to be moving further
from where I want to be.

To clear up a question, I don't see the difference between:

Public Function FormOpen(AFormName As String) As Boolean
and
Public Function FormOpen(AFormName As String)

I'm not saying there is no difference, but rather that I don't understand
the difference. I did not mean to ask about using no variable declaration
at all.

I failed to explain clearly that I have a button to open frmDept, and
another to open frmProcess, and so forth. I need to identify the form that
is intended to open when I click the command button, which is why I used a
separate function to open each form. If I use a generic function I don't
see how to pass the form name to the function, unless the form name is the
Tag property of the command button or something like that, and I pass that
to the generic OpenForm function.

I have realized that there is really no need to call the function to close
the current form when I open a new form. Instead, I can just add a line of
code:

Public Function DeptForm(strFormName As String)

DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmDept"

End Function

I use error handling, but I left it out for ease of reading.

I call the function by using:
=DeptForm([Form].[Name])
in the Click event line of the property sheet.

I don't mean to come across as contrary here. Rather, I think I did not
explain the situation adequately in the first place. I don't doubt there is
a better way of accomplishing my aims, but the way I am doing this seems to
be OK.

 

[Stefan Hoffmann]

hi Bruce,

To clear up a question, I don't see the difference between:
Public Function FormOpen(AFormName As String) As Boolean
and
Public Function FormOpen(AFormName As String)

The second is implicitly declaread as variant. It is the same as when
you would write

Public Function FormOpen(AFormName As String) As Variant

I have realized that there is really no need to call the function to close
the current form when I open a new form. Instead, I can just add a line of
code:

Public Function DeptForm(strFormName As String)
DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmDept"
End Function

That is true.

I use error handling, but I left it out for ease of reading.

Okay, but this is often not the case.

I don't mean to come across as contrary here. Rather, I think I did not
explain the situation adequately in the first place. I don't doubt there is
a better way of accomplishing my aims, but the way I am doing this seems to
be OK.

You have explained your problem quite well, but on the otherside, my
English is quite bad today, so i wasn't really able to explain what had
in mind.

Just take a closer look at these two calls:

=FormOpen("frmDept")
=DeptForm([Form].[Name])

The first one needs inline error handling, if the form doesn't exists or
was renamed.
The second call needs form level error handling, if the function is
renamed or no longer existent.
Both are not really good to refacture. But i'd just prefer the first one.

mfG
--> stefan <--

 

[BruceM]

I think I see. The function itself can be generic (one function for opening
all forms), but the function call on the property sheet includes the form
name. That makes sense. I won't be able to try it for a while, as
something else is demanding my attention at the moment, but thank you for
making the extra effort to explain the things that were puzzling me.

hi Bruce,

To clear up a question, I don't see the difference between:
Public Function FormOpen(AFormName As String) As Boolean
and
Public Function FormOpen(AFormName As String)

The second is implicitly declaread as variant. It is the same as when you
would write

Public Function FormOpen(AFormName As String) As Variant

I have realized that there is really no need to call the function to
close the current form when I open a new form. Instead, I can just add a
line of code:

Public Function DeptForm(strFormName As String)
DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmDept"
End Function

That is true.

I use error handling, but I left it out for ease of reading.

Okay, but this is often not the case.

I don't mean to come across as contrary here. Rather, I think I did not
explain the situation adequately in the first place. I don't doubt there
is a better way of accomplishing my aims, but the way I am doing this
seems to be OK.

You have explained your problem quite well, but on the otherside, my
English is quite bad today, so i wasn't really able to explain what had in
mind.

Just take a closer look at these two calls:

=FormOpen("frmDept")
=DeptForm([Form].[Name])

The first one needs inline error handling, if the form doesn't exists or
was renamed.
The second call needs form level error handling, if the function is
renamed or no longer existent.
Both are not really good to refacture. But i'd just prefer the first one.

mfG
--> stefan <--

 

[strive4peace]

Hi Bruce,

actually, I think Stephan and I are on the same track -- passing a name
instead of a form object (the only way to do that is in the code behind
your form and pass Me -- then you might as well put the code right there).

I assumed you would put something like this in an event property itself
and there is no way to handle a return value.

=CloseForm("Formname")

to be the most flexible, however, what Stephan told you would be better
to use


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your reply. I either don't fully understand some of your
questions, or I did not explain myself well enough in the first place. More
responses inline. I also replied to Stefan's response. I'm never quite
sure if I should just choose a thread and reply to everything there, or
reply individually.

Open or Close form with public functions

what is the code to assign DeptForm to frm?

I guess that's part of the problem. I know what I would like to do, and I'm
trying to figure out how to make it work. Is part of the problem that I
haven't assigned DeptForm (or any other form name) to frm in

If you are in the code behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~

Two reasons. I prefer to use error handling for all code. Perhaps it's
overkill, but there it is. If I use the code above from a command button's
Click event, I end up needing to use error handling on every form that has a
Close button, rather than using error handling just once in the public
function. Having said that, I like the part where I can just use Me.Name
rather than dim strFormName as String, then another line to define
strFormName.

With CloseForm in a public module, and with =CloseForm([Form].[Name]) in the
Click event line on a command button's property sheet, all I need to do is
copy the command button to any form I want to close. This is especially
helpful with navigation buttons and other buttons such as a Close button
that are present on many forms.

as for your CloseForm code, why not just send the form name instead of a
form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass the
name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function

That is exactly what I want to do. At the same time, I want to close the
form that is currently open (the one on which the command button appears).
Can I call CloseForm from within the OpenForm function?

As to your several questions "why not do this or that", the answer is
because I didn't know I could until now

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have several forms that need to be opened from several locations within
a database. When the form is opened, the current form is to be closed.
To streamline the process I have created code in a public module to open
each form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()


To close the current form I have another function in the public module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if so I
can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or
something like that) when I compile the code. However, attempts to
declare the variable, either on the module level or within the DeptForm
function, do not improve the situation. The code seems to compile OK,
but then I get Error 91 as a run-time error when I click the command
button.

I can run both functions from the Click event line in the form's property
sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that
wouldn't answer my main questions either, which are whether I am taking a
generally reasonable approach, and if so how best to make it work.

 

[BruceM]

Thanks for replying. When you say "the code behind the form" I expect you
mean the form's code module rather than using the event property. Using the
event property is a convenience, as I can just copy and paste from one form
to the other (I do this with custom navigation buttons on most forms that
use them). When I copy buttons that use the event procedure code I need to
drill down through the VBA editor in order to link the event to the code.
At least, that's the only way I know to do that.
I have to admit I'm not sure I completely understand what has been suggested
in this thread. Since each button needs to open a different form and close
the current form, I ended up with this:

Public Function DeptForm(strFormName As String) As Boolean

DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmRTY_SelCat"

End Function

There is error handling, but I left it out for ease of reading. In the
command button's click event property:
=DeptForm([Form].[Name])

It accomplishes what I need. I'm not quite sure it's the best way, but
there it is. Thanks again for your help.

Hi Bruce,

actually, I think Stephan and I are on the same track -- passing a name
instead of a form object (the only way to do that is in the code behind
your form and pass Me -- then you might as well put the code right there).

I assumed you would put something like this in an event property itself
and there is no way to handle a return value.

=CloseForm("Formname")

to be the most flexible, however, what Stephan told you would be better to
use


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your reply. I either don't fully understand some of your
questions, or I did not explain myself well enough in the first place.
More responses inline. I also replied to Stefan's response. I'm never
quite sure if I should just choose a thread and reply to everything
there, or reply individually.

Open or Close form with public functions

what is the code to assign DeptForm to frm?

I guess that's part of the problem. I know what I would like to do, and
I'm trying to figure out how to make it work. Is part of the problem
that I haven't assigned DeptForm (or any other form name) to frm in

If you are in the code behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~

Two reasons. I prefer to use error handling for all code. Perhaps it's
overkill, but there it is. If I use the code above from a command
button's Click event, I end up needing to use error handling on every
form that has a Close button, rather than using error handling just once
in the public function. Having said that, I like the part where I can
just use Me.Name rather than dim strFormName as String, then another line
to define strFormName.

With CloseForm in a public module, and with =CloseForm([Form].[Name]) in
the Click event line on a command button's property sheet, all I need to
do is copy the command button to any form I want to close. This is
especially helpful with navigation buttons and other buttons such as a
Close button that are present on many forms.

as for your CloseForm code, why not just send the form name instead of a
form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass
the name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function

That is exactly what I want to do. At the same time, I want to close the
form that is currently open (the one on which the command button
appears). Can I call CloseForm from within the OpenForm function?

As to your several questions "why not do this or that", the answer is
because I didn't know I could until now

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
I have several forms that need to be opened from several locations
within a database. When the form is opened, the current form is to be
closed. To streamline the process I have created code in a public
module to open each form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()


To close the current form I have another function in the public module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if so
I can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or
something like that) when I compile the code. However, attempts to
declare the variable, either on the module level or within the DeptForm
function, do not improve the situation. The code seems to compile OK,
but then I get Error 91 as a run-time error when I click the command
button.

I can run both functions from the Click event line in the form's
property sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that
wouldn't answer my main questions either, which are whether I am taking
a generally reasonable approach, and if so how best to make it work.

 

[strive4peace]

Hi Bruce,

Code that is stored with a form or report is called a Class Module.
Code that is stored on an independent module sheet is a Standard Module
(also called a general module).

Code-Behind-Form (CBF) is the Class module for a particular form and can
only be seen by that form.

when you use [Event Procedure], the code is stored in the form's class
module. When you use a function name in an event property, the function
can be in the code behind the form or in a general module. It would be
referenced like this:
=FunctionName([parameter1[, parameter2[, parameter3]]])
where parameters are optional, depending on the function declaration

Like you, I like to use the event property to assign a function rather
than define Event Procedures.

I actually did not know about using [Form] in the event procedure until
now (thank you and thanks, Stephan) -- I have always gone into code
behind the form and used Me. This is very handy to know! That may be
one reason why my previous response was confusing, sorry.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for replying. When you say "the code behind the form" I expect you
mean the form's code module rather than using the event property. Using the
event property is a convenience, as I can just copy and paste from one form
to the other (I do this with custom navigation buttons on most forms that
use them). When I copy buttons that use the event procedure code I need to
drill down through the VBA editor in order to link the event to the code.
At least, that's the only way I know to do that.
I have to admit I'm not sure I completely understand what has been suggested
in this thread. Since each button needs to open a different form and close
the current form, I ended up with this:

Public Function DeptForm(strFormName As String) As Boolean

DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmRTY_SelCat"

End Function

There is error handling, but I left it out for ease of reading. In the
command button's click event property:
=DeptForm([Form].[Name])

It accomplishes what I need. I'm not quite sure it's the best way, but
there it is. Thanks again for your help.

Hi Bruce,

actually, I think Stephan and I are on the same track -- passing a name
instead of a form object (the only way to do that is in the code behind
your form and pass Me -- then you might as well put the code right there).

I assumed you would put something like this in an event property itself
and there is no way to handle a return value.

=CloseForm("Formname")

to be the most flexible, however, what Stephan told you would be better to
use


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your reply. I either don't fully understand some of your
questions, or I did not explain myself well enough in the first place.
More responses inline. I also replied to Stefan's response. I'm never
quite sure if I should just choose a thread and reply to everything
there, or reply individually.

Open or Close form with public functions
what is the code to assign DeptForm to frm?
I guess that's part of the problem. I know what I would like to do, and
I'm trying to figure out how to make it work. Is part of the problem
that I haven't assigned DeptForm (or any other form name) to frm in

If you are in the code behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~
Two reasons. I prefer to use error handling for all code. Perhaps it's
overkill, but there it is. If I use the code above from a command
button's Click event, I end up needing to use error handling on every
form that has a Close button, rather than using error handling just once
in the public function. Having said that, I like the part where I can
just use Me.Name rather than dim strFormName as String, then another line
to define strFormName.

With CloseForm in a public module, and with =CloseForm([Form].[Name]) in
the Click event line on a command button's property sheet, all I need to
do is copy the command button to any form I want to close. This is
especially helpful with navigation buttons and other buttons such as a
Close button that are present on many forms.

as for your CloseForm code, why not just send the form name instead of a
form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass
the name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function
That is exactly what I want to do. At the same time, I want to close the
form that is currently open (the one on which the command button
appears). Can I call CloseForm from within the OpenForm function?

As to your several questions "why not do this or that", the answer is
because I didn't know I could until now

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
I have several forms that need to be opened from several locations
within a database. When the form is opened, the current form is to be
closed. To streamline the process I have created code in a public
module to open each form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()


To close the current form I have another function in the public module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if so
I can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or
something like that) when I compile the code. However, attempts to
declare the variable, either on the module level or within the DeptForm
function, do not improve the situation. The code seems to compile OK,
but then I get Error 91 as a run-time error when I click the command
button.

I can run both functions from the Click event line in the form's
property sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that
wouldn't answer my main questions either, which are whether I am taking
a generally reasonable approach, and if so how best to make it work.

 

[Stefan Hoffmann]

hi Crystal,

I actually did not know about using [Form] in the event procedure until
now (thank you and thanks, Stephan) -- I have always gone into code
behind the form and used Me. This is very handy to know!

I use it for gerneral functions on buttons like

=FormClose([Form].[Name])
=FormRequery([Form])
=ReportNoData([Report])

This makes reading the code behind a lot easier, as there is none.)


mfG
--> stefan <--

 

[BruceM]

So that's what Class and Standard mean. I've sometimes wondered.
Using [Form] in the event procedure is definitely cool, isn't it? Thanks,
Stefan. I sometimes get more out of answers (and from reading postings)
than I expected.

Hi Bruce,

Code that is stored with a form or report is called a Class Module. Code
that is stored on an independent module sheet is a Standard Module (also
called a general module).

Code-Behind-Form (CBF) is the Class module for a particular form and can
only be seen by that form.

when you use [Event Procedure], the code is stored in the form's class
module. When you use a function name in an event property, the function
can be in the code behind the form or in a general module. It would be
referenced like this:
=FunctionName([parameter1[, parameter2[, parameter3]]])
where parameters are optional, depending on the function declaration

Like you, I like to use the event property to assign a function rather
than define Event Procedures.

I actually did not know about using [Form] in the event procedure until
now (thank you and thanks, Stephan) -- I have always gone into code behind
the form and used Me. This is very handy to know! That may be one reason
why my previous response was confusing, sorry.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for replying. When you say "the code behind the form" I expect
you mean the form's code module rather than using the event property.
Using the event property is a convenience, as I can just copy and paste
from one form to the other (I do this with custom navigation buttons on
most forms that use them). When I copy buttons that use the event
procedure code I need to drill down through the VBA editor in order to
link the event to the code. At least, that's the only way I know to do
that.
I have to admit I'm not sure I completely understand what has been
suggested in this thread. Since each button needs to open a different
form and close the current form, I ended up with this:

Public Function DeptForm(strFormName As String) As Boolean

DoCmd.Close acForm, (strFormName)
DoCmd.OpenForm "frmRTY_SelCat"

End Function

There is error handling, but I left it out for ease of reading. In the
command button's click event property:
=DeptForm([Form].[Name])

It accomplishes what I need. I'm not quite sure it's the best way, but
there it is. Thanks again for your help.

Hi Bruce,

actually, I think Stephan and I are on the same track -- passing a name
instead of a form object (the only way to do that is in the code behind
your form and pass Me -- then you might as well put the code right
there).

I assumed you would put something like this in an event property itself
and there is no way to handle a return value.

=CloseForm("Formname")

to be the most flexible, however, what Stephan told you would be better
to use


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
Thanks for your reply. I either don't fully understand some of your
questions, or I did not explain myself well enough in the first place.
More responses inline. I also replied to Stefan's response. I'm never
quite sure if I should just choose a thread and reply to everything
there, or reply individually.

Open or Close form with public functions
what is the code to assign DeptForm to frm?
I guess that's part of the problem. I know what I would like to do,
and I'm trying to figure out how to make it work. Is part of the
problem that I haven't assigned DeptForm (or any other form name) to
frm in

If you are in the code behind your form, you can use Me

'~~~~~
CloseForm Me
'~~~~~

why not just put this in the code behind the form to close it?

'~~~~~
DoCmd.Close acForm, me.Name
'~~~~~
Two reasons. I prefer to use error handling for all code. Perhaps
it's overkill, but there it is. If I use the code above from a command
button's Click event, I end up needing to use error handling on every
form that has a Close button, rather than using error handling just
once in the public function. Having said that, I like the part where I
can just use Me.Name rather than dim strFormName as String, then
another line to define strFormName.

With CloseForm in a public module, and with =CloseForm([Form].[Name])
in the Click event line on a command button's property sheet, all I
need to do is copy the command button to any form I want to close.
This is especially helpful with navigation buttons and other buttons
such as a Close button that are present on many forms.

as for your CloseForm code, why not just send the form name instead of
a form object reference?

'~~~~~
Public Function CloseForm(frmName as string)
DoCmd.Close acForm, frmName
end function
'~~~~~

also, rather than creating a function to open each form, why not pass
the name of the form?

'------------------------------------ Open_Form
Function open_Form(pFormname As String)
'Open the the passed Form
'example useage: OnClick event of a command button
' = Open_Form("Addresses")
On Error GoTo Proc_Err
DoCmd.OpenForm pFormname
Proc_Exit:
Exit Function
Proc_Err:
MsgBox Err.Number & " " & Err.Description _
, , "Cannot open " & pFormname
goto Proc_Exit
End Function
That is exactly what I want to do. At the same time, I want to close
the form that is currently open (the one on which the command button
appears). Can I call CloseForm from within the OpenForm function?

As to your several questions "why not do this or that", the answer is
because I didn't know I could until now

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



BruceM wrote:
I have several forms that need to be opened from several locations
within a database. When the form is opened, the current form is to
be closed. To streamline the process I have created code in a public
module to open each form:

Public Function DeptForm()

DoCmd.OpenForm "frmDept"

End Function


I call this in a command button's Click event (on the line in the
property sheet)
=DeptForm()


To close the current form I have another function in the public
module:

Public Function CloseForm(frm as Form)

Dim strForm As String
strForm = frm.Name
DoCmd.Close acForm, strForm

End Function


I had thought I could call CloseForm from within DeptForm(), but if
so I can't find the syntax, or am doing something else wrong.
If I do:
Call CloseForm(frm)
I get Error 91, Object Variable or With Block Variable not set (or
something like that) when I compile the code. However, attempts to
declare the variable, either on the module level or within the
DeptForm function, do not improve the situation. The code seems to
compile OK, but then I get Error 91 as a run-time error when I click
the command button.

I can run both functions from the Click event line in the form's
property sheet:
=DeptForm()=CloseForm([Form])

I expect I could call both from the Click event VBA code, but that
wouldn't answer my main questions either, which are whether I am
taking a generally reasonable approach, and if so how best to make it
work.