[AccessD] Two more issues on my Word document automation

Tina Norris Fields tinanfields at torchlake.com
Fri Jun 27 06:59:33 CDT 2008


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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> 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
>>
>>
>>     
>
>
>
>   



More information about the AccessD mailing list