March 25, 2003 at 4:27 pm
Hello,
This message is an amendment to a message I sent earlier today:
I have a situation in which database users input data into a SQL Server 2000 database table via an Access 2000 Project form. Per the users request, I set up something called 'AutoTab' to make it easier for them to navigate through the form as they input records.
60 Of the fields can take one of six static values, one of which the user puts in as 'N'. This 'N' value represents 'Not Applicable'. The convention used for this kind of input might usually be 'NA', but I had to use 'N' to accommodate the AutoTab feature in the Access Project form.
The users want an 'NA' result to appear on the Project Reports when they enter 'N' on the form. What the form might show after they enter 'N' does not matter, so long as they can just enter an 'N' on the form and have AutoTab still work for them. It is okay for the table to store 'NA'.
I tried the following Trigger (using just one of the 60 fields), and it seems to initially work okay:
CREATE TRIGGER ChangeNtoNA
ON tblRCStudentGrades
FOR INSERT, UPDATE
AS
Update tblRCStudentGrades
Set ST1Q1 = 'NA'
Where ST1Q1 = 'n'
In the ADP form, when I insert 'n' into 'ST1Q1' and then click a button to preview a report, the onclick event of the button saves the record as NA, and NA shows up on the report that opens. The problem is, if I attempt to change what now appears as NA in the form at 'ST1Q1', after the change is made, I get the following message box:
"Write Conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then past our changes back in if you decide to make changes."
The msgBox gives me the options to 'Save Record' (the msgBox does not go away upon selecting this option), 'Copy to Clipboard' and 'Drop Changes'. What I would like to do is create a Trigger coded something like this instead:
ALTER TRIGGER ChangeNtoNA
ON tblRCStudentGrades
FOR INSERT, UPDATE
AS
Select
Case ST1Q1
When 'n' Then 'NA'
End
FROM tblRCStudentGrades
I am able to create this trigger, but it does not change an input of 'n' to 'NA'. Do you have any suggestions on how I could code this Trigger to change and input of 'n' to 'NA' without using an UPDATE statement?
Thanks.
CSDunn
March 26, 2003 at 11:57 am
I had to give up on triggers with an Access frontend for this reason. I thought at one time that if the developer created a form instead of using the table view in Access to enter data, that I got around this error. Apparently I am wrong about the form working.
My solution is to make the changes at night when I run some other scripts instead of making the change live. I'd be interested if anyone else has gotten this to work.
Michelle
March 26, 2003 at 1:28 pm
I was actually able to implement multiple CASE statements in the one View and SP that serve as the Record Sources for a couple of different reports. This saved me from having to set up a Trigger of any kind. MS Access Projects seem to require that fields involved in SQL Server CASE statments be aliased in the SQL code. The following is a sample of what I did:
"ELDst1Q1" = CASE
When Upper(AD.ELDst1Q1)='N' Then 'NA'
Else Upper(AD.ELDst1Q1)
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply