Tina Norris Fields
tinanfields at torchlake.com
Fri Jun 27 12:18:22 CDT 2008
Excellent, very cool. Thank you. I may learn how to do this stuff, yet
- with help from people like you.
Tina
Andy Lacey wrote:
> 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
>
>