Run Function from Switchboard

[CJ]

Hi Groupies

I am not very good with code so I am not sure what the error message is
referring to:

I have created a function and I would like it to run when a user pushes a
button on the switchboard. With the switchboard manager, I used the command
to make code run and then entered the name of my function as ExpiryCheck().
When I push the button, I get the message "There was an error executing the
command"......very helpful!!

I have used this code before, but only via a command button on a form, never
through the switchboard.
I think that perhaps my code is not declared properly.....it is as follows:

Public Function ExpiryCheck()

Dim stDocName As String

'Expiry Check takes a while, display form while waiting
DoCmd.OpenForm "frmExpiryStatus"

'Expiry Check
stDocName = "rptExpiry"
DoCmd.RepaintObject acForm, "frmExpiryStatus"

If IsNull(DLookup("IDNumber", "qryExpiry")) Then
DoCmd.Close acForm, "frmExpiryStatus"
MsgBox "No Pending Renewals. Report Preview Cancelled",
vbInformation, "Training Manager"

Else
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Maximize
DoCmd.Close acForm, "frmExpiryStatus"
MsgBox "Pending Expiration Dates Require Your Attention!",
vbCritical, "Training Manager"
End If

End Function

Thanks for the insight!

 

[Guest]

You are trying to open a form that is already open in the FALSE part of the
IF() statement
. <snip>

 

[CJ]

Thanks for the input Steve.

Actually, I don't have a problem with the code, per se. I have a problem
with how it is being called from the switchboard manager.

Below is the essence of the problem:

I have created a function and I would like it to run when a user pushes a
button on the switchboard. With the switchboard manager, I used the command
to make code run and then entered the name of my function as ExpiryCheck().
When I push the button, I get the message "There was an error executing the
command"......very helpful!!

frmExpiryStatus is just a little form that tells the user to wait while the
code executes.
stDocName = "rptExpiry" , not frmExpiryStatus
The rptExpiry only opens if there are some pending renewals.

However, the problem is not with the code itself....I have used it many
times, the problem is that I have never tried to activate with the
switchboard manager and I don't think I have referred to it properly with
the SM code command. I made the function Public, does it need to be Global
or something? If so, I have no idea how to do it.

Thanks
CJ

 

[SteveS]

CJ,

Well, I don't know if this will help, but I would be using a Subroutine, not a
Function. Try changing the code to

Public Sub ExpiryCheck()
'Public Function ExpiryCheck()

then change the last line to

'End Function
End Sub


Another thing to check.... Since the code is not in the click event of the
button, I'm guessing that the code is in a standard module. Did you perhaps
name the module the same name as the function??? (can't do that)


If it still doesn't work, copy the code into the click event of the button on
the switchboard to see if works there.


BTW, do you have "Option Explicit" at the top of every module? If not, you
really should.


The last thing to try is comment out all lines except

DoCmd.OpenForm "frmExpiryStatus"



Then uncomment


Next try

msgbox "Look up = " & IsNull(DLookup("IDNumber", "qryExpiry"))


Just keep adding lines until you find the error or it works.


HTH

 

[CJ]

Well, the switchboard manager only accepts the name of a function, not a
subroutine and buttons created with the switchboard manager do not have the
same event properties that regular command buttons do.

Soooo.... I am just going to change the S Mgr request to just open the
report and then have some modified code added to the on open and on no data
events of the report.

Thanks for the brain power.

 

[SteveS]

CJ,

I did some testing and found that the "Function ExpiryCheck()" *MUST* be in a
standard module.

So, if you have the code in the class module for the form "Switchboard", move
it to a standard module (like Module1). Remember, the name for a standard
module cannot be the same name as a function or sub.

It should work....

HTH

 

[SteveS]

CJ,

See your post "Run Function from Switchboard" in the "Forms" NG.

Steve S.