hope this is an easy one for somebody

L

Lungta

I'm using Kallal word merge, and he graciously showed the other day how to
specify different directories for different merge letters. That works great
now in the example database I was using to test.

But when I try to migrate it over into my real db backup I'm missing
something. I changed the query name, and am also now trying to call it from
a form/subform setup.

Main form: frmCase1
sf name: sfAppt (continuous form view, user clicks on desired record to make
it Current)
qry used: qryNotifLetters

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strDir As String
Dim strDirPath As String
Dim qryNotifLetters As String

Private Sub cmdMergeAll_Click()
strSQL = "select * from qryNotifLetters WHERE ApptID =" & [txtApptID]

Me.Refresh
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

'Note that you could use a condtion in the above sql
End Sub

(1) when this runs it gives error msg:
"no data was created for this merge
make sure the sql is correct
sql was
select * from qryNotifLetters WHERE ApptID =5"

But this is exactly the ApptID I want - why does it not like this sql?

debug.print strSQL gives the same sql
typing ?ApptID=5 in the Immediate window gives "False" (with frmCase1/sfAppt
open to ApptID 5)
I added an unbound txtbox to frmCase1 to reference the txtApptID in case,
but same results.

I searched this newsgroup and it looks like I should reference like this:
WHERE ApptID =" & [Forms]![frmCase1]![sfAppt].[form]![txtApptID]
but that gives the same error msg.

(2) I'm going in circles a bit so could someone please enlighten me as to
which of these Dim statements (above) I really need and which ones I can
delete?

(3) Do I need to specify the strSQL in each sub or do I just set a criteria
for the ApptID field in the qryNotifLetters so that
ApptID=Forms!frmCase1!sfAppt![txtApptID] ?

What I really want is to put the cmd button for cmdMergeAll on the main form
frmCase1 but I would be ok with putting it on each record of the sfAppt if
necessary - just desperately need to get these letters useable.

Any pointers would be most appreciated. Thanks in advance for your time.
 
D

Damian S

Hi Lungta,

Are you callilng the MergeAllWord function correctly? It would seem you
have extra brackets that are not needed.

You have:
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

Should it be?:

MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID], "word\Caseworker\")

Damian.
 
L

Lungta

Hi, Damian,

yeah, he suggested either those 2 sets of brackets as above, or no brackets
at all would both work, and they worked well before I migrated to this new db.

If you or anyone else has other ideas, though, I'd love to hear them. thanks.
--
Lungta


Damian S said:
Hi Lungta,

Are you callilng the MergeAllWord function correctly? It would seem you
have extra brackets that are not needed.

You have:
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

Should it be?:

MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID], "word\Caseworker\")

Damian.

Lungta said:
I'm using Kallal word merge, and he graciously showed the other day how to
specify different directories for different merge letters. That works great
now in the example database I was using to test.

But when I try to migrate it over into my real db backup I'm missing
something. I changed the query name, and am also now trying to call it from
a form/subform setup.

Main form: frmCase1
sf name: sfAppt (continuous form view, user clicks on desired record to make
it Current)
qry used: qryNotifLetters

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strDir As String
Dim strDirPath As String
Dim qryNotifLetters As String

Private Sub cmdMergeAll_Click()
strSQL = "select * from qryNotifLetters WHERE ApptID =" & [txtApptID]

Me.Refresh
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

'Note that you could use a condtion in the above sql
End Sub

(1) when this runs it gives error msg:
"no data was created for this merge
make sure the sql is correct
sql was
select * from qryNotifLetters WHERE ApptID =5"

But this is exactly the ApptID I want - why does it not like this sql?

debug.print strSQL gives the same sql
typing ?ApptID=5 in the Immediate window gives "False" (with frmCase1/sfAppt
open to ApptID 5)
I added an unbound txtbox to frmCase1 to reference the txtApptID in case,
but same results.

I searched this newsgroup and it looks like I should reference like this:
WHERE ApptID =" & [Forms]![frmCase1]![sfAppt].[form]![txtApptID]
but that gives the same error msg.

(2) I'm going in circles a bit so could someone please enlighten me as to
which of these Dim statements (above) I really need and which ones I can
delete?

(3) Do I need to specify the strSQL in each sub or do I just set a criteria
for the ApptID field in the qryNotifLetters so that
ApptID=Forms!frmCase1!sfAppt![txtApptID] ?

What I really want is to put the cmd button for cmdMergeAll on the main form
frmCase1 but I would be ok with putting it on each record of the sfAppt if
necessary - just desperately need to get these letters useable.

Any pointers would be most appreciated. Thanks in advance for your time.
Click to expand...
Click to expand...
 
D

Damian S

The path to your merge documents hasn't changed has it? Have you tried
running your function without the where clause to check if it works when all
records are passed?

D.

Lungta said:
Hi, Damian,

yeah, he suggested either those 2 sets of brackets as above, or no brackets
at all would both work, and they worked well before I migrated to this new db.

If you or anyone else has other ideas, though, I'd love to hear them. thanks.
--
Lungta


Damian S said:
Hi Lungta,

Are you callilng the MergeAllWord function correctly? It would seem you
have extra brackets that are not needed.

You have:
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

Should it be?:

MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID], "word\Caseworker\")

Damian.

Lungta said:
I'm using Kallal word merge, and he graciously showed the other day how to
specify different directories for different merge letters. That works great
now in the example database I was using to test.

But when I try to migrate it over into my real db backup I'm missing
something. I changed the query name, and am also now trying to call it from
a form/subform setup.

Main form: frmCase1
sf name: sfAppt (continuous form view, user clicks on desired record to make
it Current)
qry used: qryNotifLetters

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strDir As String
Dim strDirPath As String
Dim qryNotifLetters As String

Private Sub cmdMergeAll_Click()
strSQL = "select * from qryNotifLetters WHERE ApptID =" & [txtApptID]

Me.Refresh
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

'Note that you could use a condtion in the above sql
End Sub

(1) when this runs it gives error msg:
"no data was created for this merge
make sure the sql is correct
sql was
select * from qryNotifLetters WHERE ApptID =5"

But this is exactly the ApptID I want - why does it not like this sql?

debug.print strSQL gives the same sql
typing ?ApptID=5 in the Immediate window gives "False" (with frmCase1/sfAppt
open to ApptID 5)
I added an unbound txtbox to frmCase1 to reference the txtApptID in case,
but same results.

I searched this newsgroup and it looks like I should reference like this:
WHERE ApptID =" & [Forms]![frmCase1]![sfAppt].[form]![txtApptID]
but that gives the same error msg.

(2) I'm going in circles a bit so could someone please enlighten me as to
which of these Dim statements (above) I really need and which ones I can
delete?

(3) Do I need to specify the strSQL in each sub or do I just set a criteria
for the ApptID field in the qryNotifLetters so that
ApptID=Forms!frmCase1!sfAppt![txtApptID] ?

What I really want is to put the cmd button for cmdMergeAll on the main form
frmCase1 but I would be ok with putting it on each record of the sfAppt if
necessary - just desperately need to get these letters useable.

Any pointers would be most appreciated. Thanks in advance for your time.
Click to expand...
Click to expand...
Click to expand...
 
L

Lungta

Thanks for the ideas - you reminded me I should be stripping away stuff
instead of the desperate trial & error I've been doing all day... I tried
without the WHERE and also with an earlier WHERE I knew would work, and you
were right.

This particular version of auto-wordmerge requires the word docs to be
stored in the same main directory as the db, so the path changes each time
you use it in something (or copy & take home to work on). It has a nifty
procedure to automatically pick up those changes but I had layered on so much
gunk I couldn't get the sub to run.

It ended up that I had been filtering both the original qry and the strSQL,
which didn't work at all. All I needed was to dim the strSQL and the strDir
at the beginning and call the sub like MergeAllWord strSQL,
"word\Caseworker\".

Thanks very much for your patience and suggestions - I'm learning lots from
you folks!
--
Lungta


Damian S said:
The path to your merge documents hasn't changed has it? Have you tried
running your function without the where clause to check if it works when all
records are passed?

D.

Lungta said:
Hi, Damian,

yeah, he suggested either those 2 sets of brackets as above, or no brackets
at all would both work, and they worked well before I migrated to this new db.

If you or anyone else has other ideas, though, I'd love to hear them. thanks.
--
Lungta


Damian S said:
Hi Lungta,

Are you callilng the MergeAllWord function correctly? It would seem you
have extra brackets that are not needed.

You have:
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

Should it be?:

MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID], "word\Caseworker\")

Damian.

:

I'm using Kallal word merge, and he graciously showed the other day how to
specify different directories for different merge letters. That works great
now in the example database I was using to test.

But when I try to migrate it over into my real db backup I'm missing
something. I changed the query name, and am also now trying to call it from
a form/subform setup.

Main form: frmCase1
sf name: sfAppt (continuous form view, user clicks on desired record to make
it Current)
qry used: qryNotifLetters

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strDir As String
Dim strDirPath As String
Dim qryNotifLetters As String

Private Sub cmdMergeAll_Click()
strSQL = "select * from qryNotifLetters WHERE ApptID =" & [txtApptID]

Me.Refresh
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

'Note that you could use a condtion in the above sql
End Sub

(1) when this runs it gives error msg:
"no data was created for this merge
make sure the sql is correct
sql was
select * from qryNotifLetters WHERE ApptID =5"

But this is exactly the ApptID I want - why does it not like this sql?

debug.print strSQL gives the same sql
typing ?ApptID=5 in the Immediate window gives "False" (with frmCase1/sfAppt
open to ApptID 5)
I added an unbound txtbox to frmCase1 to reference the txtApptID in case,
but same results.

I searched this newsgroup and it looks like I should reference like this:
WHERE ApptID =" & [Forms]![frmCase1]![sfAppt].[form]![txtApptID]
but that gives the same error msg.

(2) I'm going in circles a bit so could someone please enlighten me as to
which of these Dim statements (above) I really need and which ones I can
delete?

(3) Do I need to specify the strSQL in each sub or do I just set a criteria
for the ApptID field in the qryNotifLetters so that
ApptID=Forms!frmCase1!sfAppt![txtApptID] ?

What I really want is to put the cmd button for cmdMergeAll on the main form
frmCase1 but I would be ok with putting it on each record of the sfAppt if
necessary - just desperately need to get these letters useable.

Any pointers would be most appreciated. Thanks in advance for your time.
Click to expand...
Click to expand...
Click to expand...
 
D

Damian S

No worries... glad to help.

D.

Lungta said:
Thanks for the ideas - you reminded me I should be stripping away stuff
instead of the desperate trial & error I've been doing all day... I tried
without the WHERE and also with an earlier WHERE I knew would work, and you
were right.

This particular version of auto-wordmerge requires the word docs to be
stored in the same main directory as the db, so the path changes each time
you use it in something (or copy & take home to work on). It has a nifty
procedure to automatically pick up those changes but I had layered on so much
gunk I couldn't get the sub to run.

It ended up that I had been filtering both the original qry and the strSQL,
which didn't work at all. All I needed was to dim the strSQL and the strDir
at the beginning and call the sub like MergeAllWord strSQL,
"word\Caseworker\".

Thanks very much for your patience and suggestions - I'm learning lots from
you folks!
--
Lungta


Damian S said:
The path to your merge documents hasn't changed has it? Have you tried
running your function without the where clause to check if it works when all
records are passed?

D.

Lungta said:
Hi, Damian,

yeah, he suggested either those 2 sets of brackets as above, or no brackets
at all would both work, and they worked well before I migrated to this new db.

If you or anyone else has other ideas, though, I'd love to hear them. thanks.
--
Lungta


:

Hi Lungta,

Are you callilng the MergeAllWord function correctly? It would seem you
have extra brackets that are not needed.

You have:
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

Should it be?:

MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID], "word\Caseworker\")

Damian.

:

I'm using Kallal word merge, and he graciously showed the other day how to
specify different directories for different merge letters. That works great
now in the example database I was using to test.

But when I try to migrate it over into my real db backup I'm missing
something. I changed the query name, and am also now trying to call it from
a form/subform setup.

Main form: frmCase1
sf name: sfAppt (continuous form view, user clicks on desired record to make
it Current)
qry used: qryNotifLetters

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strDir As String
Dim strDirPath As String
Dim qryNotifLetters As String

Private Sub cmdMergeAll_Click()
strSQL = "select * from qryNotifLetters WHERE ApptID =" & [txtApptID]

Me.Refresh
MergeAllWord ("select * from qryNotifLetters WHERE ApptID =" &
[txtApptID]), ("word\Caseworker\")

'Note that you could use a condtion in the above sql
End Sub

(1) when this runs it gives error msg:
"no data was created for this merge
make sure the sql is correct
sql was
select * from qryNotifLetters WHERE ApptID =5"

But this is exactly the ApptID I want - why does it not like this sql?

debug.print strSQL gives the same sql
typing ?ApptID=5 in the Immediate window gives "False" (with frmCase1/sfAppt
open to ApptID 5)
I added an unbound txtbox to frmCase1 to reference the txtApptID in case,
but same results.

I searched this newsgroup and it looks like I should reference like this:
WHERE ApptID =" & [Forms]![frmCase1]![sfAppt].[form]![txtApptID]
but that gives the same error msg.

(2) I'm going in circles a bit so could someone please enlighten me as to
which of these Dim statements (above) I really need and which ones I can
delete?

(3) Do I need to specify the strSQL in each sub or do I just set a criteria
for the ApptID field in the qryNotifLetters so that
ApptID=Forms!frmCase1!sfAppt![txtApptID] ?

What I really want is to put the cmd button for cmdMergeAll on the main form
frmCase1 but I would be ok with putting it on each record of the sfAppt if
necessary - just desperately need to get these letters useable.

Any pointers would be most appreciated. Thanks in advance for your time.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

urgent - split multiuser db 0
VBA - exporting serial letters to single documents 0
Pass value from subform control to query 1
Word Merge SQL syntax 1
VBA code issue...Would love expert help. 5
Delete Type Query 2
basConcatenate on New records 2
What's wrong w/ this SQL stmt? 1

Top