Using text box data in a module

G

Guest

Hi,
I posted this in the input/export page and got some help, but my problem has
not been resolved. So, I thought I would try here.

I have the following module that will do a Word merge from a command button
on an Access form. I would like to replace the "Date()-395" with data
from a Date formated text box on the calling form. In other words, the
user would enter a date on the form and then click the command button which
would then do the merge using the entered date as the criteria. I tried many
optoins but usually get syntax errors. The form is called "Report Menu" and
the text box is "Event_Start_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

Thanks,
 
G

Guest

Substitute > Date()-395
" & Format(Forms![Report Menu]![Event_Start_Date], "\#mm\/dd\/yyyy\#") & "
Click to expand...

SQL Dates are in US format so by explicity specifying the date format it
will work whatever the users regional settings.

Phil said:
Hi,
I posted this in the input/export page and got some help, but my problem has
not been resolved. So, I thought I would try here.

I have the following module that will do a Word merge from a command button
on an Access form. I would like to replace the "Date()-395" with data
from a Date formated text box on the calling form. In other words, the
user would enter a date on the form and then click the command button which
would then do the merge using the entered date as the criteria. I tried many
optoins but usually get syntax errors. The form is called "Report Menu" and
the text box is "Event_Start_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

Thanks,
Click to expand...
 
G

Guest

Thanks Pete, that worked great.

One more question. If I want to select the merge file on my form what would
the syntax be for replacing the "MyMerge.doc" in the following Set command.
Once again I tried variious options none of which worked. I just can't seem
to get the syntax right. My text box name is "Merge_Name"

Set objWord = GetObject("MyMerge.doc", "Word.Document")

Thanks again.
Pete said:
Substitute > Date()-395
" & Format(Forms![Report Menu]![Event_Start_Date], "\#mm\/dd\/yyyy\#") & "
Click to expand...

SQL Dates are in US format so by explicity specifying the date format it
will work whatever the users regional settings.

Phil said:
Hi,
I posted this in the input/export page and got some help, but my problem has
not been resolved. So, I thought I would try here.

I have the following module that will do a Word merge from a command button
on an Access form. I would like to replace the "Date()-395" with data
from a Date formated text box on the calling form. In other words, the
user would enter a date on the form and then click the command button which
would then do the merge using the entered date as the criteria. I tried many
optoins but usually get syntax errors. The form is called "Report Menu" and
the text box is "Event_Start_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

Thanks,
Click to expand...
Click to expand...
 
G

Guest

I have not tried this, but looking in the helpfile GetObject requires the
full pathname to the file. If you are always looking in then same folder then
to save the user from typing this in you could hard code it into the form.
Also the Class parameter is optional.:

Dim strFilePath as String
strFilePath = "c:\Documents\"
strFilePath=strFilePath & Forms![Report Menu]![Merge_Name]
Set objWord = GetObject(strFilePath)

Pete

Phil said:
Thanks Pete, that worked great.

One more question. If I want to select the merge file on my form what would
the syntax be for replacing the "MyMerge.doc" in the following Set command.
Once again I tried variious options none of which worked. I just can't seem
to get the syntax right. My text box name is "Merge_Name"

Set objWord = GetObject("MyMerge.doc", "Word.Document")

Thanks again.
Pete said:
Substitute > Date()-395
" & Format(Forms![Report Menu]![Event_Start_Date], "\#mm\/dd\/yyyy\#") & "
Click to expand...

SQL Dates are in US format so by explicity specifying the date format it
will work whatever the users regional settings.

Phil said:
Hi,
I posted this in the input/export page and got some help, but my problem has
not been resolved. So, I thought I would try here.

I have the following module that will do a Word merge from a command button
on an Access form. I would like to replace the "Date()-395" with data
from a Date formated text box on the calling form. In other words, the
user would enter a date on the form and then click the command button which
would then do the merge using the entered date as the criteria. I tried many
optoins but usually get syntax errors. The form is called "Report Menu" and
the text box is "Event_Start_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

Thanks,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Input variable in a module? 6
Run Time Error = 432 6
If Statement with MsgBox 7
mailmerge from access password trouble 1
Error automating mail merge using access query 1
How to Control Which Printer MS Word Prints To 1
Word Merge From Access 2
Mail merge to MS Word 1

Top