Query from VBA Script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carlos GOCA - 23 May 2008 18:11 GMT

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

Signature

Thank you for your time.

Reply to this Message

Doug Robbins - Word MVP - 24 May 2008 09:42 GMT

See the article "Access a database and insert into a Word document the data
that you find there" at:

http://www.word.mvps.org/FAQs/InterDev/GetDataFromDB.htm

This is not the sort of thing for which I would use mailmerge. Rather,
instead of Mergefields in a mail merge main document, I would use
DOCVARIABLE fields in a template and create a new document from that
template and then in place of inserting the data at the selection as is done
in the code on the above web page, I would set the value of a document
variable to the contents of the field and have the code update the fields in
the document so that the values assigned to the variables were displayed in
the DOCVARIABLE fields.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> First of All- I would like to thank everyone on here. I have accomplished
> my
[quoted text clipped - 57 lines]
> End With
> End Sub

Reply to this Message