Pass a Parameter to a Query in Access Front-End

[Mark Horton]

I thought would be a super simple project, but I was wrong. I need
Help!!!!

I am using the Word.Application.Mailmerge.OpenDataSource 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("Word.Application")

Set Db = CurrentDb
Set oMainDoc = oApp.Documents.Open("c:\MARK\proposal.doc")
With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters

param = Me![ProposalNumber]
strSQL = "Proposal" & param
.OpenDataSource Name:=Db, SQLStatement:=strSQL
End With
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute Pause:=False
.Close False
End With
oApp.Visible = True

[Fletcher Arnold]

"Mark Horton" <mh*****@texaspension.com> wrote in message
news:dc**************************@posting.google.c om...

I thought would be a super simple project, but I was wrong. I need
Help!!!!

I am using the Word.Application.Mailmerge.OpenDataSource 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("Word.Application")

Set Db = CurrentDb
Set oMainDoc = oApp.Documents.Open("c:\MARK\proposal.doc")
With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters

param = Me![ProposalNumber]
strSQL = "Proposal" & param
.OpenDataSource Name:=Db, SQLStatement:=strSQL
End With
With oMainDoc
.MailMerge.Destination = wdSendToNewDocument
.MailMerge.Execute 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("Word.Application")
But a few lines down, it looks like you have placed a reference to the Word
library and using its constants:
.MailMerge.Destination = wdSendToNewDocument

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 "qryProposal" would help others understand if that was the case.
One possible way would be to create a query "qrySelectedProposal" 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(strQueryName 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(strQueryName)

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

[Mark Horton]

Thanks for the Help. I fixed the late binding mistake, it was not a
intentional coding choice. I am sorry for my ignorance, but you said:
"You could do other things
in code beside mailmerge." What other things would be better to pass a
query to a Word Document besides using mailmerge?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

"Mark Horton" <mh*****@texaspension.com> wrote in message
news:40*********************@news.frii.net...

Thanks for the Help. I fixed the late binding mistake, it was not a
intentional coding choice. I am sorry for my ignorance, but you said:
"You could do other things
in code beside mailmerge." What other things would be better to pass a
query to a Word Document besides using mailmerge?

Hi Mark

Well if it's only one document, you could have open a standard Word document
or template (no mailmerge data) and replace certain key terms with
information from the database.
One advantage is that your users need zero mailmerge skills to alter or
create new documents. In the example below, the code replaces the
text[[ProposalNumber]] with the value from the current record.

strPropNo = Cstr(Nz(Me!ProposalNumber, 0))
Set WdDoc = WdApp.Documents.Add(strTemplatePath)
Set WdRange = WdApp.ActiveDocument.Content
WdRange.Find.Execute FindText:="[[ProposalNumber]]", ReplaceWith:=strPropNo,
Replace:=wdReplaceAll
Fletcher