Input variable in a module?

[Guest]

Hi,

I have the following module that will do a Word merge from a command button
on an Access form. My question: is it possible for the module to prompt for
data? I would like to replace the "Date()-395" with a variable that is
entered when the module is run. In other words, when the command button is
clicked and the module is processed I would like it to prompt the user for a
date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Nikos Yannacopoulos]

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

 

[Guest]

Thanks Nikos,

Next question: Can one use a value from the calling form. For example:

SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])>[Form]![Report
Menu]![StartDate]))"

where [Form]![Report Menu]![StartDate] is a text box on the calling form.
With this code I get an error "Word was unable to open the data source".
I've tried various things, but can't get it to work.

Thanks,
Phil

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

Hi,

I have the following module that will do a Word merge from a command button
on an Access form. My question: is it possible for the module to prompt for
data? I would like to replace the "Date()-395" with a variable that is
entered when the module is run. In other words, when the command button is
clicked and the module is processed I would like it to prompt the user for a
date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Nikos Yannacopoulos]

Phil,

It's not just possible, it's also a very convenient technique that helps
make a friendly and intuitive user interface.You are on the right track, but
the reference to the form control must be out of the quotes, so Access
treats it as such rather than as part of the text string:

SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report
Menu]![StartDate] & "))"

You will also need to make sure the StartDate control on the form is
formatted as date.

HTH,
Nikos

Thanks Nikos,

Next question: Can one use a value from the calling form. For example:

SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])>[Form]![Report
Menu]![StartDate]))"

where [Form]![Report Menu]![StartDate] is a text box on the calling form.
With this code I get an error "Word was unable to open the data source".
I've tried various things, but can't get it to work.

Thanks,
Phil

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

Hi,

I have the following module that will do a Word merge from a command button
on an Access form. My question: is it possible for the module to

prompt
for

data? I would like to replace the "Date()-395" with a variable that is
entered when the module is run. In other words, when the command

button
is

clicked and the module is processed I would like it to prompt the user

for
a

date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Guest]

Thanks again Nikos,

Here is my code. I change the date to a different box on the form so it is
now Event_Start_Date. The SQLStatement is on two lines with the continuation
character (_) after the quote following the WHERE. I get a systax error and
have tried various options and none worked. It appears to me that the quotes
are not correct, but in adding, removing, changing, nothing seemed to work.

Also, after more testing, I discovered that the INPUTBOX approach gave me
everyone who had a "Current Membership Date" and did not apply the
restriction. It did omit those who did not have a current date.

What am I missing?????

objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE
" _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report
Menu]![Event_Start_Date] & "))"

Thanks,
Phil

Phil,

It's not just possible, it's also a very convenient technique that helps
make a friendly and intuitive user interface.You are on the right track, but
the reference to the form control must be out of the quotes, so Access
treats it as such rather than as part of the text string:

SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report
Menu]![StartDate] & "))"

You will also need to make sure the StartDate control on the form is
formatted as date.

HTH,
Nikos

Thanks Nikos,

Next question: Can one use a value from the calling form. For example:

SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])>[Form]![Report
Menu]![StartDate]))"

where [Form]![Report Menu]![StartDate] is a text box on the calling form.
With this code I get an error "Word was unable to open the data source".
I've tried various things, but can't get it to work.

Thanks,
Phil

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

Hi,

I have the following module that will do a Word merge from a command
button
on an Access form. My question: is it possible for the module to prompt
for
data? I would like to replace the "Date()-395" with a variable that is
entered when the module is run. In other words, when the command button
is
clicked and the module is processed I would like it to prompt the user for
a
date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List]
WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Nikos Yannacopoulos]

Phil,

The quotes seem OK, but I'm not sure about the rest of it, not being
familiar with the particular method. I noticed, though, that you have
removed the colon ) after the word SQLStatement, which Ithink should be
there. Might that be the problem?

I take it for granted that field [Mailing List].[Current Membership Date] is
Date/Time type, right?

HTH,
Nikos

Thanks again Nikos,

Here is my code. I change the date to a different box on the form so it is
now Event_Start_Date. The SQLStatement is on two lines with the continuation
character (_) after the quote following the WHERE. I get a systax error and
have tried various options and none worked. It appears to me that the quotes
are not correct, but in adding, removing, changing, nothing seemed to work.

Also, after more testing, I discovered that the INPUTBOX approach gave me
everyone who had a "Current Membership Date" and did not apply the
restriction. It did omit those who did not have a current date.

What am I missing?????

objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report

Menu]![Event_Start_Date] & "))"

Thanks,
Phil

Phil,

It's not just possible, it's also a very convenient technique that helps
make a friendly and intuitive user interface.You are on the right track, but
the reference to the form control must be out of the quotes, so Access
treats it as such rather than as part of the text string:

SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report
Menu]![StartDate] & "))"

You will also need to make sure the StartDate control on the form is
formatted as date.

HTH,
Nikos

Thanks Nikos,

Next question: Can one use a value from the calling form. For example:

SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])>[Form]![Report
Menu]![StartDate]))"

where [Form]![Report Menu]![StartDate] is a text box on the calling form.
With this code I get an error "Word was unable to open the data source".
I've tried various things, but can't get it to work.

Thanks,
Phil

:

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing

List]
WHERE

((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

Hi,

I have the following module that will do a Word merge from a command
button
on an Access form. My question: is it possible for the module to prompt
for
data? I would like to replace the "Date()-395" with a variable

that
is

entered when the module is run. In other words, when the command button
is
clicked and the module is processed I would like it to prompt the

user
for

a
date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List]
WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

 

[Guest]

Thanks Nikos,

FYI

I posted the same question on the progrmming page and received the following
resonse which worked. I discovered that the colon was missing also and fixed
that, but still coulld not get it to work. The following did work

Substitute > Date()-395
with

" & Format(Forms![Report Menu]![Event_Start_Date], "\#mm\/dd\/yyyy\#") & "

Thanks again for you help....

Phil,

The quotes seem OK, but I'm not sure about the rest of it, not being
familiar with the particular method. I noticed, though, that you have
removed the colon ) after the word SQLStatement, which Ithink should be
there. Might that be the problem?

I take it for granted that field [Mailing List].[Current Membership Date] is
Date/Time type, right?

HTH,
Nikos

Thanks again Nikos,

Here is my code. I change the date to a different box on the form so it is
now Event_Start_Date. The SQLStatement is on two lines with the continuation
character (_) after the quote following the WHERE. I get a systax error and
have tried various options and none worked. It appears to me that the quotes
are not correct, but in adding, removing, changing, nothing seemed to work.

Also, after more testing, I discovered that the INPUTBOX approach gave me
everyone who had a "Current Membership Date" and did not apply the
restriction. It did omit those who did not have a current date.

What am I missing?????

objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report

Menu]![Event_Start_Date] & "))"

Thanks,
Phil

Phil,

It's not just possible, it's also a very convenient technique that helps
make a friendly and intuitive user interface.You are on the right track, but
the reference to the form control must be out of the quotes, so Access
treats it as such rather than as part of the text string:

SQLStatement = "SELECT Last_Name, First_Name FROM [Mailing List] WHERE " _
"((([Mailing List].[Current Membership Date]) > " & Forms![Report
Menu]![StartDate] & "))"

You will also need to make sure the StartDate control on the form is
formatted as date.

HTH,
Nikos

Thanks Nikos,

Next question: Can one use a value from the calling form. For example:

SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List] WHERE
((([Mailing List].[Current Membership Date])>[Form]![Report
Menu]![StartDate]))"

where [Form]![Report Menu]![StartDate] is a text box on the calling form.
With this code I get an error "Word was unable to open the data source".
I've tried various things, but can't get it to work.

Thanks,
Phil

:

Phil,

Try this:

Function MergeIt()
Dim objWord As Word.Document
Dim vDate as Date

vDate = InputBox("Format: MM/DD/YYYY","Please enter date")

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List]
WHERE
((([Mailing List].[Current Membership Date])> " & vDate & "))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function

HTH,
Nikos

Hi,

I have the following module that will do a Word merge from a command
button
on an Access form. My question: is it possible for the module to
prompt
for
data? I would like to replace the "Date()-395" with a variable that
is
entered when the module is run. In other words, when the command
button
is
clicked and the module is processed I would like it to prompt the user
for
a
date.

Can this be done?
Thanks,
Phil

Function MergeIt()
Dim objWord As Word.Document

Set objWord = GetObject("MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as Mailing List.
objWord.MailMerge.OpenDataSource _
Name:="Mailing List.mdb", _
LinkToSource:=True, _
Connection:="TABLE Mailing List", _
SQLStatement:="SELECT Last_Name, First_Name FROM [Mailing List]
WHERE
((([Mailing List].[Current Membership Date])> Date()-395))"

' Execute the mail merge.
objWord.MailMerge.Execute
End Function