William Hindman
dejpolsys at hotmail.com
Thu Jun 16 16:24:43 CDT 2005
..if you're selecting a template from a combo why not have it open another cascading combo with the options for the selected template rather than a lengthy select operation which requires recoding every time you change something? 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 4:54 PM Subject: RE: [AccessD] Select case is too cumbersome >I don't follow. I am selecting from a combo box the name of an excel > template to be filled. The template may require data from one or more > recordsets. Additionally, special formatting may be necessary. The way I > identify the unique code required by each template to open the separate > recordsets, paste the data, do the formatting etc. is by assigning it a > select case no which is stored in a table. Make sense? > Jim Hale > > -----Original Message----- > From: William Hindman [mailto:dejpolsys at hotmail.com] > Sent: Thursday, June 16, 2005 3:15 PM > To: Access Developers discussion and problem solving > Subject: Re: [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 >> > -- > 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 >