Controlling Access Switchboard

[chammock]

I have an Access app that I plan to convert to an mde file for users to
run without Access loaded. I have a Switchboard for the Main Menu. Is
there a global method to have the Switchboard be open if no other forms
are open. I have so many forms and I do a lot of opening and closing of
forms in code, sometimes in Normal view and sometimes in Design view.
So I was hoping to not have to embed logic all throughout my code to
get the Menu to be available when no other forms are loaded.
Thanks for any suggestions.

 

[Allen Browne]

Set the On Close property of each form to:
=Keep1Open([Form])
and each report to:
=Keep1Open([Report])
Then if no forms or reports are visible, it will open the switchboard again.


Public Function Keep1Open(objMe As Object)
On Error GoTo Err_Keep1Open
'Purpose: Open the Switchboard if nothing else is visible.
'Argument: The object being closed.
'Return: None.
'Usage: In the OnClose property of forms and reports:
' =Keep1Open([Form])
' =Keep1Open([Report])
Dim sName As String 'Name of the object being closed.
Dim lngObjType As Long 'acForm or acReport
Dim bFound As Boolean 'Flag not to open the switchboard.
Dim frm As Form 'an open form.
Dim rpt As Report 'an open report.

'Initialize
sName = objMe.Name
If TypeOf objMe Is Form Then
lngObjType = acForm
ElseIf TypeOf objMe Is Report Then
lngObjType = acReport
End If

'Any other visible forms?
For Each frm In Forms
If frm.Visible Then
If frm.Name <> sName Or lngObjType <> acForm Then
bFound = True
Exit For
End If
End If
Next

'Any other visible reports?
If Not bFound Then
For Each rpt In Reports
If rpt.Visible Then
If rpt.Name <> sName Or lngObjType <> acReport Then
bFound = True
Exit For
End If
End If
Next
End If

'If none found, open the switchboard.
If Not bFound Then
DoCmd.OpenForm "Switchboard"
End If

Exit_Keep1Open:
Set frm = Nothing
Set rpt = Nothing
Exit Function

Err_Keep1Open:
If Err.Number <> 2046& Then 'OpenForm is not available when closing
database.
Call LogError(Err.Number, Err.Description, conMod & ".Keep1Open()")
End If
Resume Exit_Keep1Open
End Function

 

[Guest]

Very clever, Allen. I am keeping a copy in case I ever need something like
this. I do have one question; however. I notice you make Keep1Open a
function rather than a sub even though it returns no value. My usual
practice is to use functions only when I expect to return a value and subs
when there is not value to return. Is there any advantage to using a
function rather than a sub and how do you make that decision?

Thanks.

Set the On Close property of each form to:
=Keep1Open([Form])
and each report to:
=Keep1Open([Report])
Then if no forms or reports are visible, it will open the switchboard again.


Public Function Keep1Open(objMe As Object)
On Error GoTo Err_Keep1Open
'Purpose: Open the Switchboard if nothing else is visible.
'Argument: The object being closed.
'Return: None.
'Usage: In the OnClose property of forms and reports:
' =Keep1Open([Form])
' =Keep1Open([Report])
Dim sName As String 'Name of the object being closed.
Dim lngObjType As Long 'acForm or acReport
Dim bFound As Boolean 'Flag not to open the switchboard.
Dim frm As Form 'an open form.
Dim rpt As Report 'an open report.

'Initialize
sName = objMe.Name
If TypeOf objMe Is Form Then
lngObjType = acForm
ElseIf TypeOf objMe Is Report Then
lngObjType = acReport
End If

'Any other visible forms?
For Each frm In Forms
If frm.Visible Then
If frm.Name <> sName Or lngObjType <> acForm Then
bFound = True
Exit For
End If
End If
Next

'Any other visible reports?
If Not bFound Then
For Each rpt In Reports
If rpt.Visible Then
If rpt.Name <> sName Or lngObjType <> acReport Then
bFound = True
Exit For
End If
End If
Next
End If

'If none found, open the switchboard.
If Not bFound Then
DoCmd.OpenForm "Switchboard"
End If

Exit_Keep1Open:
Set frm = Nothing
Set rpt = Nothing
Exit Function

Err_Keep1Open:
If Err.Number <> 2046& Then 'OpenForm is not available when closing
database.
Call LogError(Err.Number, Err.Description, conMod & ".Keep1Open()")
End If
Resume Exit_Keep1Open
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an Access app that I plan to convert to an mde file for users to
run without Access loaded. I have a Switchboard for the Main Menu. Is
there a global method to have the Switchboard be open if no other forms
are open. I have so many forms and I do a lot of opening and closing of
forms in code, sometimes in Normal view and sometimes in Design view.
So I was hoping to not have to embed logic all throughout my code to
get the Menu to be available when no other forms are loaded.
Thanks for any suggestions.

 

[Allen Browne]

In a property such as On Close, you can only use the name of a Function. A
sub will not do.

There is no logical difference between a sub and a function that returns no
value. IMHO, subs are an anachronism from the old BASIC days, and some
programming languages have no subs (functions only.)

I cannot recall the last time I created a Sub (other than the event
procedures Access uses.) You can almost always dream up something useful to
return, even if it no more than True if the function succeeds in its goal.

FWIW, I always use the Call keyword when calling subs and
functions-that-return-nothing. It standardizes the way you call them and
pass the parameters, and makes it very clear that this is a user-defined
routine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Very clever, Allen. I am keeping a copy in case I ever need something
like
this. I do have one question; however. I notice you make Keep1Open a
function rather than a sub even though it returns no value. My usual
practice is to use functions only when I expect to return a value and subs
when there is not value to return. Is there any advantage to using a
function rather than a sub and how do you make that decision?

Thanks.

Set the On Close property of each form to:
=Keep1Open([Form])
and each report to:
=Keep1Open([Report])
Then if no forms or reports are visible, it will open the switchboard
again.


Public Function Keep1Open(objMe As Object)
On Error GoTo Err_Keep1Open
'Purpose: Open the Switchboard if nothing else is visible.
'Argument: The object being closed.
'Return: None.
'Usage: In the OnClose property of forms and reports:
' =Keep1Open([Form])
' =Keep1Open([Report])
Dim sName As String 'Name of the object being closed.
Dim lngObjType As Long 'acForm or acReport
Dim bFound As Boolean 'Flag not to open the
switchboard.
Dim frm As Form 'an open form.
Dim rpt As Report 'an open report.

'Initialize
sName = objMe.Name
If TypeOf objMe Is Form Then
lngObjType = acForm
ElseIf TypeOf objMe Is Report Then
lngObjType = acReport
End If

'Any other visible forms?
For Each frm In Forms
If frm.Visible Then
If frm.Name <> sName Or lngObjType <> acForm Then
bFound = True
Exit For
End If
End If
Next

'Any other visible reports?
If Not bFound Then
For Each rpt In Reports
If rpt.Visible Then
If rpt.Name <> sName Or lngObjType <> acReport Then
bFound = True
Exit For
End If
End If
Next
End If

'If none found, open the switchboard.
If Not bFound Then
DoCmd.OpenForm "Switchboard"
End If

Exit_Keep1Open:
Set frm = Nothing
Set rpt = Nothing
Exit Function

Err_Keep1Open:
If Err.Number <> 2046& Then 'OpenForm is not available when
closing
database.
Call LogError(Err.Number, Err.Description, conMod &
".Keep1Open()")
End If
Resume Exit_Keep1Open
End Function

I have an Access app that I plan to convert to an mde file for users to
run without Access loaded. I have a Switchboard for the Main Menu. Is
there a global method to have the Switchboard be open if no other forms
are open. I have so many forms and I do a lot of opening and closing of
forms in code, sometimes in Normal view and sometimes in Design view.
So I was hoping to not have to embed logic all throughout my code to
get the Menu to be available when no other forms are loaded.
Thanks for any suggestions.

 

[Guest]

So you are saying you put the function name directly in the text box of the
event in design view rather than allowing it to create its own sub?

I follow the same practice regarding using the Call statement for Subs. As
to "Old Basic", I guess I am guilty of that, having coded various versions of
Basic for well over 20 years. In fact, at one time there was no such thing
as a function, there was only the GoSub which referenced a line number.

I see you point regarding returning something useful, even if not at the
first writing.

Thanks for your insight.

In a property such as On Close, you can only use the name of a Function. A
sub will not do.

There is no logical difference between a sub and a function that returns no
value. IMHO, subs are an anachronism from the old BASIC days, and some
programming languages have no subs (functions only.)

I cannot recall the last time I created a Sub (other than the event
procedures Access uses.) You can almost always dream up something useful to
return, even if it no more than True if the function succeeds in its goal.

FWIW, I always use the Call keyword when calling subs and
functions-that-return-nothing. It standardizes the way you call them and
pass the parameters, and makes it very clear that this is a user-defined
routine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Very clever, Allen. I am keeping a copy in case I ever need something
like
this. I do have one question; however. I notice you make Keep1Open a
function rather than a sub even though it returns no value. My usual
practice is to use functions only when I expect to return a value and subs
when there is not value to return. Is there any advantage to using a
function rather than a sub and how do you make that decision?

Thanks.

Set the On Close property of each form to:
=Keep1Open([Form])
and each report to:
=Keep1Open([Report])
Then if no forms or reports are visible, it will open the switchboard
again.


Public Function Keep1Open(objMe As Object)
On Error GoTo Err_Keep1Open
'Purpose: Open the Switchboard if nothing else is visible.
'Argument: The object being closed.
'Return: None.
'Usage: In the OnClose property of forms and reports:
' =Keep1Open([Form])
' =Keep1Open([Report])
Dim sName As String 'Name of the object being closed.
Dim lngObjType As Long 'acForm or acReport
Dim bFound As Boolean 'Flag not to open the
switchboard.
Dim frm As Form 'an open form.
Dim rpt As Report 'an open report.

'Initialize
sName = objMe.Name
If TypeOf objMe Is Form Then
lngObjType = acForm
ElseIf TypeOf objMe Is Report Then
lngObjType = acReport
End If

'Any other visible forms?
For Each frm In Forms
If frm.Visible Then
If frm.Name <> sName Or lngObjType <> acForm Then
bFound = True
Exit For
End If
End If
Next

'Any other visible reports?
If Not bFound Then
For Each rpt In Reports
If rpt.Visible Then
If rpt.Name <> sName Or lngObjType <> acReport Then
bFound = True
Exit For
End If
End If
Next
End If

'If none found, open the switchboard.
If Not bFound Then
DoCmd.OpenForm "Switchboard"
End If

Exit_Keep1Open:
Set frm = Nothing
Set rpt = Nothing
Exit Function

Err_Keep1Open:
If Err.Number <> 2046& Then 'OpenForm is not available when
closing
database.
Call LogError(Err.Number, Err.Description, conMod &
".Keep1Open()")
End If
Resume Exit_Keep1Open
End Function

I have an Access app that I plan to convert to an mde file for users to
run without Access loaded. I have a Switchboard for the Main Menu. Is
there a global method to have the Switchboard be open if no other forms
are open. I have so many forms and I do a lot of opening and closing of
forms in code, sometimes in Normal view and sometimes in Design view.
So I was hoping to not have to embed logic all throughout my code to
get the Menu to be available when no other forms are loaded.
Thanks for any suggestions.

 

[Allen Browne]

Yes. Putting the function name in the report's property setting means the
report does not need to have a module (i.e. it is a lightweight report.)

 

[Guest]

Light Bulb goes on here!

So the same would be good for a form?

I think I am headed that way. I have a form that has extensive code in it,
so I put all the heavy logic in a standard module and call the main logic
routine from a command click. It does load a lot faster.

 

[chammock]

Allen: Thanks so much. This seems to be working very nicely. I do not
have an error log. It might be nice if I incorporated it as well. Would
you mind sharing the code for the LogError function?

 

[Allen Browne]

This link explains the LogError() code:
http://allenbrowne.com/ser-23a.html

BTW, there is a really neat little set of utilities from www.mztools.com.
One of the things it does is allow you to define a skeleton error handler
that you can drop into any procedure by clicking a toolbar button. I have
this set up to drop in the call to LogError().

 

[Allen Browne]

Yes, you can do this for forms too.

But in practice, the only lightweight forms I end up with are display-only.
If you want to pass arguments such as the error number from Form_Error or
the KeyAscii of a KeyPress event, you have to call your generic function
from inside the event procedure.

So while I love the concept of light-weight objects (reducing memory usage,
less chance of corruption), I find that most forms need modules, while most
reports don't.