Andy Lacey
andy at minstersystems.co.uk
Fri Jun 27 01:33:39 CDT 2008
Hi again Tina
I'm on sticky ground on the technicalities of this but when you're using a
querydef you're using SQL in a more, I suppose, native way. It knows a lot
less about Access and doesn't recognise constructs like =Forms. The way to
overcome this is easy though. Change your query so that in place of
[Forms]![frmContributionAcknowledge]![txtDepositDate] as the criterion in
the query put something like [GetTheDate]. Anything will do, in [], just
avoid anything that might be a keyword. This just tells SQL there's a
parameter of that name that's needed. Then run the querydef like this:
set qdf=currentdb.QueryDefs("qryPrintListForContributionTypeOfMember_t")
Qdf.parameters("GetTheDate")=Forms![frmContributionAcknowledge]![txtDepositD
ate]
Qdf.Execute
What you're doing is resolving the parameter here and passing it as a value
to the query.
BTW if it's a Make Table you're going to have to delete it before running
every time. Is it actually necessary to have a Make Table. What I'd normally
do is keep the table the whole time and have a query which removes all
records then a second to append to it. So you'd go
Set qdf=Currentdb.Querydefs("MyDeleteQuery")
Qdf.Execute
Set Qdf=Currentdb.Querydefs("MyAppendQuery")
Qdf.Parameters...
Qdf.Execute
-- 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 23:23
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Two more issues on my Word document automation
>
>
>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
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>