C
Carlos GOCA
First of All- I would like to thank everyone on here. I have accomplished my
first VBA from reading other posts and got 90% on the second. Now the second
is down to actually working, since the syntax now works.
I am trying to make template that have a button where you push it, it asks
for CustomerID and populates a merge for them. I will add fancier stuff like
multiples when my skills improve. This company is changing from a .net
database development to a mixed, Word/Acrobat/.net with SQL so they can
modify forms in-house- lipstick on a pig kind of way.
NOW FOR THE PROBLEM
The script below is the test and will be modified to ask for the CustomerID
later. It runs and opens the database(once I put the password back), but it
ends with a 5631 error- no matching records. My guess is that 'CustomerID' =
'3030' is being passed as a text field, when it is a number field in the
database. When I remove the ' things, it throws an error. I also have no idea
what the DESC thing does, but it was in the script when I found it.
Carlos
' GOCAdbSelect Macro
' Macro recorded 5/22/2008 by GOCASERVER01
'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"D:\Documents and Settings\GOCA\My Documents\My Data
Sources\GOCASERVER02_SQLEXPRESS goca App.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Password=ahahahhh;Persist Security
Info=True;User ID=sa;Initial Catalog=goca;Data
Source=GOCASERVER02\SQLEXPRESS;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=GOCASERVER01;Use Encryption
for Data=False" _
, SQLStatement:="SELECT * FROM ""App""", SQLStatement1:="",
SubType:= _
wdMergeSubTypeOther
Dim strSQL As String
strSQL = "SELECT * FROM App WHERE 'CustomerID' = '3040' ORDER BY
'CustomerID' DESC"
MsgBox strSQL
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.QueryString = strSQL
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub
first VBA from reading other posts and got 90% on the second. Now the second
is down to actually working, since the syntax now works.
I am trying to make template that have a button where you push it, it asks
for CustomerID and populates a merge for them. I will add fancier stuff like
multiples when my skills improve. This company is changing from a .net
database development to a mixed, Word/Acrobat/.net with SQL so they can
modify forms in-house- lipstick on a pig kind of way.
NOW FOR THE PROBLEM
The script below is the test and will be modified to ask for the CustomerID
later. It runs and opens the database(once I put the password back), but it
ends with a 5631 error- no matching records. My guess is that 'CustomerID' =
'3030' is being passed as a text field, when it is a number field in the
database. When I remove the ' things, it throws an error. I also have no idea
what the DESC thing does, but it was in the script when I found it.
Carlos
' GOCAdbSelect Macro
' Macro recorded 5/22/2008 by GOCASERVER01
'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"D:\Documents and Settings\GOCA\My Documents\My Data
Sources\GOCASERVER02_SQLEXPRESS goca App.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Password=ahahahhh;Persist Security
Info=True;User ID=sa;Initial Catalog=goca;Data
Source=GOCASERVER02\SQLEXPRESS;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=GOCASERVER01;Use Encryption
for Data=False" _
, SQLStatement:="SELECT * FROM ""App""", SQLStatement1:="",
SubType:= _
wdMergeSubTypeOther
Dim strSQL As String
strSQL = "SELECT * FROM App WHERE 'CustomerID' = '3040' ORDER BY
'CustomerID' DESC"
MsgBox strSQL
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.QueryString = strSQL
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub