Hijack Insert/Update in a continuous bound form

[Todd]

Hi All,

The quick question is does anybody know how to seamlessly substitute a
stored procedure for the regular insert/update process? I can almost
do it using the form.undo and form.refresh commands in the
beforeInsert/beforeUpdate events, but can't seem to get the record
position to return to the last user selected record after the refresh
(refreshing loses the selected record).

That's the basic question, here's my specific details:

I'm trying to create a continuous form that allows the user to easily
enter dates that relate to an Inventory.

Here's a simplified table structure:

Table: Inventory
InventoryID int

Table: InventoryDate
InventoryID
InventoryDate datetime

Here's my simplified query used to populate the form:
SELECT IDate.InventoryID,
IDate.InventoryDate
FROM Inventory AS I
LEFT JOIN InventoryDate AS IDate
ON I.InventoryID = IDate.InventoryID

There may or may not be an InventoryDate record associated with each
Inventory but because of the LEFT JOIN, all the inventory records show
up in the continuous form regardless of wether there is an
InventoryDate record associated with it.

I want the user to be able to enter InventoryDate's next to each
inventory. Access see's this as an update, but really it needs to be
an INSERT if the InventoryDate record doesn't exist.

So...I tried "hijacking" the BeforeUpdate event with some insert code
(using form.undo to stop the regular update process, and form.refresh
to show my newly inserted record).

That all works great except I don't know of any way to determine what
was the last record the user clicked on that triggered the insert. I
can return the focus to the inserted/updated record, but that is
anoying for the user because they have to click twice to go to their
next desired record.

Any ideas on other methods for the hijack, or ways of determing which
record the user selected would be greatly appreciated.

Todd

P.S. I've tried playing around the Current() and Click() events and
haven't been able to find one that gives me the last selected record.

 

[Sylvain Lafontaine]

The best way of solving this under ADP would be to use a subform instead of
a Left Join.

I don't know why you are not able to determine the last record clicked in
the Insert process as this should be the same as the current record.

If you want to substitute a stored procedure, your best chance would be to
create and use a View with ViewMetadata and an Instead Of trigger but I
never tried this myself. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp
for more information.

Another possibility would be to use the Resync command to create your new
record: instead of undoing all the fields in the form, all you will have to
do would be to undo the InventoryDate field. It is also possible that you
could use something else as the "spark" plug; for example a second dummy
InventoryDate field. However, this is a possibility that I never
investigated, too.

 

[Todd]

Those are really good ideas. I'm leaning towards the view solution,
but have some ideas for the dummy InventoryDate field as well. Thanks
for the help!

 

[aaron.kempf]

cant you do this as a disconnected recordset?

 

[Vadim Rapp]

T> Any ideas on other methods for the hijack, or ways of determing which
T> record the user selected would be greatly appreciated.

the ultimate hijack is INSTEAD OF trigger in the database, which in your
case would be (in simplified form, with PK meaning primary key)

create trigger T on table Inventory instead of UPDATE as

if exists(select 1 from inserted left outer join deleted on inserted.PK=
deleted.PK where deleted.PK is null)
insert into inventory (...) select (...) from inserted -- no update,
do insert instead
else
update inventory
set col1=inserted.col1, col2=inserted.col2,...
from inserted inner join inventory on inventory.PK =inserted.PK -- do
what was supposed to


Vadim