Creating a function?

[PayeDoc]

Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses. I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick event
procedures behind each of the 5 buttons is almost identical - except for the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for each of the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should follow
the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs


The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[BruceM via AccessMonster.com]

Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses. I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick event
procedures behind each of the 5 buttons is almost identical - except for the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for each of the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should follow
the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs

The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[PayeDoc]

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be opened,
and for the other 2 a particular (different) form needs to be opened. That's
all that happens (in addition to the same SendJamail action): either 1 of 3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e. opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les

Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses. I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick event
procedures behind each of the 5 buttons is almost identical - except for the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for each of the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should follow
the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs

The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[BruceM via AccessMonster.com]

You could add another argument to the function. For instance:

Private Function OpenFormOrReport(stDocName As String, Optional blnForm as
Boolean = True)

Boolean is Yes/No, so if it is a form leave it blank (the Default is True);
set it to False for a report. You would call it something like this for a
form:

OpenFormOrReport("Form1")

Like this for a Report:

OpenFormOrReport("Report1",False)

In the function:

If blnForm = True Then
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview
End If

Use whatever arguments you want for opening a report. I just added one
possibility with acViewPreview by wa of illustration.

You could skip the optional element of the second argument:

Private Function OpenFormOrReport(stDocName As String, blnForm as Boolean)

In that case you would have to specify True for a form:

OpenFormOrReport("Form1",True)

as well as False as shown above for a report. The optional argument should
work, but this is air code (i.e. untested). Or it may be simpler to think
about if you always specify either True or False. I'm just showing some
possibilities.

As an aside, all you need for a function name is something unique that you
will recognize. You may find a shorter function name is more convenient (e.g.
OpenIt). Your choice.

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be opened,
and for the other 2 a particular (different) form needs to be opened. That's
all that happens (in addition to the same SendJamail action): either 1 of 3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e. opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les
[quoted text clipped - 163 lines]

here" means something is left out, or if opening the form *is* the higher-
level action.

 

[Jack Leach]

Aside from Bruce's suggestions (which are perfectly good ones), you might
consider a standard naming convention for your forms or reports. For
instance, all my forms are prefixed "frm" and all my reports are prefixed
"rpt".

That way a simple string comparison will do the trick.


Select Case Left(stDocName, 3)
Case "frm"
DoCmd.OpenForm....
Case "rpt"
DoCmd.OpenReport...
Case Else
MsgBox "Convention Not Recognized!"
End Select


This would work, with a little less trouble than extra args in a function,
but only if you faithfully use the same naming convention throughout your
project. Admittedly, this type of use of naming conventions can be a
debatable subject, but I thought I'd throw it out there anyway... it works
good for me.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be opened,
and for the other 2 a particular (different) form needs to be opened. That's
all that happens (in addition to the same SendJamail action): either 1 of 3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e. opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les

Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses. I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick event
procedures behind each of the 5 buttons is almost identical - except for the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for each of the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should follow
the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs

The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[Douglas J. Steele]

It seems unnecessary to pass a variable to decide whether it's a form or
report, given that Access knows. Why not just read the system catalog to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You could add another argument to the function. For instance:

Private Function OpenFormOrReport(stDocName As String, Optional blnForm as
Boolean = True)

Boolean is Yes/No, so if it is a form leave it blank (the Default is
True);
set it to False for a report. You would call it something like this for a
form:

OpenFormOrReport("Form1")

Like this for a Report:

OpenFormOrReport("Report1",False)

In the function:

If blnForm = True Then
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview
End If

Use whatever arguments you want for opening a report. I just added one
possibility with acViewPreview by wa of illustration.

You could skip the optional element of the second argument:

Private Function OpenFormOrReport(stDocName As String, blnForm as Boolean)

In that case you would have to specify True for a form:

OpenFormOrReport("Form1",True)

as well as False as shown above for a report. The optional argument
should
work, but this is air code (i.e. untested). Or it may be simpler to think
about if you always specify either True or False. I'm just showing some
possibilities.

As an aside, all you need for a function name is something unique that you
will recognize. You may find a shorter function name is more convenient
(e.g.
OpenIt). Your choice.

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be
opened,
and for the other 2 a particular (different) form needs to be opened.
That's
all that happens (in addition to the same SendJamail action): either 1 of
3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e.
opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or
is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les

Hello All

[quoted text clipped - 163 lines]

here" means something is left out, or if opening the form *is* the
higher-
level action.

 

[Clifford Bass]

Hi Leslie,

Pretty simple, I believe. You would create a function if you want to
return a value, such as success or failure. It makes the most sense to
create the function or subroutine in the same module as the On Click events
unless you plan to use the code from other forms. If in the same module,
generally it is better to make it private so that code outside the module
cannot call it. The reasons get into object oriented programming (OOP)
theory, and I will leave it to you to study up on that. It looks like the
only thing that differs is the name of the form to open. Assuming that you
do not need to return a value, create a new private subroutine inside of the
same module that takes as its parameter the desired form name:

Private Sub MySubName(ByVal stDocName As String)

End Sub

Use whatever you want for an appropriate name of the subroutine instead
of "MySubName". Copy all of the code from one of the current On Click events
into that subroutine. Then delete the Dim stDocName As String line since you
will be passing that value as a parameter. For each of the places where you
set stDocName, (i.e. stDocName = "frm_P45_submission"), delete that line.

Then in each of your actual On Click event subroutines change them all
to one line only that looks something like this:

MySubName "frm_P45_submission"

Compile and test.

Oh yes, I do not see that you ever set the stLinkCriteria variable. So
you may as well get rid of the "Dim stLinkCriteria As String" line and change
the "DoCmd.OpenForm ..." lines to just "DoCmd.OpenForm stDocName".

Hope that helps,

Clifford Bass

Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses. I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick event
procedures behind each of the 5 buttons is almost identical - except for the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for each of the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should follow
the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs


The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[BruceM via AccessMonster.com]

I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are typically
hidden. However, it seems to me the code needs a consistent variable name in
any case. Unless I am missing something, stDocName will not be recognized in
the code if NameOfFormOrReport is the function's argument value.

It seems unnecessary to pass a variable to decide whether it's a form or
report, given that Access knows. Why not just read the system catalog to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]

 

[Douglas J. Steele]

Oops. You are, of course, correct.

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport NameOfFormOrReport, acViewPreview
End Select

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are
typically
hidden. However, it seems to me the code needs a consistent variable name
in
any case. Unless I am missing something, stDocName will not be recognized
in
the code if NameOfFormOrReport is the function's argument value.

It seems unnecessary to pass a variable to decide whether it's a form or
report, given that Access knows. Why not just read the system catalog to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]

higher-
level action.

 

[Leslie Isaacs]

Douglas, Bruce and Cliiford

Very many thanks to all of you for your suggestions. I know that the
answer I need is in there ... but it is going to take me some time to sort
it out (because this is new stuff for me) ... top priority for tomorrow!

I'll post back with new of my success, or ...

Thanks again
Les

Oops. You are, of course, correct.

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport NameOfFormOrReport, acViewPreview
End Select

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are
typically
hidden. However, it seems to me the code needs a consistent variable name
in
any case. Unless I am missing something, stDocName will not be recognized
in
the code if NameOfFormOrReport is the function's argument value.

It seems unnecessary to pass a variable to decide whether it's a form or
report, given that Access knows. Why not just read the system catalog to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]
higher-
level action.

 

[Leslie Isaacs]

Jack

Of course! All the forms and reports that are to be used with this
function do in fact start with 'frm' or 'rpt', so I could use that. I
realise that the function will fail if I try to use it with other, randomly
named objects (and there are some!), but in fact that's unlikey ... so
thanks for the tip!

Les

Aside from Bruce's suggestions (which are perfectly good ones), you might
consider a standard naming convention for your forms or reports. For
instance, all my forms are prefixed "frm" and all my reports are prefixed
"rpt".

That way a simple string comparison will do the trick.


Select Case Left(stDocName, 3)
Case "frm"
DoCmd.OpenForm....
Case "rpt"
DoCmd.OpenReport...
Case Else
MsgBox "Convention Not Recognized!"
End Select


This would work, with a little less trouble than extra args in a function,
but only if you faithfully use the same naming convention throughout your
project. Admittedly, this type of use of naming conventions can be a
debatable subject, but I thought I'd throw it out there anyway... it works
good for me.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be opened,
and for the other 2 a particular (different) form needs to be opened. That's
all that happens (in addition to the same SendJamail action): either 1 of 3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e. opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les

PayeDoc wrote:
Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user

responses.
I

need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I

need
to

perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the

OnClick
event

procedures behind each of the 5 buttons is almost identical - except

for
the

final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a seperate
function, or procedure, or something, that I could then call for

each of
the

5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything -

should
follow

the Function, Public Function, or Private Sub declaration (as parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form [staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to reply!!

Thanks for any help.
Leslie Isaacs

The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[Jack Leach]

FYI... the only *real* way to do this is through Doug's suggestion of
querying the system table. I had thought to mention it, but I am not versed
with the system tables myself so could not provide an example. His is much
more elegant... it will always work, even if you accidentally leave out a
letter in "frm".

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack

Of course! All the forms and reports that are to be used with this
function do in fact start with 'frm' or 'rpt', so I could use that. I
realise that the function will fail if I try to use it with other, randomly
named objects (and there are some!), but in fact that's unlikey ... so
thanks for the tip!

Les

Aside from Bruce's suggestions (which are perfectly good ones), you might
consider a standard naming convention for your forms or reports. For
instance, all my forms are prefixed "frm" and all my reports are prefixed
"rpt".

That way a simple string comparison will do the trick.


Select Case Left(stDocName, 3)
Case "frm"
DoCmd.OpenForm....
Case "rpt"
DoCmd.OpenReport...
Case Else
MsgBox "Convention Not Recognized!"
End Select


This would work, with a little less trouble than extra args in a function,
but only if you faithfully use the same naming convention throughout your
project. Admittedly, this type of use of naming conventions can be a
debatable subject, but I thought I'd throw it out there anyway... it works
good for me.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Bruce

Many thanks for your reply.

Sorry about including all the code - I'll remember next time!

The only difference between the 5 'higher-level actions' is that - as you
guessed - for 3 of them a particular (different) report needs to be opened,
and for the other 2 a particular (different) form needs to be opened. That's
all that happens (in addition to the same SendJamail action): either 1 of 3
reports, or 1 of 2 forms, opens - if (and only if) the set of data/user
input conditions is met.

Is it possible for my Private Function OpenTheForm (which I will rename
"OpenTheFormOrReport") to handle all 5 'higher-level actions' - i.e. opening
reports as well as forms? Obviously the line

DoCmd.OpenForm stDocName, , , stLinkCriteria

will fail if stDocName isn't the name of a form. Perhaps Private Function
OpenTheFormOrReport can test whether the argument is a form or report? Or is
there a better way of handling these two possibilities?

Thanks again for your help: I'm learning a lot here!
Les



PayeDoc wrote:
Hello All

I have a particular set of conditions, and an action (SendJmail) that
involve a combination of data held in tables, and runtime user responses.
I
need to apply this same set of conditions and action to 5 different
'higher-level' actions - i.e. depending on the data/user resonses I need
to
perform 1 of 5 actions (there are in fact 4 different combinations of
data/user resonses under which the action should be performed), and there
are 5 buttons on my form corresponding to each. Currently, the OnClick
event
procedures behind each of the 5 buttons is almost identical - except for
the
final 'higher-level' action (which appears 4 times, representing the 4
different combinations of data/user resonses under which the action
should
be performed). The first of the 5 event procedures is below, into which I
have added 4 comments
'higher-level' action here:
to show where this happens.

So far, so good ... and it all works fine ... except that when I need to
update the set of conditions or the SendJmail action I have to do it 5
times. I think I should be writing the conditions and action as a
seperate
function, or procedure, or something, that I could then call for each of
the
5 'higher-level' actions, but I'm just not sure how to do this. The main
thing I don't understand is whether it should be a Function, or a Public
Function, or a Private Sub, and then also what - if anything - should
follow
the Function, Public Function, or Private Sub declaration (as
parameters?).
The 5 buttons for the 5 'higher-level' actions are all on the form
[staffs
subform new], from which the expressions in the conditions take certain
values, but I can't see how to get the Function (or Public Function or
Private Sub) to take these values.

I hope I haven't made all this sound too complicated for anyone to
reply!!

Thanks for any help.
Leslie Isaacs

The first of the 5 event procedures is:

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull([DOB]) Or IsNull([NI number]) Then
Dim missinginfo As String
If IsNull([DOB]) Then missinginfo = " date of birth."
If IsNull([NI number]) Then missinginfo = " National Insurance
number."
If IsNull([NI number]) And IsNull([DOB]) Then missinginfo = " date of
birth or National Insurance number."
MsgBox ("We do not have a record of this employee's" & [missinginfo])

If Not IsNull(DLookup("(e-mail address removed)

 

[Leslie Isaacs]

Hello Douglas, Bruce, Jack and Clifford!

Many thanks to you all - job done ... just about!
I am very grateful indeed for this, partly because it solves the current
problem but also because I can now apply the method elsewhere.

The only thing I now need to do is, for each of the 5 button's event
procedures, to add a further action that will depend on whether or not my
new procedure 'OpenFormOrReport' succeeded in opening the form/report
(recall that the new procedure involves a lot of conditions, which - if not
met - will mean the the form/report does not open). So if OpenFormOrReport
does open the form/report I want to do one thing, otherwise I want to do
another thing. So something like:

OpenFormOrReport ("P45_online_part1")
If OpenFormOrReport succeeded Then
'Do one thing
Else
'Do the other thing
End If

Question is - what to put in place of "If OpenFormOrReport succeeded "? I'm
guessing this means that 'OpenFormOrReport' needs to return a value
according to whether or not the form/report was opened ... but how do I do
that?

Many thanks to all of you once again.
Les

Oops. You are, of course, correct.

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport NameOfFormOrReport, acViewPreview
End Select

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are
typically
hidden. However, it seems to me the code needs a consistent variable name
in
any case. Unless I am missing something, stDocName will not be recognized
in
the code if NameOfFormOrReport is the function's argument value.

It seems unnecessary to pass a variable to decide whether it's a form or
report, given that Access knows. Why not just read the system catalog to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]
higher-
level action.

 

[Douglas J. Steele]

To be perfectly honest, I came to this discussion late, so I have to confess
I don't know exactly what you're trying to do.

If what you're saying is that within the OpenFormOrReport routine you're
putting logic so that you may not actually end up opening the form or
report, use a function rather than a sub:

Function OpenFormOrReport(NameOfFormOrReport As String) As Boolean

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
If ConditionsToOpenForm = True Then
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
OpenFormOrReport = True
Else
OpenFormOrReport = False
End If
Case cReport
If ConditionsToOpenReport = True Then
DoCmd.OpenReport NameOfFormOrReport, acViewPreview

OpenFormOrReport = True
Else
OpenFormOrReport = False
End If
End Select

End Sub

Your code would then be

If OpenFormOrReport ("P45_online_part1") Then
'Do one thing
Else
'Do the other
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas, Bruce, Jack and Clifford!

Many thanks to you all - job done ... just about!
I am very grateful indeed for this, partly because it solves the current
problem but also because I can now apply the method elsewhere.

The only thing I now need to do is, for each of the 5 button's event
procedures, to add a further action that will depend on whether or not my
new procedure 'OpenFormOrReport' succeeded in opening the form/report
(recall that the new procedure involves a lot of conditions, which - if
not
met - will mean the the form/report does not open). So if OpenFormOrReport
does open the form/report I want to do one thing, otherwise I want to do
another thing. So something like:

OpenFormOrReport ("P45_online_part1")
If OpenFormOrReport succeeded Then
'Do one thing
Else
'Do the other thing
End If

Question is - what to put in place of "If OpenFormOrReport succeeded "?
I'm
guessing this means that 'OpenFormOrReport' needs to return a value
according to whether or not the form/report was opened ... but how do I do
that?

Many thanks to all of you once again.
Les

Oops. You are, of course, correct.

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport NameOfFormOrReport, acViewPreview
End Select

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are
typically
hidden. However, it seems to me the code needs a consistent variable name
in
any case. Unless I am missing something, stDocName will not be recognized
in
the code if NameOfFormOrReport is the function's argument value.

Douglas J. Steele wrote:
It seems unnecessary to pass a variable to decide whether it's a form
or
report, given that Access knows. Why not just read the system catalog
to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]
higher-
level action.

 

[Jack Leach]

I actually haven't gone through all of the code you orginally posted either,
so I'm not exactly sure what you are trying to do, but I would like to make
on more recommendation based on the glance that I did take. It *may* not be
applicable here, and if you have this working you don't have to go
restructuring your code, but this is a pretty good general concept to follow.

When your writing procedures, whether they be subs or functions, try to keep
them as short as possible. A procedure should generally only have one
specific function... it should only really do one thing. A lot of times when
people start writing code, they'll think "ok - i need to do this this this
and this", and they'll make an attempt to write this all of that code in one
procedure, where there are several advantages to breaking them down into
smaller more concise procedures. Readability and debugging simplification
are handy ones, other concepts like the number of variables in scope and
objects open have a more direct effect.

There was a post a while back where the op had a procedure that was near to
nine miles long and was looking for performance improvements... by breaking
his one large procedure down into a number of small concise procedures he was
able to cut the process time to almost a quarter of what it was.

One of the signs that I generally look for is nested If statements. In most
cases, when you start seeing more than 3 or 4 nests deep in If statements,
you can most likely clean up your code considerably by moving some of the
portions to their own private functions. This probably goes for non-nested
If statements as well... if I wind up with more than a handful in a
particular procedure thats a sign for me to take a step back and rethink the
operations... see what can be moved out and made it's own seperate part of
the module. Or if you have any code doubled inside a procedure... that's a
sure sign, a red flag, move it to it's own procedure.

The other great part about working like this is you'll have a much greater
tendancy to find little snippets of code that you can use elsewhere in your
project.

Anyway, based on a quick glance at your original code, I suspect that you
may benifit from this concept. Keep in mind that any procedure should be
clear, concise and to the point, and you will find your coding experience
will be a lot easier on many levels as you progress. Again... remember that
you don't *have* to go through and redesign everything you already have...
just something to think about for future tasks.


hth


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[PayeDoc]

Douglas

Many thanks for this 'final touch' - worked perfectly.

I've had a good day!

Thanks again
Les

To be perfectly honest, I came to this discussion late, so I have to confess
I don't know exactly what you're trying to do.

If what you're saying is that within the OpenFormOrReport routine you're
putting logic so that you may not actually end up opening the form or
report, use a function rather than a sub:

Function OpenFormOrReport(NameOfFormOrReport As String) As Boolean

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
If ConditionsToOpenForm = True Then
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
OpenFormOrReport = True
Else
OpenFormOrReport = False
End If
Case cReport
If ConditionsToOpenReport = True Then
DoCmd.OpenReport NameOfFormOrReport, acViewPreview

OpenFormOrReport = True
Else
OpenFormOrReport = False
End If
End Select

End Sub

Your code would then be

If OpenFormOrReport ("P45_online_part1") Then
'Do one thing
Else
'Do the other
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas, Bruce, Jack and Clifford!

Many thanks to you all - job done ... just about!
I am very grateful indeed for this, partly because it solves the current
problem but also because I can now apply the method elsewhere.

The only thing I now need to do is, for each of the 5 button's event
procedures, to add a further action that will depend on whether or not my
new procedure 'OpenFormOrReport' succeeded in opening the form/report
(recall that the new procedure involves a lot of conditions, which - if
not
met - will mean the the form/report does not open). So if OpenFormOrReport
does open the form/report I want to do one thing, otherwise I want to do
another thing. So something like:

OpenFormOrReport ("P45_online_part1")
If OpenFormOrReport succeeded Then
'Do one thing
Else
'Do the other thing
End If

Question is - what to put in place of "If OpenFormOrReport succeeded "?
I'm
guessing this means that 'OpenFormOrReport' needs to return a value
according to whether or not the form/report was opened ... but how do I do
that?

Many thanks to all of you once again.
Les

Oops. You are, of course, correct.

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm NameOfFormOrReport, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport NameOfFormOrReport, acViewPreview
End Select

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I didn't know about that. I don't tend to think about using system tables,
mostly because I don't know all that much about them and they are
typically
hidden. However, it seems to me the code needs a consistent variable name
in
any case. Unless I am missing something, stDocName will not be recognized
in
the code if NameOfFormOrReport is the function's argument value.

Douglas J. Steele wrote:
It seems unnecessary to pass a variable to decide whether it's a form
or
report, given that Access knows. Why not just read the system catalog
to
determine whether it's a form or report?

Sub OpenFormOrReport(NameOfFormOrReport As String)

Const cReport As Long = -32764
Const cForm As Long = -32768

Select Case Nz(DLookup("[Type]", "MSysObjects", _
"[Name] = '" & NameOfFormOrReport & "'"), 0)
Case cForm
DoCmd.OpenForm stDocName, , ,stLinkCriteria
Case cReport
DoCmd.OpenReport stDocName, acViewPreview
End Select

End Sub

You could add another argument to the function. For instance:

[quoted text clipped - 79 lines]
higher-
level action.

 

[PayeDoc]

Jack

Many thanks for these tips. I understand what you're saying and will
certainly bear it in mind from now on. If/when I get some 'development' time
I will also have a look at breaking up some of what I already have!!

Thanks again
Les