By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,580 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,580 IT Pros & Developers. It's quick & easy.

Override Access update with stored procedure

P: n/a
wondered if anyone might lend a hand? I'm having a little difficulty
updating the backend SQL Server tables. Normally I just use Access'
update but I'd really like to try something new.

I'd like to be able to override Access' update, capture the updated
fields from the subform controls (text boxes) and pass the updated
fields to the stored procedure to update the table.

I've tried the subforms before update event...see the following:

***Start***
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim vNewID As String
Dim vNewName As String
Dim vWF_Ref_ID As Long

If IsNull(Me!NewID.Value) Then vNewID = "" Else vNewID =
Me!NewID.Value
If IsNull(Me!NewName.Value) Then vNewName = "" Else vNewName =
Me!NewName.Value
If IsNull(Me!WF_Ref_ID.Value) Then vWF_Ref_ID = -1 Else vWF_Ref_ID =
Me!WF_Ref_ID.Value

'MsgBox vNewID & ", " & vNewName

DoCmd.CancelEvent
'SendKeys "{ESC}{ESC}"
Me.Dirty = False

'DoCmd.GoToRecord , , acNewRec

Exit Sub
errHandler:
' nothing here yet

End Sub

****End****
The commented out portions are some of the other things I've tried.
Bascially, I need to capture the updated NewID and NewName and pass
them as parameters to the stored procedure. The Primary Key on the
view is WF_Ref_ID.

I can't even move to the next record after I make changes in the
subform.

Also, I understand that ADP might be another option but the business
rules don't allow for it.

Any assistance would be greatly appreciated.

Regards,

Paul
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
mi*******@yahoo.com (Paul) wrote in
news:69**************************@posting.google.c om:
wondered if anyone might lend a hand? I'm having a little
difficulty updating the backend SQL Server tables. Normally I just
use Access' update but I'd really like to try something new.

I'd like to be able to override Access' update, capture the
updated fields from the subform controls (text boxes) and pass the
updated fields to the stored procedure to update the table.


Use an unbound form.

Load a recordset with the needed record.

Programattically read the data from the recordset into the fields.

Edit the fields.

When done, have a SAVE button that writes the data from the controls
to the back end with a SQL UPDATE.

That's the basics -- in reality, it's rather complex to implement.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #2

P: n/a
Paul wrote:
wondered if anyone might lend a hand? I'm having a little difficulty
updating the backend SQL Server tables. Normally I just use Access'
update but I'd really like to try something new.

I'd like to be able to override Access' update, capture the updated
fields from the subform controls (text boxes) and pass the updated
fields to the stored procedure to update the table.

I've tried the subforms before update event...see the following:

***Start***
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim vNewID As String
Dim vNewName As String
Dim vWF_Ref_ID As Long

If IsNull(Me!NewID.Value) Then vNewID = "" Else vNewID =
Me!NewID.Value
If IsNull(Me!NewName.Value) Then vNewName = "" Else vNewName =
Me!NewName.Value
If IsNull(Me!WF_Ref_ID.Value) Then vWF_Ref_ID = -1 Else vWF_Ref_ID =
Me!WF_Ref_ID.Value

'MsgBox vNewID & ", " & vNewName

DoCmd.CancelEvent
'SendKeys "{ESC}{ESC}"
Me.Dirty = False

'DoCmd.GoToRecord , , acNewRec

Exit Sub
errHandler:
' nothing here yet

End Sub

****End****
The commented out portions are some of the other things I've tried.
Bascially, I need to capture the updated NewID and NewName and pass
them as parameters to the stored procedure. The Primary Key on the
view is WF_Ref_ID.

I can't even move to the next record after I make changes in the
subform.

Also, I understand that ADP might be another option but the business
rules don't allow for it.

Any assistance would be greatly appreciated.

Regards,

Paul


Try using an "INSTEAD OF TRIGGER"

e.g.
Create Trigger iTrig_MyView on MyView instead of insert
as
declare @MyID int
-- first off update the base table
insert into MyTable (fieldlist) select fieldlist from inserted
-- get the ID and do something with it
set @MyID = SCOPE_IDENTITY()
etc etc.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.