#deleted problem when deleting records from sql server through vba

[paula]

I have a front-end access database that uses a sql server back-end to
link the tables. When I run the following code, the subform displays
#deleted in place of the deleted record. What am I doing wrong???

s = "DELETE WMS_FID FROM [tmp_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
domcd.RunSQL (s)

'*** PMP 9/22/07 - Copy the records to be deleted from t_PID
to t_PID_Deleted table

s = "INSERT INTO t_PID_Deleted ( WMS_FID, S_PER_IK, S_SEQ_NB,
MRN_PRE, MRN, PID, EU, LAST_NM, FIRST_NM, MIDDLE_NM, SSN, GENDER,
MBR_TYPE, " & _
" EFF_DT, TERM_DT, CUR_STS, STATUS, [FOUND], ADDED_UN,
ADDED_DT, EDITED_UN, EDITED_DT, PREPOP, DMC_TS, TS )" & _
" SELECT t_PID.WMS_FID, t_PID.S_PER_IK,
t_PID.S_SEQ_NB, t_PID.MRN_PRE, t_PID.MRN, t_PID.PID, t_PID.EU,
t_PID.LAST_NM, t_PID.FIRST_NM, " & _
" t_PID.MIDDLE_NM, t_PID.SSN, t_PID.GENDER,
t_PID.MBR_TYPE, t_PID.EFF_DT, t_PID.TERM_DT, t_PID.CUR_STS,
t_PID.STATUS, t_PID.FOUND, " & _
" t_PID.ADDED_UN, t_PID.ADDED_DT, " & Chr(34) &
Environ("UserName") & Chr(34) & " AS Expr1, Now() AS Expr2,
t_PID.PREPOP, t_PID.DMC_TS, t_PID.TS" & _
" FROM t_PID" & _
" WHERE (((t_PID.WMS_FID)=" & Me.txt_WMS_FID & "));"

DoCmd.RunSQL (s)

'*** PMP 09/20/07 - Added to delete the dependents and
t_pids
s = "DELETE WMS_FID FROM [t_DEPENDENTS] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

s = "DELETE WMS_FID FROM [t_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

Forms!f_pull_requests!lst_FS_Results.Form.Requery
Forms![f_pull_requests]![f_PID_Lookup_sub1].Requery

Thanks,

Paula

[J_Goddard via AccessMonster.com]

Hi -

Is the sub-form that is showing the #deleted one of the ones you are
requerying? If not, it should be - that will remove the #deleted.

John
pa***@chubbyjoes.com wrote:

>I have a front-end access database that uses a sql server back-end to
link the tables. When I run the following code, the subform displays
#deleted in place of the deleted record. What am I doing wrong???

s = "DELETE WMS_FID FROM [tmp_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
domcd.RunSQL (s)

'*** PMP 9/22/07 - Copy the records to be deleted from t_PID
to t_PID_Deleted table

s = "INSERT INTO t_PID_Deleted ( WMS_FID, S_PER_IK, S_SEQ_NB,
MRN_PRE, MRN, PID, EU, LAST_NM, FIRST_NM, MIDDLE_NM, SSN, GENDER,
MBR_TYPE, " & _
" EFF_DT, TERM_DT, CUR_STS, STATUS, [FOUND], ADDED_UN,
ADDED_DT, EDITED_UN, EDITED_DT, PREPOP, DMC_TS, TS )" & _
" SELECT t_PID.WMS_FID, t_PID.S_PER_IK,
t_PID.S_SEQ_NB, t_PID.MRN_PRE, t_PID.MRN, t_PID.PID, t_PID.EU,
t_PID.LAST_NM, t_PID.FIRST_NM, " & _
" t_PID.MIDDLE_NM, t_PID.SSN, t_PID.GENDER,
t_PID.MBR_TYPE, t_PID.EFF_DT, t_PID.TERM_DT, t_PID.CUR_STS,
t_PID.STATUS, t_PID.FOUND, " & _
" t_PID.ADDED_UN, t_PID.ADDED_DT, " & Chr(34) &
Environ("UserName") & Chr(34) & " AS Expr1, Now() AS Expr2,
t_PID.PREPOP, t_PID.DMC_TS, t_PID.TS" & _
" FROM t_PID" & _
" WHERE (((t_PID.WMS_FID)=" & Me.txt_WMS_FID & "));"

DoCmd.RunSQL (s)

'*** PMP 09/20/07 - Added to delete the dependents and
t_pids
s = "DELETE WMS_FID FROM [t_DEPENDENTS] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

s = "DELETE WMS_FID FROM [t_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

Forms!f_pull_requests!lst_FS_Results.Form.Requery
Forms![f_pull_requests]![f_PID_Lookup_sub1].Requery

Thanks,

Paula

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200710/1

[paula]

On Oct 5, 5:48 pm, "J_Goddard via AccessMonster.com" <u37558@uwe>
wrote:

Hi -

Is the sub-form that is showing the #deleted one of the ones you are
requerying? If not, it should be - that will remove the #deleted.

John

[e-mail] wrote:

I have a front-end access database that uses a sql server back-end to
link the tables. When I run the following code, the subform displays
#deleted in place of the deleted record. What am I doing wrong???

s = "DELETE WMS_FID FROM [tmp_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
domcd.RunSQL (s)

'*** PMP 9/22/07 - Copy the records to be deleted from t_PID
to t_PID_Deleted table

s = "INSERT INTO t_PID_Deleted ( WMS_FID, S_PER_IK, S_SEQ_NB,
MRN_PRE, MRN, PID, EU, LAST_NM, FIRST_NM, MIDDLE_NM, SSN, GENDER,
MBR_TYPE, " & _
" EFF_DT, TERM_DT, CUR_STS, STATUS, [FOUND], ADDED_UN,
ADDED_DT, EDITED_UN, EDITED_DT, PREPOP, DMC_TS, TS )" & _
" SELECT t_PID.WMS_FID, t_PID.S_PER_IK,
t_PID.S_SEQ_NB, t_PID.MRN_PRE, t_PID.MRN, t_PID.PID, t_PID.EU,
t_PID.LAST_NM, t_PID.FIRST_NM, " & _
" t_PID.MIDDLE_NM, t_PID.SSN, t_PID.GENDER,
t_PID.MBR_TYPE, t_PID.EFF_DT, t_PID.TERM_DT, t_PID.CUR_STS,
t_PID.STATUS, t_PID.FOUND, " & _
" t_PID.ADDED_UN, t_PID.ADDED_DT, " & Chr(34) &
Environ("UserName") & Chr(34) & " AS Expr1, Now() AS Expr2,
t_PID.PREPOP, t_PID.DMC_TS, t_PID.TS" & _
" FROM t_PID" & _
" WHERE (((t_PID.WMS_FID)=" & Me.txt_WMS_FID & "));"

DoCmd.RunSQL (s)

'*** PMP 09/20/07 - Added to delete the dependents and
t_pids
s = "DELETE WMS_FID FROM [t_DEPENDENTS] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

s = "DELETE WMS_FID FROM [t_PID] WHERE [WMS_FID]=" &
Me.txt_WMS_FID
DoCmd.RunSQL (s)

Forms!f_pull_requests!lst_FS_Results.Form.Requery
Forms![f_pull_requests]![f_PID_Lookup_sub1].Requery

Thanks,

Paula

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

Yes, the subform that shows the #deleted is the subform I am
requerying.