MS Access Multiuser - Re: Best strategy for MU data entry form with multiple record subf

Author

Re: Best strategy for MU data entry form with multiple record subf

AliKwok

2005-08-22, 7:25 am

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" <AliKwok@discussions.microsoft.com> wrote in message
> news:015EABB8-276E-487F-96AC- 92C1227268B1@microso
ft.com...
>
>
>

Allen Browne

2005-08-22, 7:25 am

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" <AliKwok@discussions.microsoft.com> wrote in message
news:37E5A702-5ED9-40CE-9547- 13ABB98E87AF@microso
ft.com...[color=darkred]
> 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:
>

David C. Holley

2005-08-22, 11:24 am

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:[color=darkred
]
> 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:
>
>

David C. Holley

2005-08-22, 11:24 am

(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:[color=darkred
]
> 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:
>
>

Sponsored Links