"Archive" process not working properly

[rdsandy]

Hi,

Im using Access 2003 and SQL Server 2005. I made a stored procedure to do an archiving process, which if theres a change in a certain field, it inserts the certain fields in that row to an "archive" table. The stroed procedure is below:

alter PROCEDURE [dbo].[usp_RiskChangeArchive]
@RiskID int

AS
BEGIN

SET NOCOUNT ON;

insert into RiskChangeArchive (RiskID, Risk, ProjectID, PlatformID, ClientID, CategoryID, WBS, Probability, Consequence, RiskRating, Manageability, Criticality, ArchiveMonth)
select risk.riskid, risk.risk, risk.projectid, risk.platformid, risk.clientid, risk.categoryid, risk.wbs, risk.probability, risk.consequence, risk.riskrating, risk.manageability, risk.criticality, getdate() as archivemonth
from risk
where riskid = @RiskID

There is a form, with a subform on it. The subform holds all the fields, and the main form has a text box (called txtRiskID) which holds the value of the ID number for the row that I am on in the subform. An example of where I use the stored procedure is:

Private Sub Consequence_AfterUpdate() 
    RiskChangeArchive Me.Parent.txtRiskID
End Sub

The problem I have is that it archives the row with the old value, not the new value. So say the value in Consequence is 3 to begin with, and I change it to 2, it archives the row with Consequence having a value of 3, not 2.

Where am I going wrong?

Many thanks,

Andrew

[Denburt]

Just a guess but you might try to make sure the record is saved before you run the archive process.

DoCmd.DoMenuIte m acForm, acEdit, acSelectRecord
DoCmd.DoMenuIte m acForm, acEdit, acSaveRecord

Hope that helps.

[rdsandy]

Hi Denburt,

I tried with both lines in and I got an error message saying "The command or action 'InsertQueryCol umn' isn't available now" on the acSelectRecord line, and if I take that line out then it still does the same as before.

Andrew

[Denburt]

I am lost or maybe not, perhaps I was not clear, did you add the lines like so?

Private Sub Consequence_AfterUpdate()
DoCmd.DoMenuItem acForm, acEdit, acSelectRecord
DoCmd.DoMenuItem acForm, acEdit, acSaveRecord

          RiskChangeArchive Me.Parent.txtRiskID
      End Sub

[rdsandy]

Sorry I didn't reply sooner. Yes I do have it like that but it only inserts the old data, not the new data.

[Denburt]

So many things could be at issue. First make sure you add some error trapping in that MS Access procedure that alone could shed more light on the situation.

Now make sure that recordset you are trying to update is updateable. You can do this by changing the record and making sure it is updated in the table. On most forms/subforms there is a record selector on the left, once you start editing that record before it is saved it should show a pencil. Then I would go to the menu bar and choose Edit/select record and see what response you get. The pencil should change to an arrow. Post back and let us know the results. Thanks.