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
5 1167
Just a guess but you might try to make sure the record is saved before you run the archive process.
DoCmd.DoMenuItem acForm, acEdit, acSelectRecord
DoCmd.DoMenuItem acForm, acEdit, acSaveRecord
Hope that helps.
Hi Denburt,
I tried with both lines in and I got an error message saying "The command or action 'InsertQueryColumn' isn't available now" on the acSelectRecord line, and if I take that line out then it still does the same as before.
Andrew
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
Sorry I didn't reply sooner. Yes I do have it like that but it only inserts the old data, not the new data.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jim Hefferon |
last post by:
Hello,
I'm getting an error join-ing strings and wonder if someone can
explain why the function is behaving this way? If I .join in a string
that contains a high character then I get an ascii...
|
by: Michael |
last post by:
After trying to load my java applet from <OBJECT> tag
(rather than <APPLET> tag), it renders fine, except when
you click between the DESIGN view then back to HTML view,
the <PARAM NAME="ARCHIVE"...
|
by: gregory_may |
last post by:
First the research links:
IPv6 spec (look for 'jumbo payload'):
http://www.cs-ipv6.lancs.ac.uk/ipv6/documents/rfcs/archive/rfc1883.txt
IPv6 Sample C# Client/Server...
|
by: google.100.frause |
last post by:
Previously I had the same Problem discussed and solved here:
http://clariusconsulting.net/blogs/hdl/archive/2005/12/02/410.aspx?CommentPosted=true#commentmessage
but now I've got the next...
|
by: dwainew |
last post by:
Problem: I'd like to archive off the contents of a text file. However,
other processes may have the file open, so MoveTo(newfilepath) can't be
done, as far as I've found.
Start with a 5 MB...
|
by: Mark Shroyer |
last post by:
I guess this sort of falls under the "shameless plug" category, but
here it is: Recently I used a custom metaclass in a Python program
I've been working on, and I ended up doing a sort of write-up...
|
by: andrew.smith.cpp |
last post by:
Hello :-)
how can i play a ".wav" file in C++.means with the help of the C++
code i just want to play a "a.wav" file more then 10 times. i just
want to play background music.
Can i do it in C++ ?...
|
by: akansha1234 |
last post by:
Hi all,
I am facing this problem. When i am building my workspace in eclipse i am getting following exception its very urgent..
0000004c SystemErr R
*** ERROR ***: Fri Sep 12 03:53:38 CDT...
|
by: =?Utf-8?B?UGF1bCBQcmV3ZXR0?= |
last post by:
I'm attempting to use LINQ to insert a record into a child table and I'm
receiving a "Specified cast is not valid" error that has something to do w/
the keys involved. The stack trace is:
...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |