March 19, 2009 at 3:55 am
Good Morning,
Can anyone please help me with the following issue ?
I'm using Access 2000 database (office 2003) and SQL Server 2005.
I've a form on my access database to insert data on an ODBC table (SQL Server driver)
into a table called 'CASES'
On this table, I've a field called reference which I update using an Insert trigger on the CASES table.
By the same way, I'm using the trigger to send an email with details about the inserted record.
The insert is happening fine and the email is being sent properly as well.
The only problem I'm having is on my Access form where all the fields are showing #deleted.
I tried to change this code by inserting the mail information into another table instead of sending a
mail directly and the result is as well #deleted on my form.
If I keep only the code to update the CASES table, then the form is showing correct values.
Any ideas ?
ALTER TRIGGER [dbo].[case_upd]
ON [dbo].[CASES]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN
UPDATE CASES
SET REFERENCE = INSERTED.REFERENCE + right('000000'+convert(varchar(6),INSERTED.ID),6)
FROM CASES INNER JOIN INSERTED ON CASES.ID = INSERTED.ID;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'LUXLBSQLT01',
@recipients=N'ffff.llll@myprov.com',
@body = N'BODY TEXT',
@subject=N'SUBJECT Database email';
END
END
Thanks , Gilles
March 24, 2009 at 12:45 am
Any idea ? :crying:
March 24, 2009 at 1:21 am
It seems like there no issue on the SQL Server Side.
I haven't used ACCES in a while, but did you try requery your data and refresh the form after that?
March 24, 2009 at 1:48 am
Hi Lutz,
Thanks for your reply.
I tried to requery, refresh whatever was possible and it's not working.
The problems seems to occur when the data is saved from access, I mean
when I click on my save button the following command is called
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
What I didn't mention, is that the form is setup with "DataEntry" to YES...
any impact ?
Really it's strange as it looks like that any additional code after or before the code to update the reference is causing the issue within the SQL trigger ... :doze:
March 25, 2009 at 1:02 pm
Your problem is that you have a form bound to the underlying recordset that gets changed outside of the form - i.e. the trigger. Access has read the underlying record and knows what has been updated in the form. When it detects the fact that the underlying record has been changed outside the form (from the trigger) it gives the #deleted# errors on the form.
If you don't really need the column that the trigger updates on your form you can leave it off of the form (and the underlying recordset if the form is bound to a query rather than the table). The #deleted# problem will go away.
If you have to have the updated column displayed on the form you could try doing a Me.Requery in the form AfterUpdate event. This will pull in the data from SQL Server again and there should be no conflict between old data and new data in the form.
Another way to handle this is to make the column that the trigger updates an unbound field on the form. I assume that since the trigger does the update, the column itself should not be updated in the form. In the FormCurrent event, you put in some code to read that column from the database so you can display it. After any update on the form, you run the same code to display the new value.
Todd Fifield
March 26, 2009 at 5:38 am
Hi Todd,
Thanks for your reply, but unfortunatelly your solutions are not working
The refresh action is clearing the #deleted#, but it moves to a new record as
the form is in DataEntry mode.
Removing the updated reference field does not fix the issue :ermm:
I'll try to put the code to send the email into another trigger to see ...
March 26, 2009 at 9:16 am
I'm turning around any valuable solution... 🙁
To finish, I've changed my reference field as a calculated field.
The trigger now is used only to send the email.
In this case, its working :hehe:
March 26, 2009 at 10:34 am
Gilles,
I forgot to mention that the requery would then put you at the 1st record in the recordset. A solution to that would be to save the primary key before the requery and then come back that record after the requery.
The #deleted# problem shouldn't show up if the column being updated by the trigger is not part of the form's recordset - I've tested that and it was worked for me. If your form is bound to the underlying table itself, then you'd have to change it to a query that left out that particular column. Then have that column on the form as unbound. You'd read it when the form was current and after the form was updated.
Todd Fifield
March 26, 2009 at 11:06 am
Hi Todd,
I'm a bit confused on how to save and select the primary key again.
I'll look at this tomorrow ...
Thanks again ! 🙂
March 26, 2009 at 6:53 pm
Gilles,
Let's say you have a button on the form that saves the current record called btnSave. Let's say that the primary key for the table is an INT called RecID and that it is an integer. The code for the click event on the button would look something like:
Private Sub btnSave_Click()
Dim SaveRec as Long
Dim RC as RecordSet
SaveRec = RecID ' The primary key for the table.
Me.Refresh ' Save the current record and cause the trigger
' to fire.
Me.Requery ' Requery the recordset
Set RC = Me.RecordsetClone 'Get a clone of the form's recordset
RC.FindFirst "RecID = " & Cstr(SaveRec) 'Navigate to the record that
' was saved
Me.BookMark = RC.BookMark ' Set the form's current record to the clone.
Set RC = Nothing
End Sub
March 27, 2009 at 1:54 am
Hi,
I tried this as well,
but somewhere something is wrong.
On my form, the record ID is generated on save, then can not
be recorded. before saving :doze:
March 27, 2009 at 12:41 pm
Gilles,
If the back end database is Access then you get the new autonumber (RecID) as soon as you start to insert a new record. With SQL Server as the back end, you don't get it until the new record is saved.
Try retrieving the RecID after you do a Me.Refresh. You should be able to get it.
Todd Fifield
March 30, 2009 at 7:58 am
Hi Todd,
I agree with what you're saying, that's the reason why I wanted to create the reference field in a trigger
I'm getting the record ID , unfortunately the RC recordset is alway empty :blink:
I'll have to go ahead with my temporary solution .
I'll come back to this a bit later.
Thanks again for your help.
Gilles
March 30, 2009 at 2:54 pm
Gilles,
Let me know how it goes.
Todd Fifield
March 30, 2009 at 11:44 pm
Yes , no problem.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply