R
Rick Roberts
Acc 2003 - ADP
I would like to have a continuous form that displays Inventory items and
allows the user to enter the current quantity on hand for each displayed
record. So far – So good. What I would also like is to prevent Access from
attempting to update the record. I have a Stored Procedure I would much
rather call from my code to handle the update of the appropriate fields /
related tables.
If I setup the form with a recordset type as “Snapshot†(can’t update) add
a unbound text box, as soon as I enter a quantity for one it changes it for
all displayed records. If I bind the control directly to my QtyOnHand
column, I can’t update the field at all.
So the recordset type must be “Updatable Snapshot†and the control must be
bound.
What I have tried is writing code in the Before Update event that
Calls the special procedure that updates the count
Me.Undo - So that Access won’t update it
DoCmd.CancelEvent – Cancel the Update event – not sure if this is needed
after the undo
Me.Refresh – to get the current count from the inventory table as updated
by the special procedure
Call RecordSearch_Navigation(Me, SearchFor) – Navigate back to the record I
was on
This seems to work but I am having to make several trips to the Server
Database for each update. That probably can’t be avoided but this solution
seems very convoluted.
Is there a simplier answer? I would hope so!
Any suggestions or ideas?
I would like to have a continuous form that displays Inventory items and
allows the user to enter the current quantity on hand for each displayed
record. So far – So good. What I would also like is to prevent Access from
attempting to update the record. I have a Stored Procedure I would much
rather call from my code to handle the update of the appropriate fields /
related tables.
If I setup the form with a recordset type as “Snapshot†(can’t update) add
a unbound text box, as soon as I enter a quantity for one it changes it for
all displayed records. If I bind the control directly to my QtyOnHand
column, I can’t update the field at all.
So the recordset type must be “Updatable Snapshot†and the control must be
bound.
What I have tried is writing code in the Before Update event that
Calls the special procedure that updates the count
Me.Undo - So that Access won’t update it
DoCmd.CancelEvent – Cancel the Update event – not sure if this is needed
after the undo
Me.Refresh – to get the current count from the inventory table as updated
by the special procedure
Call RecordSearch_Navigation(Me, SearchFor) – Navigate back to the record I
was on
This seems to work but I am having to make several trips to the Server
Database for each update. That probably can’t be avoided but this solution
seems very convoluted.
Is there a simplier answer? I would hope so!
Any suggestions or ideas?