pcs at azizaz.com
pcs at azizaz.com
Thu Nov 15 00:11:44 CST 2007
Darren, Sorry, no immediate suggestion to your question about the calculated text controls in the Footer... I don't fully understand how you have designed your Form... We normally split the UI into a Summary Form and a Detail Form... The Summary Form is a continous form that also allow us to scroll horizontal across the columns of a row.... There are usually a number of predefined selection criteria in the Form Footer (a combo box served up by an auxiliary table holding information about SPs that corresponds to each of these predefined selection criteria)..... On the Summary Form above the text control and label that forms each column we have another unbound text control that functions as a 'filter' - the user can enter filter criteria in any of these controls - if an ad hoc 'filter' like that is present it will work in conjunction with the predefined selection criteria that has been selected. The code module will then fetch the chunks of SQL that was used to create the SP (the auxiliary table holds the PK - Description - Name of SP - "Select Part" of SP in memo field - "Where Part" of SP in memo field - "Order By" Part in memo field) and along with the ad hoc filter provided the code will build the SQL string that now is to be used to fetch the record set from the Server instead of the straightforward SP. Using ADO recordset.... Clicking on a column will open the Detail Form on the PK of the Summary Form record... Using query on ODBC linked table(s) limited to one record selected by the PK provided.... On the Detail Form we have a combo box that gets populated with the sub set of records from the Summary Form limited to the first 100 if the Summary Form shows more than 100 records.... So, rather than scrolling on the Detail Form, the User clicks on the combo box to select another record from the subset arrived at on the Summary Form. On the Summary Form you normally show all the essential static information about the records at hand plus any additional fields that is relevant to search on ..... This provides the User with a very effective way of quickly drilling down to the subset of records that the User will be working on in more detail.... The record set on the Summary Form can be made up of any complex (and non-updatable) query .... I don't know if this is relevant to you... Borge ---- Original message ---- >Date: Thu, 15 Nov 2007 16:10:49 +1100 >From: "Darren D" <darren at activebilling.com.au> >Subject: Re: [AccessD] Getting Results from SQL SVR into a Continuous form >To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com> > >Hi Borge > >Thanks for the reply - I can see this is very efficient compared to the way I >was doing it >And - It works - kinda >If I use code similar to > > Dim strForm As String > strForm = "frmRunDetails" > DoCmd.OpenForm strForm '"frmRunDetails" > Set Forms(strForm).Recordset = rs1 > >And ensure the form is not bound to any table or query - I get only 1 row >showing at a time >Even though the form is a continuous form and there are plenty of rows to show >I can scroll through all the records but only displaying one row at a time > >Is there a trick to get the form to show say 20 records of say 200 then let the >user scroll as normal through 180 or so not showing? > >Also I have text boxes at the footer of the form showing up with #Error using >the set RS method- They have things like...="Total Runs: " & Count([RunNo]) as >their control source >Any way around this too? > >Many thanks > >DD > >-----Original Message----- >From: accessd-bounces at databaseadvisors.com >[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of pcs at azizaz.com >Sent: Tuesday, 13 November 2007 11:27 AM >To: Access Developers discussion and problemsolving >Subject: Re: [AccessD] Getting Results from SQL SVR into a Continuous form > >Darren, >Try: > >Set Forms(strFrm).Recordset = rs > >where rs is the recordset you have created and strForm is a >string variable holding the name of your Form > >Regards >Borge > >---- Original message ---- >>Date: Tue, 13 Nov 2007 11:06:46 +1100 >>From: "Darren D" <darren at activebilling.com.au> >>Subject: Re: [AccessD] Getting Results from SQL SVR into a >Continuous form >>To: "'Access Developers discussion and problem solving'" ><accessd at databaseadvisors.com> >> >>Hi Arthur >> >>This sounds very efficient - Cool >> >>Now the tricky bit... >> >>How do I 'bind' a continuous form in access 2003 (NOT ADP) to >a SPROC or view in >>SQL SVR? >> >>Thanks >> >>Darren >>-----Original Message----- >>From: accessd-bounces at databaseadvisors.com >>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of >Arthur Fuller >>Sent: Tuesday, 13 November 2007 10:53 AM >>To: Access Developers discussion and problem solving >>Subject: Re: [AccessD] Getting Results from SQL SVR into a >Continuous form >> >>Why not eliminate both at once and instead create a sproc or >view and use it >>as the record source? >> >>A. >> >>On 11/12/07, Darren D <darren at activebilling.com.au> wrote: >>> >>> Hi team >>> >>> >>> >>> Access 2003 >>> >>> >>> >>> I am running select statements to get results from an SQL dB >>> >>> >>> >>> Then looping through the rs object and populating temp >tables with the >>> results - >>> Then I am 'binding' my forms to these temp tables for display >>> >>> >>> >>> This all works fine - no Prob - I was just wondering if it >is possible to >>> cut >>> out the middle man - >>> >>> >>> >>> I mean ditch the temp tables and somehow populate a >continuous form with >>> the >>> various results straight from the rs object >>> >>> >>> >>> EG Select AccountNo, AccountName, ContactPerson from >tblAccounts may >>> return >>> say.10 records each with 3 fields of data >>> >>> >>> >>> How then do I get these results from the rs object in code >into a >>> continuous >>> form without populating a temp table first? >>> >>> >>> >>> Also - Are there any advantages doing it this way or is >using a temp table >>> perfectly reasonable? >>> >>> >>> >>> Many thanks in advance >>> >>> >>> >>> Have a great day >>> >>> >>> >>> Darren >>> >>> >>> >>> >>> >>> -- >>> 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 >-- >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