Tina Norris Fields
tinanfields at torchlake.com
Thu Jun 26 17:22:38 CDT 2008
Joe,
Thank you for that thought. I will play with that, too. There is
something I don't get, though - why is it good to use a function that
references the textbox on the form, but not good to reference that
textbox on the form in a query? What am I accomplishing by making this
change?
Thanks again,
Tina
Joe O'Connell wrote:
> 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
>>>
>>>
>>>
>>>
>>
>>