Creating a function?

PayeDoc · Sep 9, 2009

BruceM via AccessMonster.com · Sep 9, 2009

PayeDoc said:
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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] = [Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45 information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so you'll have
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving date] Then
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " & [Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If[/QUOTE]

That's a lot to sort out. When posting code it is helpful if you can pare
down the part that is not essential to the problem. For instance, a much
shorter version of the message would make the code more readable.

Also, it would have helped had you indicated what exactly is different in the
five procedures. Is it the document name?

A function is Public if it is to be called from code modules other than the
one where it is located. It is Private if it is to be used only within its
own code module. Code specific to a form is typically located in the form's
code module as a Private function. To tell the truth, I am not sure under
what circumstances a function in neither Private nor Public.

If all of the code is the same except for the form name ("frm_P45_submission"
in your example), you could make the code be a private function in the form's
code module, and pass the form name as an argument:

Private Function OpenThe Form (stDocName as String)

' Code Here
' ******** Eliminate the following line of code in all cases
' stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Function

In a command button's Click event:

OpenTheForm("Form1")

The part in the parentheses is known as an argument. Each button would use a
different argument (form name). The form name is passed to the function as
the variable stDocName. When the function gets to the line of code where it
opens the form, it uses that value as the name of the form to be opened.

Get rid of Dim stDocName as String in the code, as it is being passed to the
function as an argument. I don't think it would hurt if you left it in, but
its unnecessary, so there is no need to take the chance.

I may have missed the point, as it is not clear whether "higher-level action
here" means something is left out, or if opening the form *is* the higher-
level action.

PayeDoc · Sep 9, 2009

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

BruceM via AccessMonster.com said:
PayeDoc said:

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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] = [Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45 information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so you'll have
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving date] Then
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " & [Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If[/QUOTE]

That's a lot to sort out. When posting code it is helpful if you can pare
down the part that is not essential to the problem. For instance, a much
shorter version of the message would make the code more readable.

Also, it would have helped had you indicated what exactly is different in the
five procedures. Is it the document name?

A function is Public if it is to be called from code modules other than the
one where it is located. It is Private if it is to be used only within its
own code module. Code specific to a form is typically located in the form's
code module as a Private function. To tell the truth, I am not sure under
what circumstances a function in neither Private nor Public.

If all of the code is the same except for the form name ("frm_P45_submission"
in your example), you could make the code be a private function in the form's
code module, and pass the form name as an argument:

Private Function OpenThe Form (stDocName as String)

' Code Here
' ******** Eliminate the following line of code in all cases
' stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Function

In a command button's Click event:

OpenTheForm("Form1")

The part in the parentheses is known as an argument. Each button would use a
different argument (form name). The form name is passed to the function as
the variable stDocName. When the function gets to the line of code where it
opens the form, it uses that value as the name of the form to be opened.

Get rid of Dim stDocName as String in the code, as it is being passed to the
function as an argument. I don't think it would hurt if you left it in, but
its unnecessary, so there is no need to take the chance.

I may have missed the point, as it is not clear whether "higher-level action
here" means something is left out, or if opening the form *is* the higher-
level action.
[/QUOTE]

Click to expand...

BruceM via AccessMonster.com · Sep 9, 2009

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.

Click to expand...

Jack Leach · Sep 9, 2009

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)

PayeDoc said:
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

BruceM via AccessMonster.com said:

PayeDoc said:

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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] = [Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45 information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so you'll have
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving date] Then
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " & [Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If[/QUOTE]

That's a lot to sort out. When posting code it is helpful if you can pare
down the part that is not essential to the problem. For instance, a much
shorter version of the message would make the code more readable.

Also, it would have helped had you indicated what exactly is different in the
five procedures. Is it the document name?

A function is Public if it is to be called from code modules other than the
one where it is located. It is Private if it is to be used only within its
own code module. Code specific to a form is typically located in the form's
code module as a Private function. To tell the truth, I am not sure under
what circumstances a function in neither Private nor Public.

If all of the code is the same except for the form name ("frm_P45_submission"
in your example), you could make the code be a private function in the form's
code module, and pass the form name as an argument:

Private Function OpenThe Form (stDocName as String)

' Code Here
' ******** Eliminate the following line of code in all cases
' stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Function

In a command button's Click event:

OpenTheForm("Form1")

The part in the parentheses is known as an argument. Each button would use a
different argument (form name). The form name is passed to the function as
the variable stDocName. When the function gets to the line of code where it
opens the form, it uses that value as the name of the form to be opened.

Get rid of Dim stDocName as String in the code, as it is being passed to the
function as an argument. I don't think it would hurt if you left it in, but
its unnecessary, so there is no need to take the chance.

I may have missed the point, as it is not clear whether "higher-level action
here" means something is left out, or if opening the form *is* the higher-
level action.
[/QUOTE]

Click to expand...

Click to expand...

Douglas J. Steele · Sep 9, 2009

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!)

BruceM via AccessMonster.com said:
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

Click to expand...

[quoted text clipped - 163 lines]

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

Click to expand...

Click to expand...

Clifford Bass · Sep 9, 2009

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

PayeDoc said:
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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] = [Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45 information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so you'll have
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving date] Then
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " & [Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If
[/QUOTE]

BruceM via AccessMonster.com · Sep 9, 2009

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:

Click to expand...

[quoted text clipped - 79 lines]

Douglas J. Steele · Sep 9, 2009

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!)

BruceM via AccessMonster.com said:
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:

Click to expand...

[quoted text clipped - 79 lines]

higher-
level action.

Click to expand...

Click to expand...

Leslie Isaacs · Sep 9, 2009

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

Douglas J. Steele said:
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!)

BruceM via AccessMonster.com said:

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.

Click to expand...

Click to expand...

Leslie Isaacs · Sep 9, 2009

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

Jack Leach said:
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)

PayeDoc said:

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

BruceM via AccessMonster.com said:

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

Click to expand...

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

Click to expand...

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

Click to expand...

OnClick
event

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

Click to expand...

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

Click to expand...

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 -

Click to expand...

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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] = [Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45 information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so[/QUOTE] you'll
have[QUOTE]
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving[/QUOTE] date]
Then[QUOTE]
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " & [Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If

That's a lot to sort out. When posting code it is helpful if you can pare
down the part that is not essential to the problem. For instance, a much
shorter version of the message would make the code more readable.

Also, it would have helped had you indicated what exactly is[/QUOTE] different in
the[QUOTE]
five procedures. Is it the document name?

A function is Public if it is to be called from code modules other[/QUOTE] than
the[QUOTE]
one where it is located. It is Private if it is to be used only[/QUOTE] within
its[QUOTE]
own code module. Code specific to a form is typically located in the form's
code module as a Private function. To tell the truth, I am not sure under
what circumstances a function in neither Private nor Public.

If all of the code is the same except for the form name ("frm_P45_submission"
in your example), you could make the code be a private function in[/QUOTE] the
form's[QUOTE]
code module, and pass the form name as an argument:

Private Function OpenThe Form (stDocName as String)

' Code Here
' ******** Eliminate the following line of code in all cases
' stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Function

In a command button's Click event:

OpenTheForm("Form1")

The part in the parentheses is known as an argument. Each button[/QUOTE] would
use a[QUOTE]
different argument (form name). The form name is passed to the[/QUOTE] function
as[QUOTE]
the variable stDocName. When the function gets to the line of code[/QUOTE] where
it[QUOTE]
opens the form, it uses that value as the name of the form to be opened.

Get rid of Dim stDocName as String in the code, as it is being passed[/QUOTE] to
the[QUOTE]
function as an argument. I don't think it would hurt if you left it[/QUOTE] in,
but[QUOTE]
its unnecessary, so there is no need to take the chance.

I may have missed the point, as it is not clear whether "higher-level action
here" means something is left out, or if opening the form *is* the higher-
level action.
[/QUOTE][/QUOTE]

Click to expand...

Click to expand...

Jack Leach · Sep 10, 2009

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)

Leslie Isaacs said:
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

Jack Leach said:

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)

PayeDoc said:

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("", "[practices]", "[prac name] =
[Forms]![staffs subform new]![practice]")) Then
If MsgBox("Do you want to send an email requesting this
information?", vbYesNo + vbQuestion, "WARNING") = vbYes Then
SendJmail _
DLookup("[email]", "[practices]", "[prac name] =
[Forms]![staffs
subform new]![practice]"), _
[Forms]![staffs subform new]![practice], _
"[email protected]", _
[Forms]![staffs subform new]![practice] & " - P45
information",
_
"Hello " & DLookup("[prac mgr calling name]", "[practices]",
"[prac name] = [Forms]![staffs subform new]![practice]") _
& vbLf & vbLf & "We need to send a P45(1) for " &
[Forms]![staffs subform new]![Text544] & _
" to HMRC, but we do not have a record of " & [Forms]![staffs
subform new]![possesivepronoun] & [missinginfo] & _
" As this information is now mandatory for P45s, please could
you let us have the information as soon as possible." & vbLf & _
vbLf & "Kind regards" & vbLf & [Forms]![frm x
main]![Combo687] &
vbLf & "PayeDoc"
MsgBox ("Email sent!")
End If
Else
MsgBox ("There is no email address for this Practice, so you'll
have
to phone them!")
End If

Else

If [Forms]![staffs subform new]![Text531] & "" = "" Then
MsgBox ("This employee has no leaving date!")
Else
If CDate("1 " & DLookup("[months]![month name]", "[months]",
"[number]=DMax(""[x confirmed]![month number]"", ""[x confirmed]"",
""[name]=[forms]![staffs subform new]![text544]"")")) > [leaving date]
Then
If MsgBox("This employee's last paid month is " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")") & ", but their leaving date is " & [leaving
date] & "." & vbNewLine & "Are you sure the P45 should include " &
DLookup("[months]![month name]", "[months]", "[number]=DMax(""[x
confirmed]![month number]"", ""[x confirmed]"", ""[name]=[forms]![staffs
subform new]![text544]"")"), vbYesNo) = vbYes Then
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs subform new]![Text531] & "." & vbNewLine & "Is this the
correct employee?", vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
Else
If [Forms]![staffs subform new]![Text531] < date - 30 Then
If MsgBox("This employee's leaving date is " &
[Forms]![staffs
subform new]![Text531] & "." & vbNewLine & "Is this the correct
employee?",
vbYesNo) = vbYes Then
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Else
' 'higher-level' action here:
stDocName = "frm_P45_submission"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If
End If

End If

That's a lot to sort out. When posting code it is helpful if you can pare
down the part that is not essential to the problem. For instance, a much
shorter version of the message would make the code more readable.

Also, it would have helped had you indicated what exactly is different in
the
five procedures. Is it the document name?

A function is Public if it is to be called from code modules other than
the
one where it is located. It is Private if it is to be used only within
its
own code module. Code specific to a form is typically located in the
form's
code module as a Private function. To tell the truth, I am not sure under
what circumstances a function in neither Private nor Public.

If all of the code is the same except for the form name
("frm_P45_submission"
in your example), you could make the code be a private function in the
form's
code module, and pass the form name as an argument:

Private Function OpenThe Form (stDocName as String)

' Code Here
' ******** Eliminate the following line of code in all cases
' stDocName = "frm_P45_submission"[/QUOTE][/QUOTE][/QUOTE]

Click to expand...

Click to expand...

Leslie Isaacs · Sep 10, 2009

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

Douglas J. Steele said:
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!)

BruceM via AccessMonster.com said:

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.

Click to expand...

Click to expand...

Douglas J. Steele · Sep 10, 2009

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!)

Leslie Isaacs said:
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

Douglas J. Steele said:

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!)

BruceM via AccessMonster.com said:

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.

Click to expand...

Click to expand...

Jack Leach · Sep 10, 2009

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 · Sep 10, 2009

Douglas

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

I've had a good day!

Thanks again
Les

Douglas J. Steele said:
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!)

Leslie Isaacs said:

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

Douglas J. Steele said:

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.

Click to expand...

Click to expand...

PayeDoc · Sep 10, 2009

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