Albert D. Kallal -WordMerge20

Albert,
Thanks for making your WordMerge20 sample db available- it's very slick! I
have a question about chosing a query. All of the queries that I'd like to
use for my WordMerge require user input- they are renewal notices with an "as
of date" pop-up box (so that the user can run the merges, say, as of the end
of next month).

Is there a way that I could still use the fabulous WordMerge20 example, but
modify it to accept user input?

This is my sql subquery for user input:
(SELECT Max(DuesLineItem.DateCreated) AS MaxOfCreateDate FROM DuesLineItem
GROUP BY DuesLineItem.ContactID HAVING
(((DateDiff("d",Max([DuesLineItem].[DateCreated]),[forms]![DateParam]![FindDate],True))334)

In my report, here's the vba:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateParam", , , , , acDialog
If Not IsLoaded("DateParam") Then
Cancel = True
End If

In your WhatQuery code you have:
Private Sub Command2_Click()

MergeAllWord Me.Combo0

End Sub

I tried to add code to your code to open my user input form- it didn't like
the Is Loaded bit, but seemed to accept DoCmd.OpenForm "DateParam", , , , ,
acDialog
then my input box opened, I entered the date, and hit the preview button
(which of course has nothing to do with WordMerge, but I'm not sure what to
do about it- how to use an input box...)
Private Sub Preview_Click()
Me.Visible = False
End Sub
and then got an error message: no data for this merge.

Can the WordMerge work with user input parameters?

"Stephanie" wrote in message
...


Is there a way that I could still use the fabulous WordMerge20 example,
but
modify it to accept user input?

Sure, you can grab the users input for a date, or even build a form with a
text box for the date, and a "word merge" button.

If you want to modify (build a new) form that "returns" values, then I
explain how to do this he
http://www.members.shaw.ca/AlbertKal...log/Index.html

In looking at your reprot code sample, it seems you are using the above
approach anyway (visiable = false to kick out of acDiaogMode).

ok, if you do the above then the query should work.

You code could be:

DoCmd.OpenForm "DateParam", , , , , acDialog
If Not IsLoaded("DateParam") Then
exit sub
End If

MergeAllWord ("nameofquery")

-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Thanks so much for the reply!
I must admit, I'm a bit confused. I'm sorry I haven't explained myself
well. Let me explain what I'd really like to do and see if it can be done.

I like the WhatQuery user interface- very user friendly and easy to use for
more than one WordMerge. So I'd like the user to select the query from the
combo box on WhatQuery, then have the appropriate user input open up, the
user enters the date, and then clicks on the "Click here to merge using the
above query", and have the merge run using the query with the user defined
date and the chosen WordMerge template.

So I don't want to use a Report (I think that's what has me confused), and
therefore DateParam (my user input form won't open). Rather what the query is
looking for will pop up as a window that states "Enter Parameter Value" and
shows Forms!DateParam!FindDate (perhaps technically it's all the same thing)
along with a field to enter the date.

I'm wondering if there is a way to modify your WhatQuery form to accept user
input (for the date) and then click on merge to use the query, with the input
date, and the WordMerge template.

Or barring all of that user-friendly stuff, I tried modifying my report:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "DateParam", , , , , acDialog
If Not IsLoaded("DateParam") Then
' Cancel = True
Exit Sub
End If
MergeAllWord ("Labels by Ken ITA Membership")
End Sub

But received an error message: No data for this merge...make sure sql is
correct.

Thanks for the help- I appreciate your efforts.

"Albert D.Kallal" wrote:

"Stephanie" wrote in message
...


Is there a way that I could still use the fabulous WordMerge20 example,
but
modify it to accept user input?

Sure, you can grab the users input for a date, or even build a form with a
text box for the date, and a "word merge" button.

If you want to modify (build a new) form that "returns" values, then I
explain how to do this he
http://www.members.shaw.ca/AlbertKal...log/Index.html

In looking at your reprot code sample, it seems you are using the above
approach anyway (visiable = false to kick out of acDiaogMode).

ok, if you do the above then the query should work.

You code could be:

DoCmd.OpenForm "DateParam", , , , , acDialog
If Not IsLoaded("DateParam") Then
exit sub
End If

MergeAllWord ("nameofquery")

-
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Hum, I always thought that you should be able to use a expression the query,
and my merge should work.

As long as that form is open with the correct date entered, and you then hit
the merge button, it should work.

am not sure why it don't work (but, it should...).

Another approach, that is somewhat easier is to build a form with the date
prompt, and also the merge button.

Also, built a new query, and have the "condition" set to this new form.

(so, change the query form!e expression to this "new" form that we make).

You should then be able to go:

MergeAllWord ("qryLablesITAMembership")

In the above example, we called the new query aryLablesITMMembership

As mentioned, I actually build a lot of prompt forms...and if you notice,
most of them have a word merge button

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

So, you might have better luck if you put the date prompt, and the merge
buttion on a form together..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Ok...hold on!!

You can't use a form expression in those queries for the word merge.


For some reason I thought you could...but you can not..... MY SORRY!!!


So, you could change that query to a append query, send it to a temp
table..and then merge on that....

Another solution is to simply remove the condition from the sql...and add it
yourself in code....

That would mean removing everything in the sql clause in the having to code.

If you want me to post some code that does this, take your query (in sql
view) as you have it now, and paste into a response on this message.

I will then respond with a small code snippt that will work for you.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Albert,
Thank you- you're very kind. I'm not talented enough to use an append query
and temp tables...
A quick explanation of my setup: I have members that have more than one
address and some members live together and share a membership. I want to
submit an input parameter (date) and send a form letter to members who have
their membership due soon (memberships are good for 2 years).
I run a report and the report asks if I want to make address lables. I'm
going to list 2 queries because the grouping of members is a bit complicated
(for me anyway, MVP Ken was instrumental). The first query brings back all
of the info, but without combining the member and the significant other. The
second query prepares the label content and combines the member/significant
other name.

Labels by Ken ITA Membership:
SELECT Nz(c1.NickName,c1.FirstName) & " " & c1.LastName AS [Member Name],
(SELECT Nz(c2.[NickName],c2.[FirstName]) & " " & c2.[LastName] AS [SONm] FROM
Contacts c2 WHERE c2.ContactID = Nz(c1.SignificantOtherID,-9999)) AS [SO
Name], IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProv ince2)) AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, MemberCategory.MemberCategory,
DuesLineItem.DateCreated, DuesLineItem.AmountDue,
DuesLineItem.DuesItemTypeID, DuesItemType.DuesItemType,
(DateAdd("d",365,[DateCreated])) AS [Due Date], Nz(c1.CompanyName," ") AS
[Company Name], Nz(c1.ContactNickName,c1.ContactFirstName) & " " &
c1.ContactLastName AS ContactName
FROM MemberCategory INNER JOIN (DuesItemType INNER JOIN ((Contacts AS c1
INNER JOIN MemberStatus ON c1.MemberStatusID=MemberStatus.MemberStatusID)
INNER JOIN DuesLineItem ON c1.ContactID=DuesLineItem.ContactID) ON
DuesItemType.DuesItemTypeID=DuesLineItem.DuesItemT ypeID) ON
MemberCategory.MemberCategoryID=c1.MemberCategoryI D
WHERE (((c1.SignificantOtherID)[c1].[ContactID] Or (c1.SignificantOtherID)
Is Null) AND ((DuesLineItem.DateCreated) In (SELECT
Max(DuesLineItem.DateCreated) AS MaxOfCreateDate FROM DuesLineItem GROUP BY
DuesLineItem.ContactID HAVING
(((DateDiff("d",Max([DuesLineItem].[DateCreated]),[forms]![DateParam]![FindDate],True))334))));

Labels_Report ITA Membership:
SELECT (IIf(Len(Trim(LK.[Company Name]) & "")=0,IIf(Len(Trim(LK.[SO Name]) &
"")=0,Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13)
& Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode) & Chr(13)
& Chr(10),Trim(LK.[Member Name]) & Chr(13) & Chr(10) & Trim(LK.[SO Name]) &
Chr(13) & Chr(10) & Trim(LK.Address) & Chr(13) & Chr(10) & Trim(LK.City & ",
" & LK.State & " " & LK.PostalCode)),Trim(LK.[Member Name]) & Chr(13) &
Chr(10) & Trim(LK.[Company Name]) & Chr(13) & Chr(10) & Trim(LK.Address) &
Chr(13) & Chr(10) & Trim(LK.City & ", " & LK.State & " " & LK.PostalCode)))
AS MyLabelContents
FROM [Labels by Ken ITA Membership] AS LK;

And it might be nice to print the address labels "Labels_Report ITA
Membership" report when printing the WordMerge document.

Anyway, it all looks complicated to me. It would be nice to be able to make
it easier for the end-user if possible. Thanks for you help- I would
appreciate a step-by-step if you have time. Cheers!


"Albert D.Kallal" wrote:

Ok...hold on!!

You can't use a form expression in those queries for the word merge.


For some reason I thought you could...but you can not..... MY SORRY!!!


So, you could change that query to a append query, send it to a temp
table..and then merge on that....

Another solution is to simply remove the condition from the sql...and add it
yourself in code....

That would mean removing everything in the sql clause in the having to code.

If you want me to post some code that does this, take your query (in sql
view) as you have it now, and paste into a response on this message.

I will then respond with a small code snippt that will work for you.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Ok, here is what you do.

In code, we will put the paramters in for you.

Dim strSql As String
Dim strWhere As String
Dim strParm As String
Dim strV As String

Dim strF As String

strF = "DateParam"

DoCmd.OpenForm strF, , , , , acDialog
If IsLoaded(strF) = False Then
Exit Sub
End If

strSql = CurrentDb.QueryDefs("query1")

strSql = Left(strSql, InStr(strSql, ";") - 1)

strParm = "[forms]![DateParam]![FindDate]"

strV = Forms![DateParam]![FindDate]
strV = "#" & Format(strV, "mm/dd/yyyy") & "#"

strSql = Replace(strSql, strParm, strV)

MergeAllWord (strSql)


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Thanks so much for the reply!
I'm still a bit confused about the methodology. Is this code going on your
WhatQuery form so that when I click the "click here to merge using above
query", the DateParam form is called? Or am I suppose to attach the code
somewhere else? I put it on the WhatQuery form, under Private Sub
Command2_Click()

On code line:
strSql = CurrentDb.QueryDefs("query1")

two questions: 1) am I suppose to replace "query1" by the name of a specific
query or is this set up to handle a generic query name? Generic being the
nicest possible as I have more than one query that needs to be merged.

2) in either case (using "query1" or "Labels by Ken ITA Memberships", I
receive an error message: Compile Error, type mismatch

What did I do wrong? Thanks!


"Albert D.Kallal" wrote:

Ok, here is what you do.

In code, we will put the paramters in for you.

Dim strSql As String
Dim strWhere As String
Dim strParm As String
Dim strV As String

Dim strF As String

strF = "DateParam"

DoCmd.OpenForm strF, , , , , acDialog
If IsLoaded(strF) = False Then
Exit Sub
End If

strSql = CurrentDb.QueryDefs("query1")

strSql = Left(strSql, InStr(strSql, ";") - 1)

strParm = "[forms]![DateParam]![FindDate]"

strV = Forms![DateParam]![FindDate]
strV = "#" & Format(strV, "mm/dd/yyyy") & "#"

strSql = Replace(strSql, strParm, strV)

MergeAllWord (strSql)


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

"Stephanie" wrote in message
news
Thanks so much for the reply!
I'm still a bit confused about the methodology. Is this code going on
your
WhatQuery form so that when I click the "click here to merge using above
query", the DateParam form is called? Or am I suppose to attach the code
somewhere else? I put it on the WhatQuery form, under Private Sub

To solve a bit of question, and answers, lets just make a new form scratch.


Turn off all of the navigation bars, record selectors etc.

Just put a control on the form called "datefind".

Then, put a button on this form to run the merge.

Now, take your query, and modify the condition to point to our form.
(lets assume we call the form it DateToWord)

two questions: 1) am I suppose to replace "query1" by the name of a
specific
query or is this set up to handle a generic query name? Generic being the
nicest possible as I have more than one query that needs to be merged.

yes, you change to a specific query.
In my example, I assumed you replace query1 with the name of your actual
query that you made
(so, it is hard coded in this case, but you could later expanded on this
concept..but lets just get one thing working at a time here)

So, since our form now has the date prompt ON it (we don't open a another
form to get the date).

So, we could put a button on this form to do the merge...

Our code behind this button could be:

Dim strSql As String
Dim strWhere As String
Dim strParm As String
Dim strV As String

Dim strF As String


strSql = CurrentDb.QueryDefs("query1")

strSql = Left(strSql, InStr(strSql, ";") - 1)

strParm = "[forms]![DateToWordParam]![FindDate]"

strV = Forms![DateToWordParam]![FindDate]
strV = "#" & Format(strV, "mm/dd/yyyy") & "#"

strSql = Replace(strSql, strParm, strV)

MergeAllWord (strSql)

Make sure you have a control called "findDate" on our form. Also, make sure
you change the condition in the query to match the current form

ie change:
(((DateDiff("d",Max([DuesLineItem].[DateCreated]),[forms]![DateToWordParam]![FindDate],True))334))));


strParm = "[forms]![DateToWordParam]![FindDate]"

So, the above "[forms]![DateToWordParam]![FindDate]" must be EXACLTY the
same as what you have in the query....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Thanks for the step-by-step.
My query is called ITAMembershipsDue
I've set up my form called DateToWordParam with an empty record
source. The form has an unbound field called FindDate

I changed my query parameter to:

(((DateDiff("d",Max([DuesLineItem].[DateCreated]),[forms]![DateToWordParam]![FindDate],True))334))

To the form, I added a button called Command3 and attached this code to
the button:

Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim strSql As String
Dim strWhere As String
Dim strParm As String
Dim strV As String

Dim strF As String


strSql = CurrentDb.QueryDefs("ITAMembershipsDue")

strSql = Left(strSql, InStr(strSql, ";") - 1)

strParm = "[forms]![DateToWordParam]![FindDate]"

strV = Forms![DateToWordParam]![FindDate]
strV = "#" & Format(strV, "mm/dd/yyyy") & "#"

strSql = Replace(strSql, strParm, strV)

MergeAllWord (strSql)


Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

The code is bombing out on the line that reads:
strSql = CurrentDb.QueryDefs("ITAMembershipsDue")

with Complie Error: Type mismatch

2 questions: 1) why the error (could it be realted to code References?)
2) While I understand the setup so far (with the form allowing me to enter
the paramater date and then hit the merge button), I still don't understand
where we're calling the Word template to merge with. Maybe we're not there
yet...

Sorry to be having so many problems! Thanks for the help.




"Albert D.Kallal" wrote:

"Stephanie" wrote in message
news
Thanks so much for the reply!
I'm still a bit confused about the methodology. Is this code going on
your
WhatQuery form so that when I click the "click here to merge using above
query", the DateParam form is called? Or am I suppose to attach the code
somewhere else? I put it on the WhatQuery form, under Private Sub

To solve a bit of question, and answers, lets just make a new form scratch.


Turn off all of the navigation bars, record selectors etc.

Just put a control on the form called "datefind".

Then, put a button on this form to run the merge.

Now, take your query, and modify the condition to point to our form.
(lets assume we call the form it DateToWord)

two questions: 1) am I suppose to replace "query1" by the name of a
specific
query or is this set up to handle a generic query name? Generic being the
nicest possible as I have more than one query that needs to be merged.

yes, you change to a specific query.
In my example, I assumed you replace query1 with the name of your actual
query that you made
(so, it is hard coded in this case, but you could later expanded on this
concept..but lets just get one thing working at a time here)

So, since our form now has the date prompt ON it (we don't open a another
form to get the date).

So, we could put a button on this form to do the merge...

Our code behind this button could be:

Dim strSql As String
Dim strWhere As String
Dim strParm As String
Dim strV As String

Dim strF As String


strSql = CurrentDb.QueryDefs("query1")

strSql = Left(strSql, InStr(strSql, ";") - 1)

strParm = "[forms]![DateToWordParam]![FindDate]"

strV = Forms![DateToWordParam]![FindDate]
strV = "#" & Format(strV, "mm/dd/yyyy") & "#"

strSql = Replace(strSql, strParm, strV)

MergeAllWord (strSql)

Make sure you have a control called "findDate" on our form. Also, make sure
you change the condition in the query to match the current form

ie change:
(((DateDiff("d",Max([DuesLineItem].[DateCreated]),[forms]![DateToWordParam]![FindDate],True))334))));


strParm = "[forms]![DateToWordParam]![FindDate]"

So, the above "[forms]![DateToWordParam]![FindDate]" must be EXACLTY the
same as what you have in the query....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal