roz.clarke at barclays.com
roz.clarke at barclays.com
Sun Mar 25 05:45:45 CDT 2012
They want to be able to sort, use all the filter and find functionality, re-order and re-size the columns. They're trying to find pairs or groups of items that 'belong together', and the judgement about their matching is very human (we will already have done all the programmatic matching we can). They're used to doing this exercise in Excel. I'm starting to wonder if I should go back to Excel actually, but I'm not great with it and we haven't got much time. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: 25 March 2012 02:27 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Disconnected recordset as form source? That was my thought until I saw "it's got about 100k rows in it" -- Stuart On 24 Mar 2012 at 15:52, Doug Murphy wrote: > Not sure what the overall objective is or required functionality from > the continuous form but a list box would make the selection part trivial. > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > roz.clarke at barclays.com > Sent: Saturday, March 24, 2012 11:18 AM > To: accessd at databaseadvisors.com > Subject: [AccessD] Disconnected recordset as form source? > > Hello all! > > I'm doing a very rare, for me, bit of work on a database, and I'm > trying to make a continuous form give me extended multiselect > functionality, so that the users can use filter by form etc., then > choose a bunch of records to perform an action on. They need to be able to select discontiguous records. > I'm finding it a bit challenging... > > The back end will eventually be on SQL Server but I'm doing a demo > version with an Access 2000 back end. > > I can see that I could do it by copying the working data into a new, > local, table, adding a 'selected' column, and using this, bound to my > continuous form, to identify the records for manipulation. However to > synchronise with other user updates (there will be a couple hundred > people writing updates) that table's going to get deleted and > re-created every two minutes which is going to cause a bit of bloat (it's got about 100k rows in it). > > I had thought that I could achieve the same thing with disconnected > recordsets. On paper it works elegantly; pull the PK & other key > fields > + the 'selected' field through at the start of the session, bind a > search form to the recordset and drop the connection, have the users > search for the records they want to work on, let them make their > selection of rows to go ahead and edit, and only then do I pop them > into a bound form based on their pick of records. No changes in the > disconnected rst ever need to be written back to the table. > > This is the code opening the rst, binding the form and dropping the cnn: > > Dim cnn As New ADODB.Connection > Dim rstExceptions As New ADODB.Recordset > > > Private Sub Form_Open(Cancel As Integer) > > Set cnn = CurrentProject.Connection > > rstExceptions.CursorLocation = adUseClient rstExceptions.Open "SELECT > * from tblUnmatched_Combined", cnn, adOpenKeyset, > adLockBatchOptimistic > > Set Me.Recordset = rstExceptions > > Set rstExceptions.ActiveConnection = Nothing cnn.Close > > End Sub > > BUT OF COURSE it doesn't quite work; I'm getting the error message > "This record has been changed but the updated data will not be > displayed because it doesn't satisfy the criteria of the underlying recordsource." > > It's random; you can update 3 rows happily and then one fails, come > back to that one a few seconds later and it succeeds. So it's *not* the data. > The 'help' says this can be a problem you get with SQL Server > recordsets, but this ain't a SQL Server recordset so... > > Anyone tried something like this and willing to chew it over with me? > > It's good to be back. > > Roz > This e-mail and any attachments are confidential and intended solely > for the addressee and may also be privileged or exempt from disclosure > under applicable law. If you are not the addressee, or have received > this e-mail in error, please notify the sender immediately, delete it > from your system and do not copy, disclose or otherwise act upon any > part of this e-mail or its attachments. > > Internet communications are not guaranteed to be secure or virus-free. > The Barclays Group does not accept responsibility for any loss arising > from unauthorised access to, or interference with, any Internet > communications by any third party, or from the transmission of any > viruses. Replies to this e-mail may be monitored by the Barclays Group > for operational or business reasons. > > Any opinion or other information in this e-mail or its attachments > that does not relate to the business of the Barclays Group is personal > to the sender and is not given or endorsed by the Barclays Group. > > Barclays Bank PLC. Registered in England and Wales (registered no. > 1026167). > Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom. > > Barclays Bank PLC is authorised and regulated by the Financial > Services Authority. > > > -- > 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 This e-mail and any attachments are confidential and intended solely for the addressee and may also be privileged or exempt from disclosure under applicable law. If you are not the addressee, or have received this e-mail in error, please notify the sender immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments. Internet communications are not guaranteed to be secure or virus-free. The Barclays Group does not accept responsibility for any loss arising from unauthorised access to, or interference with, any Internet communications by any third party, or from the transmission of any viruses. Replies to this e-mail may be monitored by the Barclays Group for operational or business reasons. Any opinion or other information in this e-mail or its attachments that does not relate to the business of the Barclays Group is personal to the sender and is not given or endorsed by the Barclays Group. Barclays Bank PLC. Registered in England and Wales (registered no. 1026167). Registered Office: 1 Churchill Place, London, E14 5HP, United Kingdom. Barclays Bank PLC is authorised and regulated by the Financial Services Authority.