MailMerge Guru: Albert D. Kallal

D

Dale C Gray

Hi Albert
I've been following the threads on mail merges and of course have stumbled
across the code you have graciously provided. I almost have it working for
what I need. However in the SQL below I keep getting an "object variable
not set" error. If I replace Me!Lnprn with an actual number i.e. 41 the
code you supplied works as expected. This value is derived from a form at
runtime and the SQL works if pasted into a query?? I only want to merge the
values on the new instance of the form, not all the records in the
table...Can you comment, if I have supplied enough info where
Lnprn=First(autoid).

strSQL = "SELECT tblChemistry.Accession, tblOrders.Received,
tblPatient.PHN," _
& " tblPatient.LastName, tblPatient.FirstName, tblPatient.DOB,
tblPatient.Priority, tblPatient.Sex," _
& " tblPatient.Physician, tblPatient.Phone, tblPatient.Fax,
tblPatient.CollectTime, tblPatient.CallOther" _
& " FROM tblPatient INNER JOIN (tblChemistry INNER JOIN tblOrders ON
tblChemistry.Accession = tblOrders.Accn) ON tblPatient.Nbr = tblOrders.Nbr"
_
& " WHERE ([tblChemistry].[AutoId])>= ([Me]![LnPrn]) "
Me.Refresh
MergeAllWord (strSQL)
 
A

Albert D. Kallal

You need to send that routine a "ready to go" sql string.

It can't evaluate the field expression. So, the following should work:
& " WHERE ([tblChemistry].[AutoId])>= " & Me![LnPrn]
Click to expand...
 
B

BJ Freeman

for those of us that have not seen MergeAllWord
would you mind including that.
What I suggest is you put a break point at me.refresh then single step thru
with F8 till you get the message then post where you are in the code when
you get the message.
 
D

Dale C Gray

Thanks Albert, no sooner had I posted than I figured it out!!
BTW...hi from Calgary!!! ... just noticed you are from Edmonton.

BJ...I've supplied a link to Alberts website...hope Albert doesn't mind!!
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

BJ Freeman said:
for those of us that have not seen MergeAllWord
would you mind including that.
What I suggest is you put a break point at me.refresh then single step thru
with F8 till you get the message then post where you are in the code when
you get the message.


Dale C Gray said:
Hi Albert
I've been following the threads on mail merges and of course have stumbled
across the code you have graciously provided. I almost have it working for
what I need. However in the SQL below I keep getting an "object variable
not set" error. If I replace Me!Lnprn with an actual number i.e. 41 the
code you supplied works as expected. This value is derived from a form at
runtime and the SQL works if pasted into a query?? I only want to merge the
values on the new instance of the form, not all the records in the
table...Can you comment, if I have supplied enough info where
Lnprn=First(autoid).

strSQL = "SELECT tblChemistry.Accession, tblOrders.Received,
tblPatient.PHN," _
& " tblPatient.LastName, tblPatient.FirstName, tblPatient.DOB,
tblPatient.Priority, tblPatient.Sex," _
& " tblPatient.Physician, tblPatient.Phone, tblPatient.Fax,
tblPatient.CollectTime, tblPatient.CallOther" _
& " FROM tblPatient INNER JOIN (tblChemistry INNER JOIN tblOrders ON
tblChemistry.Accession = tblOrders.Accn) ON tblPatient.Nbr = tblOrders.Nbr"
_
& " WHERE ([tblChemistry].[AutoId])>= ([Me]![LnPrn]) "
Me.Refresh
MergeAllWord (strSQL)
Click to expand...
Click to expand...
 
Top