Bookmark/cmdDelete Record in a Sub-Form

[Andy]

Hi;

It is time to overcome this obstacle.

Have an sfrm, (datasheet), that records what a customer purchases; both
inventory items and services. The user selects either inventory item or
services from a combo box list, (cbxProductType), then selects an item from
a second combo box, then enters the quantity. When the control txtQty loses
focus it triggers an Update qry that removes that many inventory items from
stock. That works perfectly.

If the user must remove a record from the sfrm the products have to be
returned to stock so the Update qry was reversed, (instead of minusing it
added).
To trigger that qry created a cmdbutton. It also works perfectly.

Now the obstacle.

Want to have the cmdbutton also automatically remove that particular record
from the sfrm.
Know that DoCmd.DoMenuItem acFormBar, acEditMenu, acSelectRecord will
select that record, but when the user clicks on the cmdbutton he is no
longer in the sfrm he is on the parent.

Believe that the answer is to create a bookmark when cbxProductType gets the
focus and then have the cmdbutton trigger an event that goes to that
bookmark after it triggers the update qry to return the items to stock.

Have read and read and read and read and read and want to finish this before
the next ice age. Have tried modifying examples from Microsoft and just not
getting it.

Have tried this: (Tried many more, this seems closest)
In txtQty_GotFocus:
Dim varBookmark As Variant
varBookmark = .Bookmark

In cmdButton_Click
Dim varBookmark As Variant
Bookmark = varBookmark

Err msg returning."Not a valid bookmark".

Would someone be so kind to point me in the correct direction. Is there a
better solution?

Thank You.

Andy

 

[Allen Browne]

Andy, this issue is one that many developers face, but I do not believe you
can get it working satisfactorily with the approach you are trying.

If you use the LostFocus update of the control to subtract the quantity from
inventory:
a) The event will fire *every* time the control is visited, not once for the
record;
b) If the user changes the quantity before saving the wrong quantity will
have been subtracted;
c) The user may later change the product, the subtract will happen again for
the new product, but the previous subtract is not restored;
d) If the entry is aborted, the subtraction is not restored.
e) If an existing record is edited, the subtraction happens again.
f) If an existing record is deleted, the quantity is not restored.
g) Even if you manage to sort out all the above, if any write fails ever,
your quantity gradually becomes more and more wrong over time, with no way
to trace and fix it.

It really needs a completely different approach. For an introduction, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
For a more comprehensive approach to assigning inventory, see the sample
database included with John Viescas' book "Microsoft Office Access 2003
Inside Out" (Microsoft Press, 2005.) The sample database alone is worth the
price of the book, and you can consider the book to be invaluable
documentation.

 

[Andy]

Allen;

Thank You the information.

You must have "ESP", same things running through my mind for days now. What
IF this. What IF that. That is why I asked: "Is there a better solution?"

Have already invested in a few of Rick Dobson's books. Its time to invest
in John Viescas' book.

Andy