"Mark Horton" <mh*****@texasp ension.com> wrote in message
news:dc******** *************** ***@posting.goo gle.com...
I thought would be a super simple project, but I was wrong. I need
Help!!!!
I am using the Word.Applicatio n.Mailmerge.Ope nDataSource to try and
send a Dynamic Query to my stated Document.Open File. For the life of
me I cannot fiqure out how to pass a parameter from this code to the
"Proposal" Query in the Access Front End. If I do not add the param
variable then everthing works fine except that it pulls all of the
matching records instead of the specific one based on the proposal
number. I am open to any Suggestions.
SEE CODE BELOW:
Dim oMainDoc As Word.Document
Dim oSel As Word.Selection
Dim sDBPath As String
Dim strSQL As String
Dim param As Integer
Set oApp = CreateObject("W ord.Application ")
Set Db = CurrentDb
Set oMainDoc = oApp.Documents. Open("c:\MARK\p roposal.doc")
With oMainDoc.MailMe rge
.MainDocumentTy pe = wdFormLetters
param = Me![ProposalNumber]
strSQL = "Proposal" & param
.OpenDataSource Name:=Db, SQLStatement:=s trSQL
End With
With oMainDoc
.MailMerge.Dest ination = wdSendToNewDocu ment
.MailMerge.Exec ute Pause:=False
.Close False
End With
oApp.Visible = True
There are a number of things I'm puzzled with here. Firstly, it looks like
you have elected for late-binding with the line:
Set oApp = CreateObject("W ord.Application ")
But a few lines down, it looks like you have placed a reference to the Word
library and using its constants:
.MailMerge.Dest ination = wdSendToNewDocu ment
Are you aware of the pros and cons of early / late binding and have you made
a conscious choice?
Secondly, I'm not sure what this is:
strSQL = "Proposal" & param
Is "Proposal" the name of a query? Using a naming convention where you
called it "qryProposa l" would help others understand if that was the case.
One possible way would be to create a query "qrySelectedPro posal" which you
re-write at run-time to get the result you want (see function below), but at
a guess, your query is supposed to only return one record anyway and so
perhaps an actual mail-merge is not the best way. You could do other things
in code beside mailmerge.
Public Function DefineQuery(str QueryName As String, strSQL As String) As
Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(s trQueryName)
qdf.SQL = strSQL
DefineQuery = True
Exit_Handler:
If Not qdf Is Nothing Then Set qdf = Nothing
If Not dbs Is Nothing Then Set dbs = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function