Input variable in a module?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Nikos Yannacopoulos said:
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

Phil said:
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
Click to expand...
Click to expand...
 
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

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

Nikos Yannacopoulos said:
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

Phil said:
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
Click to expand...
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
Click to expand...
button
is
clicked and the module is processed I would like it to prompt the user
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

Nikos Yannacopoulos said:
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

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

Nikos Yannacopoulos said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Phil said:
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
Click to expand...
Menu]![Event_Start_Date] & "))"
Thanks,
Phil

Nikos Yannacopoulos said:
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

Phil said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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\#") & "
Click to expand...

Thanks again for you help....

Nikos Yannacopoulos said:
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

Phil said:
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
Click to expand...
Menu]![Event_Start_Date] & "))"
Thanks,
Phil

Nikos Yannacopoulos said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top