If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Best strategy for MU data entry form with multiple record subform
Hi All
Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! Any and all suggestions appreciated. Ali Kwok |
#2
|
|||
|
|||
Why use a transaction? That seems to be the place where you are making this
more difficult that it needs to be. Instead, set up the relation with a cascading delete between the main order table and the line items table. If the user "abandons" the order entry (i.e. deletes the main form entry), the related line order items are deleted as well. So, no transaction is needed for deletes. As the user adds new records to the line items, no transaction is needed. If the user edits records in the line items, no transaction is needed. You're done, and you're avoided the whole puzzle of trying to manage simultaneous uncommitted transactions beyond the single-record buffering and optimistic writes that Access does so well. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AliKwok" wrote in message ... Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! Any and all suggestions appreciated. Ali Kwok |
#3
|
|||
|
|||
Thanks for your help, Allen - it's not the first time.
I follow the cascading deletes logic OK, but I also need users to be able to abandon ANY edits - whether or not they have moved between rows in the subform. I don't see how I can avoid committing such changes without using a transaction(?) The 'complete reversion' functionality is needed to enable rework of order quantities etc to test the effects on pricing and delivery time - while the customer waits on the phone - with the confidence to be able to leave the original order untouched if necessary. Thank you Ali "Allen Browne" wrote: Why use a transaction? That seems to be the place where you are making this more difficult that it needs to be. Instead, set up the relation with a cascading delete between the main order table and the line items table. If the user "abandons" the order entry (i.e. deletes the main form entry), the related line order items are deleted as well. So, no transaction is needed for deletes. As the user adds new records to the line items, no transaction is needed. If the user edits records in the line items, no transaction is needed. You're done, and you're avoided the whole puzzle of trying to manage simultaneous uncommitted transactions beyond the single-record buffering and optimistic writes that Access does so well. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AliKwok" wrote in message ... Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! Any and all suggestions appreciated. Ali Kwok |
#4
|
|||
|
|||
Okay. You have a specific goal in mind.
But there still might be an easier way to handle the stock quantity issues. One way is to handle the stock quantities dynamically, i.e. don't store the stock values, beyond a known point such as a stock take or start-of-day value. Another is illustrated by John Viescas in Building MS Access Applications (Microsoft Press, 2003), which includes a sample inventory management application. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AliKwok" wrote in message ... Thanks for your help, Allen - it's not the first time. I follow the cascading deletes logic OK, but I also need users to be able to abandon ANY edits - whether or not they have moved between rows in the subform. I don't see how I can avoid committing such changes without using a transaction(?) The 'complete reversion' functionality is needed to enable rework of order quantities etc to test the effects on pricing and delivery time - while the customer waits on the phone - with the confidence to be able to leave the original order untouched if necessary. Thank you Ali "Allen Browne" wrote: Why use a transaction? That seems to be the place where you are making this more difficult that it needs to be. Instead, set up the relation with a cascading delete between the main order table and the line items table. If the user "abandons" the order entry (i.e. deletes the main form entry), the related line order items are deleted as well. So, no transaction is needed for deletes. As the user adds new records to the line items, no transaction is needed. If the user edits records in the line items, no transaction is needed. You're done, and you're avoided the whole puzzle of trying to manage simultaneous uncommitted transactions beyond the single-record buffering and optimistic writes that Access does so well. "AliKwok" wrote in message ... Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! |
#5
|
|||
|
|||
If the Order is abandonded (deleted), the SCENARIOS are moot. The
records would be deleted. I would probably approach it from the standpoint of creating a duplicate purchase order which can be changed around in any such way. If you don't already have a [STATUS] field in the SALES ORDER header table, add one and create a new [STATUS] such as 'SCENRIO' or 'DUPLICATE' something that separates it out from being a LIVE Sales Order. If the user wants to discard the changes, you would just delete the duplicate sales order. If the user wants to save the changes, you would run an UPDATE query to update the line items for the LIVE sales order to the values of the line items for the DUPLICATE sales order. Assuming of course that you want to keep the SALES ORDER NUMBER the same. Otherwise, it would be a matter of updating the [STATUS] of the first to 'Cancelled' and updating the status of the duplicate to 'Active/Live'. AliKwok wrote: Thanks for your help, Allen - it's not the first time. I follow the cascading deletes logic OK, but I also need users to be able to abandon ANY edits - whether or not they have moved between rows in the subform. I don't see how I can avoid committing such changes without using a transaction(?) The 'complete reversion' functionality is needed to enable rework of order quantities etc to test the effects on pricing and delivery time - while the customer waits on the phone - with the confidence to be able to leave the original order untouched if necessary. Thank you Ali "Allen Browne" wrote: Why use a transaction? That seems to be the place where you are making this more difficult that it needs to be. Instead, set up the relation with a cascading delete between the main order table and the line items table. If the user "abandons" the order entry (i.e. deletes the main form entry), the related line order items are deleted as well. So, no transaction is needed for deletes. As the user adds new records to the line items, no transaction is needed. If the user edits records in the line items, no transaction is needed. You're done, and you're avoided the whole puzzle of trying to manage simultaneous uncommitted transactions beyond the single-record buffering and optimistic writes that Access does so well. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AliKwok" wrote in message ... Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! Any and all suggestions appreciated. Ali Kwok |
#6
|
|||
|
|||
(Additional Comment on my last post)...
There is no 'BEST' strategy. There are multiple ways of accomplishing the same task. The strategy choosen should be the one that works in light of your specific requirements and is consistent with the overall design & approach of your solution. AliKwok wrote: Thanks for your help, Allen - it's not the first time. I follow the cascading deletes logic OK, but I also need users to be able to abandon ANY edits - whether or not they have moved between rows in the subform. I don't see how I can avoid committing such changes without using a transaction(?) The 'complete reversion' functionality is needed to enable rework of order quantities etc to test the effects on pricing and delivery time - while the customer waits on the phone - with the confidence to be able to leave the original order untouched if necessary. Thank you Ali "Allen Browne" wrote: Why use a transaction? That seems to be the place where you are making this more difficult that it needs to be. Instead, set up the relation with a cascading delete between the main order table and the line items table. If the user "abandons" the order entry (i.e. deletes the main form entry), the related line order items are deleted as well. So, no transaction is needed for deletes. As the user adds new records to the line items, no transaction is needed. If the user edits records in the line items, no transaction is needed. You're done, and you're avoided the whole puzzle of trying to manage simultaneous uncommitted transactions beyond the single-record buffering and optimistic writes that Access does so well. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "AliKwok" wrote in message ... Just how do developers sensibly handle record locking conflicts in this situation: I have a multiuser FE/BE A2002 database. There is a single form for both data entry and subsequent re-editing. The main form is bound to the Sales Order header table, and the child form to the Line detail table. I decided to use a transaction so that the user may enter/edit the entire order and save/abandon it entirely (which seems to be 'universal' behaviour for this type of interface). This is of course where it comes unstuck! I assumed (foolishly) that I could use optimistic, record-level locking and get away with it. The whole thing works beautifully for a single user, but practically, only one user at a time may enter/edit data because the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! I'm using DAO bound recordsets, because I'm only really familiar with DAO - could ADO help me out here? I wondered about using an unbound form, or bound form with unbound controls, but I can't imagine how I could display the multiple detail rows without the bound subform in datasheet mode. This must be a classic programming challenge - forgive my ignorance! Any and all suggestions appreciated. Ali Kwok |
#7
|
|||
|
|||
First, using transaction only works for you recordset code, and DOES NOT
work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#8
|
|||
|
|||
Thanks for your reply Albert.
I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#9
|
|||
|
|||
"AliKwok" wrote in message
... I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! I seem to recall that some issues can arise. First, I would remove the forms (and sub-forms) recordsouce. (I assume you built the forms with a bound table/query). You then REMOVE the data source, since you are now using recordsets. If you done the above, then I perhaps there is some issue with locking. I have not used the transaction idea in production, but I have test a form, and it did seem to work ok (but, I did notice a lock on the record that appeared *after* I did a commit. So, there certainly might be some lock issue. I would also make sure you are running a split database if not already. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
#10
|
|||
|
|||
Have you considered the suggestion that I made in another post? In the
amount of time spent banging your head on the wall to get your fax software to work, you could have easily driven the fax across the street. (ie Sometimes is better to consider a different approach) AliKwok wrote: Thanks for your reply Albert. I have indeed wrapped the form in a transaction by programmatically assigning the recordsets, and as I wrote, it was working fine for a single user, but appears to lock whole pages when 2 users are attempting to enter new records, or edit old ones. Version is A2002, as I wrote. Since my settings are No Locks/Record-level locking and the recordsets' locking setting is Optimistic, I can only suggest that 1) the Transaction must override the optimistic setting, and 2) the potential for multiple child records within the scope of the Transaction must override the record-level locking. This is only my hypothesis to explain the observed behaviour - I havn't had time to test it! Thanks for your time and thought. Ali "Albert D.Kallal" wrote: First, using transaction only works for you recordset code, and DOES NOT work for forms. In other words, starting a transaction has not relation to a form. the possibilty of multiple dirty rows in the subform appears to force Jet to switch to page-level locking...help!! Not to my knowledge at all does the above happen. However, I don't see why the above would be a problem, or related to this in any way. You don't mention what version of ms-access, but the last 3 versions of ms-access do have record locking as opposed to page locking anyway. (so, anything after a97 would not be a problem. However, even a97 is not a problem, since there is NO REASON TO HAVE ANY kind of locking enable, or set for the CHILD TABLES!!). So, this issue is moot, since why bother locking the child records in ANY WAY at all? The ONLY way you are EVER going to edit a child record is by first finding, and bringing the master record/form. The child records will then display. So, you can't get to the child records unless you edit/find the MASTER record. (so, really, you only need to lock the master record). I see ZERO reason as to why you have and child record locking in the first place. As mentioned, actually all of the above may be moot, as transactions have NOTHING to do with forms anyway. This must be a classic programming challenge - forgive my ignorance! Allowing a bail out of a master/child forms is something that ms-access does not do well. You *can* bind recordsets to a form that are wrapped in a transaction, but this assumes you have a existing design that will allow this (and that is not normal the case). So, keep in mind the issue of transactions, the issue of forms, the issue of locking...as they are all in face separate issues. Having said the above, you *can* build your own recordsets in code, and then ASSIGN these reocrdets to the form, and sub-forms reocrdset property. The link master/child settings do not work when you do this, but it is simple matter to put the assigning of the parent id in the child form in the before insert event of the child form Me!contact_id = Me.Parent!ContactID So, you can wrap a form in a transaction, but you will thus need to load up the one record into the master reocrdset, and load up the child records into a child reocrdset. This likely also means you will need some setup for the "adding" of records.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.members.shaw.ca/AlbertKallal |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Entry Into Multiple Tables Using One Form | Linda | Using Forms | 2 | October 4th, 2005 07:40 PM |
Creating a data entry form from a query | Kathryn | New Users | 1 | June 21st, 2005 09:12 PM |
format data displayed on Excel data entry form | Bob, too | Setting up and Configuration | 0 | May 19th, 2005 08:26 PM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |