Values as varibles

C

Carl

Guys
How can I set a value of a field from a specific record
from an Access97 query as varible in Word97?
I don't want to carry out a mail merge (yet) as this value
will be used to source the mail merge (word) template.
This is what I have:

'mailmerge to determine the template to use
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

'a user form sets the value of the txtPos varible
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT [APS_Level] FROM [qryTemplateCreation]
WHERE (([Position_No] = " _
& "'" & txtPosNo & "'))" & ""

With ActiveDocument.MailMerge
'The value determines which template to use
Templte$ = DataSource.DataFields
("[APS_Level]").Value
End With

Path$ = Path$ + Templte$
'Debug.Print Path$

'Open the template
WordBasic.FileNew Template:=Path$, NewTemplate:=0

Debug.Print QryString$

'the real mailmerge
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

QryString$ = "SELECT * FROM [qryTemplateCreation]
WHERE (([Position_No] =" _
& "'" & txtPosNo & "'))" & ""

ActiveDocument.MailMerge.DataSource.QueryString =
QryString$

'carry out the merge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
 
P

Peter Jamieson

You're close. As far as I can see, the only things you would absolutely have
to change to make this work are

With ActiveDocument.MailMerge
'The value determines which template to use
' there should be a dot before DataSource
' Word's field names do not need the [] around them, which are just
"quoting characters"
' within Access to allow characters in table and field names, such as
spaces,
' which might otherwise confuse SQL
Templte$ = .DataSource.DataFields("APS_Level").Value
End With

However,
a. I think things would be clearer if you got rid of the remnants of
Wordbasic in your code, especially that
WordBasic.FileNew Template:=Path$, NewTemplate:=0
b. the OpenDataSource statements can be much simpler, e.g. the following
should be enough in this case:

ActiveDocument.MailMerge.OpenDataSource _
Name:=\\MEFS004\Position Profiles.mdb, _
Connection:="", _
SQLStatement:="SELECT * FROM [qryTemplateCreation]"

(You shouldn't need anything in the Connection parameter because Word will
assume a DDE connection if you don't specify an ODBC connection string. But
leave it in if you want).

--
Peter Jamieson
MS Word MVP

Carl said:
Guys
How can I set a value of a field from a specific record
from an Access97 query as varible in Word97?
I don't want to carry out a mail merge (yet) as this value
will be used to source the mail merge (word) template.
This is what I have:

'mailmerge to determine the template to use
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

'a user form sets the value of the txtPos varible
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT [APS_Level] FROM [qryTemplateCreation]
WHERE (([Position_No] = " _
& "'" & txtPosNo & "'))" & ""

With ActiveDocument.MailMerge
'The value determines which template to use
Templte$ = DataSource.DataFields
("[APS_Level]").Value
End With

Path$ = Path$ + Templte$
'Debug.Print Path$

'Open the template
WordBasic.FileNew Template:=Path$, NewTemplate:=0

Debug.Print QryString$

'the real mailmerge
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

QryString$ = "SELECT * FROM [qryTemplateCreation]
WHERE (([Position_No] =" _
& "'" & txtPosNo & "'))" & ""

ActiveDocument.MailMerge.DataSource.QueryString =
QryString$

'carry out the merge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Click to expand...
 
C

Carl

Thanks Peter
The problem is it picks the value of the first record.
When I alter the SQLStatement to "SELECT * FROM
[qryTemplateCreation] WHERE (([Position_No] = '" &
txtPosNo & "'))" & ""
I get a runtime error 5922, can't open data source. I need
this where condition to return the correct value, not the
first.

-----Original Message-----
You're close. As far as I can see, the only things you would absolutely have
to change to make this work are

With ActiveDocument.MailMerge
'The value determines which template to use
' there should be a dot before DataSource
' Word's field names do not need the [] around them, which are just
"quoting characters"
' within Access to allow characters in table and field names, such as
spaces,
' which might otherwise confuse SQL
Templte$ = .DataSource.DataFields("APS_Level").Value
End With

However,
a. I think things would be clearer if you got rid of the remnants of
Wordbasic in your code, especially that
WordBasic.FileNew Template:=Path$, NewTemplate:=0
b. the OpenDataSource statements can be much simpler, e.g. the following
should be enough in this case:

ActiveDocument.MailMerge.OpenDataSource _
Name:=\\MEFS004\Position Profiles.mdb, _
Connection:="", _
SQLStatement:="SELECT * FROM [qryTemplateCreation]"

(You shouldn't need anything in the Connection parameter because Word will
assume a DDE connection if you don't specify an ODBC connection string. But
leave it in if you want).

--
Peter Jamieson
MS Word MVP

Guys
How can I set a value of a field from a specific record
from an Access97 query as varible in Word97?
I don't want to carry out a mail merge (yet) as this value
will be used to source the mail merge (word) template.
This is what I have:

'mailmerge to determine the template to use
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

'a user form sets the value of the txtPos varible
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT [APS_Level] FROM [qryTemplateCreation]
WHERE (([Position_No] = " _
& "'" & txtPosNo & "'))" & ""

With ActiveDocument.MailMerge
'The value determines which template to use
Templte$ = DataSource.DataFields
("[APS_Level]").Value
End With

Path$ = Path$ + Templte$
'Debug.Print Path$

'Open the template
WordBasic.FileNew Template:=Path$, NewTemplate:=0

Debug.Print QryString$

'the real mailmerge
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

QryString$ = "SELECT * FROM [qryTemplateCreation]
WHERE (([Position_No] =" _
& "'" & txtPosNo & "'))" & ""

ActiveDocument.MailMerge.DataSource.QueryString =
QryString$

'carry out the merge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Click to expand...


.
Click to expand...
 
P

Peter Jamieson

Just in case: Is the Position_No column defiitely data type "String"? If no,
ditch the single quotes

If it is a string but txtPosNo isn't /identical/ to the value in the data,
you will get no hits and the query will fail.

There could be other problems but that would be my starting point.

--
Peter Jamieson
MS Word MVP

Carl said:
Thanks Peter
The problem is it picks the value of the first record.
When I alter the SQLStatement to "SELECT * FROM
[qryTemplateCreation] WHERE (([Position_No] = '" &
txtPosNo & "'))" & ""
I get a runtime error 5922, can't open data source. I need
this where condition to return the correct value, not the
first.

-----Original Message-----
You're close. As far as I can see, the only things you would absolutely have
to change to make this work are

With ActiveDocument.MailMerge
'The value determines which template to use
' there should be a dot before DataSource
' Word's field names do not need the [] around them, which are just
"quoting characters"
' within Access to allow characters in table and field names, such as
spaces,
' which might otherwise confuse SQL
Templte$ = .DataSource.DataFields("APS_Level").Value
End With

However,
a. I think things would be clearer if you got rid of the remnants of
Wordbasic in your code, especially that
WordBasic.FileNew Template:=Path$, NewTemplate:=0
b. the OpenDataSource statements can be much simpler, e.g. the following
should be enough in this case:

ActiveDocument.MailMerge.OpenDataSource _
Name:=\\MEFS004\Position Profiles.mdb, _
Connection:="", _
SQLStatement:="SELECT * FROM [qryTemplateCreation]"

(You shouldn't need anything in the Connection parameter because Word will
assume a DDE connection if you don't specify an ODBC connection string. But
leave it in if you want).

--
Peter Jamieson
MS Word MVP

Guys
How can I set a value of a field from a specific record
from an Access97 query as varible in Word97?
I don't want to carry out a mail merge (yet) as this value
will be used to source the mail merge (word) template.
This is what I have:

'mailmerge to determine the template to use
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

'a user form sets the value of the txtPos varible
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT [APS_Level] FROM [qryTemplateCreation]
WHERE (([Position_No] = " _
& "'" & txtPosNo & "'))" & ""

With ActiveDocument.MailMerge
'The value determines which template to use
Templte$ = DataSource.DataFields
("[APS_Level]").Value
End With

Path$ = Path$ + Templte$
'Debug.Print Path$

'Open the template
WordBasic.FileNew Template:=Path$, NewTemplate:=0

Debug.Print QryString$

'the real mailmerge
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"\\MELFS004\Position Profiles.mdb" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="QUERY
qryTemplateCreation", _
SQLStatement:="SELECT * FROM
[qryTemplateCreation]", SQLStatement1:=""

QryString$ = "SELECT * FROM [qryTemplateCreation]
WHERE (([Position_No] =" _
& "'" & txtPosNo & "'))" & ""

ActiveDocument.MailMerge.DataSource.QueryString =
QryString$

'carry out the merge
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
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

mailmerge recorded macro 0
Visible:=False not working for Word 2007 Mail Merge? 0
Mailmerge FindEntry problem 0
macro stops 0
Mail Merge Functionality 0
Insert variables into mailmerge documents 0
tab delimiter problem 3
VB for mailmerge that worked in 97 1

Top