MailMerge Guru: Albert D. Kallal

  • Thread starter Thread starter Dale C Gray
  • Start date Start date
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)
 
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...
 
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.
 
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...
 
Back
Top