[AccessD] Select case is too cumbersome

..I'm missing something here (not the first time) ...but why are you using 
a select here rather than building a combo on the fly and using that 
instead? ...rename all the applicable queries from qryxxx to zqryxxx and 
fill your combo with a qry on zqryxxx ...much faster than a select and you 
don't have to recode it everytime you add a new qry.

William

----- Original Message ----- 
From: "Hale, Jim" <Jim.Hale at fleetpride.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Thursday, June 16, 2005 3:29 PM
Subject: RE: [AccessD] Select case is too cumbersome


>I think what you suggest is doable. I have created a class that handles
> opening and saving the Excel spreadsheet. I think  much of the remaining
> code deals with opening recordsets and pasting data. I think I'll create a
> table to hold all the query names and work sheet names and execute the 
> paste
> function as a method from within my class. This will still leave a fairly
> bodacious, if somewhat slimmed down, select statement. The trick as you
> suggest is to identify the truly repeating elements and build functions.
> Anyone used used the EVAL function like the Article I mentioned suggests?
>
> Jim Hale
>
> -----Original Message-----
> From: Josh McFarlane [mailto:darsant at gmail.com]
> Sent: Thursday, June 16, 2005 1:18 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Select case is too cumbersome
>
>
> On 6/16/05, Hale, Jim <Jim.Hale at fleetpride.com> wrote:
>> I have a form which lists different Excel workbooks I can populate. When 
>> I
>> make a selection from the form I read the path to the appropriate excel
>> template  and the select case number from a table. I pass these to a
>> function  "Public Function Load_Financials(intCaseno As Integer,
> strpathname
>> As String)" for processing. The function opens the appropriate Excel
>> template and, based upon the select case number, populates the template
> from
>> various recordsets and formats the workbook as appropriate. All of this
>> works fine. My problem is that I am up to case 40 in my select statement.
>> Many of the cases are quite lengthy so Load_Financials is getting out of
>> hand. 5 cases was no big deal, 40 and 500 lines of code is a bit much.
>> Surely there is a more elegant way to arrange this. For example, I read
> "Two
>> functions you don't use enough" in this month's Smart Access which
> suggested
>> each case can be placed in its own function and the EVAL function can be
>> used to execute them. How do ya'll handle this issue?
>>
>> Jim Hale
>>
> If you're just speaking from an Access perspective, I'd haev one
> function with just the SELECT / CASE statement, then for each case,
> call a seperate function for the differences.
>
> If some of your cases have the same code for chunks, you could group
> them in one function and call the function in the Case statement.
>
> For example, creating an excel file, formating a header, formatting
> the data, then saving, you could seperate it into 4 functions: Create
> file (before the case), Format Header and Format Data within the case,
> then Save File / Write Data at the end.
>
> This way you would have a set of stable functions and can save
> yourself from redundant work / large files.
>
> -- 
> Josh McFarlane
> "Peace cannot be kept by force. It can only be achieved by understanding."
> -Albert Einstein
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> ***********************************************************************
> The information transmitted is intended solely for the individual or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or
> other use of or taking action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited.
> If you have received this email in error please contact the sender and
> delete the material from any computer. As a recipient of this email,
> you are responsible for screening its contents and the contents of any
> attachments for the presence of viruses. No liability is accepted for
> any damages caused by any virus transmitted by this email.


--------------------------------------------------------------------------------


> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>