Joe O'Connell
joeo at appoli.com
Thu Jun 26 17:00:50 CDT 2008
Tina,
Instead of referencing the form in your query, try using a function to
return the value:
Instead of
... WHERE somefield =
[Forms]![frmContributionAcknowledge]![txtDepositDate]
Use this
... WHERE somefield = GetDepositDate()
In a module put this function
Public Function GetDepositDate() as Date
GetDepositDate = [Forms]![frmContributionAcknowledge]![txtDepositDate]
Exit Sub
Joe O'Connell
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Thursday, June 26, 2008 5:36 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Two more issues on my Word document automation
Hi Andy,
The query makes a table of the records matching a date criterion - the
DepositDate field criterion is
[Forms]![frmContributionAcknowledge]![txtDepositDate]. So the answers
are yes and yes.
The first try with this code gave the message that the table already
existed. I deleted the temporary table and ran the code again. This
time it said there were too few parameters, that it expected 1. I do
not know what that was about. I'll watch for your reply, and while
waiting I will read up on querydef. Thanks again for all your
assistance.
Tina
Andy Lacey wrote:
> Mm well it rather depends what's in that query. Is it appending to a
table
> or something? Does it use anything like =Forms!etc as criteria?
>
> If the answrs are yes and no then try this
>
> Dim qdf as querydef
>
> Set
qdf=currentdb.QueryDefs("qryPrintListForContributionTypeOfMember_t")
> qdf.Execute
> If qdf.recordsAffected=0 then
> MsgBox "No records found"
> Else
> MsgBox "Done"
> End If
> Qdf.close
> Set qdf=Nothing
>
> Give that a try first.
>
> -- Andy Lacey
> http://www.minstersystems.co.uk
>
>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>> Tina Norris Fields
>> Sent: 26 June 2008 19:21
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] Two more issues on my Word document automation
>>
>>
>> Hi Andy,
>>
>> The OnClick event procedure of the button btnMakeTableContribs is as
>> follows:
>> = = = = =
>> Private Sub btnMakeTableContribs_Click()
>> On Error GoTo Err_btnMakeTableContribs_Click
>>
>> Dim stDocName As String
>>
>> stDocName = "qryPrintListForContributionTypeOfMember_t"
>> DoCmd.SetWarnings False
>> DoCmd.OpenQuery stDocName, acNormal, acEdit
>> DoCmd.SetWarnings True
>> MsgBox "Done"
>>
>> Exit_btnMakeTableContribs_Click:
>> Exit Sub
>>
>> Err_btnMakeTableContribs_Click:
>> MsgBox Err.Description
>> DoCmd.SetWarnings True
>> Resume Exit_btnMakeTableContribs_Click
>>
>> End Sub
>> = = = = =
>>
>> As you see, it does use the DoCmd.OpenQuery. What would be a
>> better way
>> to do it? Isn't there a 'no data' sort of property or result I could
>> use? (Don't know what it's called.)
>>
>> Teach me - I'm eager to learn! Thanks,
>>
>> Tina
>>
>>
>> Andy Lacey wrote:
>>
>>> Hi Tina
>>> Don't know about point 1 but point 2 shouldn't be too hard.
>>>
>> What's the
>>
>>> code look like that runs the query? If you're using a querydef then
>>> that gives you a .RecordsAffected property after executing
>>>
>> and you can
>>
>>> test for that being zero. You can't do that if you're sing
>>> Docmd.Openquery. So how're you doing it at the moment?
>>>
>>> Is point 1 some security setting in Word perhaps?
>>>
>>> -- Andy Lacey
>>> http://www.minstersystems.co.uk
>>>
>>>
>>>
>>>> -----Original Message-----
>>>> From: accessd-bounces at databaseadvisors.com
>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>>>> Tina Norris Fields
>>>> Sent: 25 June 2008 21:45
>>>> To: AccessD at databaseadvisors.com
>>>> Subject: [AccessD] Two more issues on my Word document automation
>>>>
>>>>
>>>> Hi again,
>>>>
>>>> First, thanks for the wonderful help so far. My form is
>>>>
>> coming along
>>
>>>> very nicely. The user selects the date from a calendar control for
>>>> records to be processed and clicks a button to set that date.
>>>> The user
>>>> then clicks a button to run the make-table query, a message
>>>> box appears
>>>> to let the user know that is completed, and the user clicks OK to
>>>> dismiss message box. The user then clicks a button to
>>>>
>> launch Word and
>>
>>>> open the contributions merge letter. So far, so good.
>>>>
>>>> Two issues:
>>>>
>>>> 1. The Word document is a merge form letter expecting data from
the
>>>> temporary table in the database. I would like to dismiss
>>>>
>> the message
>>
>>>> box that pops up asking the user to confirm that he (or
>>>>
>> she) wants the
>>
>>>> form letter to accept the data from the database - that is, I
>>>> don't want
>>>> that message box to appear, but I don't see where in the
>>>>
>> Word document
>>
>>>> to turn it off. If it's something I can set for this
>>>>
>> document, does
>>
>>>> anybody know where that setting is? If I can't set it in the Word
>>>> document, is there a way for me to turn off that message box
>>>> programmatically from the database?
>>>>
>>>> 2. Other issue - if a user selects a date for which there are no
>>>> records, and clicks the button to run the query, I want the
>>>> message box
>>>> to tell the user there are no records for that date.
>>>>
>>>> If all this works well, a user will get into the Word
>>>> document, ready to
>>>> complete the merge, with 4 mouse-clicks. That will make my
>>>>
>> friends at
>>
>>>> Habitat very happy. Down the road, when I am confident the
>>>>
>> automation
>>
>>>> is working right, and the BE and FE are properly in place,
>>>>
>> I want to
>>
>>>> combine the actions of the buttons on the form so that the
>>>>
>> user will
>>
>>>> simply pick the appropriate date and click a GO button. But, as I
>>>> mentioned earlier, we are trying to live in this house
>>>>
>> while we gut it
>>
>>>> and totally remodel it - so I will be very happy with incremental
>>>> improvements.
>>>>
>>>> Thanks again for all the help,
>>>> Tina
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> AccessD mailing list
>>>> AccessD at databaseadvisors.com
>>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>>> Website: http://www.databaseadvisors.com
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>>
>>
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com