Andy Lacey
andy at minstersystems.co.uk
Fri Jun 27 07:38:37 CDT 2008
Hi Tina
That's what the querydef gives you, the ability to know if anthing was
written. So:
Set qdf=Currentdb.Querydefs("MyDeleteQuery")
Qdf.Execute
Set Qdf=Currentdb.Querydefs("MyAppendQuery")
Qdf.Parameters...
Qdf.Execute
If Qdf.RecordsAffected=0 Then
MsgBox("Nothing there")
Else
Msgbox("Done")
End If
--
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: 27 June 2008 13:00
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Two more issues on my Word document automation
>
>
>Hi Andy,
>
>Okay, now that makes sense to me - "resolving the parameter
>and passing
>it as a value." Thanks very much.
>
>So, instead of using a make-table query to capture the records I need,
>craft the temporary table and keep it whole, delete all the records in
>it with the first query and append the appropriate records with the
>second query. That makes sense. I like the elegance of that.
>
>If the [DepositDate] criterion, selected from my calendar control,
>produces no records, I want to generate a message box that says "There
>are no records for this date." What I've thought of is a query that
>returns the count of records in the temporary table, which a condition
>loop will check for. If the number is >0 the document merge will
>proceed. If not, the message box should appear. If I were generating
>an Access report, I'd put this stuff in the "On No Data" event of the
>report. In my setup, I'm thinking about when and where to do this
>checking and generate the message. I just know this error
>will pop up -
>my local Habitat does their deposits on Fridays. But, fingers do slip
>and type wrong dates (I found an entry in their contributions
>table for
>the year 2020!!!! Hmm, some error trapping or data-validation needed
>here!) And, someday in the future, they may switch deposit days - who
>knows?
>
>Do you have any further wisdom to offer me? Thanks very much for the
>help you've already given.
>
>Kind regards,
>Tina
>
>Andy Lacey wrote:
>> 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
>>>>>>>>
>>>>>>>>
________________________________________________
Message sent using UebiMiau 2.7.2