Problem with passing a string to a function

[carl.barrett]

Hi,

I have a number of buttons on a form which run mailmerges. Next to
each button is a text box/control that the user enters a date into when
the letter was created/merged. When the user presses the button it
calls a function called LetterMerge to carry out a mail merge.

Here is the on click property for one of the buttons:

Private Sub cmdConsultationEWO_Click()

Dim strLetterDateSentName As String
strLetterDateSentName =
Forms![frmCase]![frmIncident]![frmIncidentPerpetrator]![LetterDateSentConsultEWO].ControlSource
Call LetterMerge("Consultation-EWO.doc", strLetterDateSentName)
End Sub

The control source is called: LetterDateSentConsultEWO for this
particular text box/control to the right of the button.

The function LetterMerge is below:

Public Function LetterMerge(strLetterToMerge As String,
strLetterDateSent As String)
Dim objWord As Word.Document
Dim strMergeDoc As String
Dim strPath As String
Dim msg0 As String
Dim msg1 As String
Dim msgErr As String
Dim strIncidentID As String

On Error Resume Next

If IsNull(strLetterDateSent) Then
MsgBox "You must enter a date for this letter before it can be
generated.", vbExclamation, "Empty Date Field"
strLetterDateSent.SetFocus
End
End If

DoCmd.OpenForm "frmMergingLettersPleaseWait"
DoCmd.RepaintObject acForm, "frmMergingLettersPleaseWait"

strPath = FixPath(CurrentProject.Path)
strPath = strPath & "MergeLetters\"
strMergeDoc = strPath & strLetterToMerge
strIncidentID = [Forms]![frmCase]![frmIncident]![IncidentID]

Set objWord = GetObject(strMergeDoc, "Word.Document")

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="QUERY qryLetterMerge", _
SQLstatement:="SELECT * FROM [qryLetterMerge] WHERE
[IncidentID] =" & strIncidentID

objWord.MailMerge.Execute
objWord.Close

DoCmd.Close acForm, "frmMergingLettersPleaseWait"
End Function

What I need is a check to see if the control: LetterDateSentConsultEWO
is null and if it is display a message to the user, otherwise continue
with the merge.

The problem is this, in the line: If IsNull(strLetterDateSent) Then

The string looks like "LetterDateSentConsultEWO" and it jumps straight
to end if when the field is empty. If I use: If
IsNull(LetterDateSentConsultEWO ) Then

It works OK.

It doesn't seem to like the use of a variable or the fact that it has
quotes around it.

I want to use it this way so that I don't have to put the following in
each of the onclick properties for each button.:

If IsNull(Name Of The Control) Then
MsgBox "You must enter a date for this letter before it can be
generated.", vbExclamation, "Empty Date Field"
Name Of The Control.SetFocus
End
End If
Cheers for any help that can be offered.

Regards.

[Salad]

ca**********@newcastle.gov.uk wrote:

Hi,

I have a number of buttons on a form which run mailmerges. Next to
each button is a text box/control that the user enters a date into when
the letter was created/merged. When the user presses the button it
calls a function called LetterMerge to carry out a mail merge.

Here is the on click property for one of the buttons:

Private Sub cmdConsultationEWO_Click()

Dim strLetterDateSentName As String
strLetterDateSentName =
Forms![frmCase]![frmIncident]![frmIncidentPerpetrator]![LetterDateSentConsultEWO].ControlSource
Call LetterMerge("Consultation-EWO.doc", strLetterDateSentName)
End Sub

The control source is called: LetterDateSentConsultEWO for this
particular text box/control to the right of the button.

The function LetterMerge is below:

Public Function LetterMerge(strLetterToMerge As String,
strLetterDateSent As String)
Dim objWord As Word.Document
Dim strMergeDoc As String
Dim strPath As String
Dim msg0 As String
Dim msg1 As String
Dim msgErr As String
Dim strIncidentID As String

On Error Resume Next

If IsNull(strLetterDateSent) Then
MsgBox "You must enter a date for this letter before it can be
generated.", vbExclamation, "Empty Date Field"
strLetterDateSent.SetFocus
End
End If

DoCmd.OpenForm "frmMergingLettersPleaseWait"
DoCmd.RepaintObject acForm, "frmMergingLettersPleaseWait"

strPath = FixPath(CurrentProject.Path)
strPath = strPath & "MergeLetters\"
strMergeDoc = strPath & strLetterToMerge
strIncidentID = [Forms]![frmCase]![frmIncident]![IncidentID]

Set objWord = GetObject(strMergeDoc, "Word.Document")

objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
LinkToSource:=True, _
Connection:="QUERY qryLetterMerge", _
SQLstatement:="SELECT * FROM [qryLetterMerge] WHERE
[IncidentID] =" & strIncidentID

objWord.MailMerge.Execute
objWord.Close

DoCmd.Close acForm, "frmMergingLettersPleaseWait"
End Function

What I need is a check to see if the control: LetterDateSentConsultEWO
is null and if it is display a message to the user, otherwise continue
with the merge.

The problem is this, in the line: If IsNull(strLetterDateSent) Then

The string looks like "LetterDateSentConsultEWO" and it jumps straight
to end if when the field is empty. If I use: If
IsNull(LetterDateSentConsultEWO ) Then

It works OK.

It doesn't seem to like the use of a variable or the fact that it has
quotes around it.

I want to use it this way so that I don't have to put the following in
each of the onclick properties for each button.:

If IsNull(Name Of The Control) Then
MsgBox "You must enter a date for this letter before it can be
generated.", vbExclamation, "Empty Date Field"
Name Of The Control.SetFocus
End
End If
Cheers for any help that can be offered.

Regards.

Very sloppy coding and logic.

Why are you adding confusion to your calls? Why can't you pass a date
field as a date? Or as a variant?
Public Function LetterMerge(strLetterToMerge As String,
datLetterDateSent As Date)
would be more preferable.
Then you have this weird construct.
If IsNull(strLetterDateSent) Then
MsgBox "You must enter a date for this letter before it can be
generated.", vbExclamation, "Empty Date Field"

'what are you setting Focus for at this point. And why would it
'work? It wasn't sent to the function as a control.
strLetterDateSent.SetFocus

'what the heck is this?
End
End If

Why not have an Exit Function instead so it doesn't execute further
statements below it?

Have you heard of the function IsDate()?

[carl.barrett]

Hi,

Thanks for your comments.

I am not trying to pass the date in the control to the function I am
trying to pass the name of the control to see if it is empty. I am
setting the focus to it if it is empty that is why I have the
strLetterDateSent.SetFocus. If I use: IsNull(LetterDateSentConsultEWO
) Then along with LetterDateSentConsultEWO .SetFocus - it works OK as
it is using the control name.

Any helpful suggestions would be appreciated regarding my sloppy code
and logic.

Cheers.

[Keith W]

<ca**********@newcastle.gov.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi,

Thanks for your comments.

I am not trying to pass the date in the control to the function I am
trying to pass the name of the control to see if it is empty. I am
setting the focus to it if it is empty that is why I have the
strLetterDateSent.SetFocus. If I use: IsNull(LetterDateSentConsultEWO
) Then along with LetterDateSentConsultEWO .SetFocus - it works OK as
it is using the control name.

Any helpful suggestions would be appreciated regarding my sloppy code
and logic.

I would suggest passing the form to the procedure (using "Me"), then you
could test the control thusly:

Public Function MyFunction(frm as Form)

If IsNull(frm.txtMyTextBox) Then ... etc

Just my 2p worth.

Keith.
www.keithwilby.com