?
Solved

Access 2003 ADP - SQL trigger problem

Posted on 2006-05-30
8
Medium Priority
?
531 Views
Last Modified: 2012-06-27
I have a SQL table 'A' with an INSERT trigger that inserts rows into another table 'B'. The trigger works fine when I use Enterprise Manager to insert a row into table 'A'. But if I try to edit table 'A' from the ADP and insert a row I get an error, though the trigger does actually do the job. What's going on?

The error is "The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source."

'A' has a one-to-many relationship to 'B'.

The trigger:
CREATE TRIGGER trgContact_Insert ON [dbo].[tblContact]
FOR INSERT
NOT FOR REPLICATION
AS
--Add default records to tblMailout when new Contact is created
--if records don't already exist and the PromMaterial bit is on

SET NOCOUNT ON

DECLARE @HasMail      int
DECLARE @ContactID      int
DECLARE @PromoMaterial      bit

BEGIN TRANSACTION

SELECT @ContactID=ContactID,@PromoMaterial=PromoMaterial FROM Inserted

--get a count of records from tblMailout for this client
SELECT @HasMail=count(*) FROM dbo.tblMailout
WHERE ContactID=@ContactID

--insert the tblMailout records if required
IF @PromoMaterial=1 and @HasMail=0

      INSERT INTO dbo.tblMailout
      (ContactID,MailoutTypeID,Hardcopy,Email,Link)
      SELECT @ContactID,MailoutTypeID,1,1,1 FROM dbo.tbl_LU_MailOutType


COMMIT TRANSACTION
0
Comment
Question by:ravl
  • 4
  • 4
8 Comments
 
LVL 7

Accepted Solution

by:
Jonathan Kelly earned 500 total points
ID: 16789689
Hi  ravl,

im not sure, but it sounds like the answere is in the error message.

Doed the inserted record satisfy the record sources criteria for the form?
0
 
LVL 1

Author Comment

by:ravl
ID: 16793727
I get the same problem if I open the table directly, from the adp, without using a form.
0
 
LVL 1

Author Comment

by:ravl
ID: 16794020
You are right Datrias. The answer is in the message, of course. Table 'A' has an identity key. When I remove it the error goes away. I just don't understand why the process should be different when updating from the ADP, as it works from Enterprise Manager. Thanks anyway.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 16840524
ravl, apologies for long delay in reply. I have been away from the office the last couple of days.

do you need the identity field? if so, as a test I would put back in the indentity field and remove the trigger and then test it.


0
 
LVL 1

Author Comment

by:ravl
ID: 16840792
I fixed it by leaving the identity key but trapping the error in the form and ignoring it!
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 16840934
this article on sqlservercentral has some info on what you seem to be experiencing.

http://www.sqlservercentral.com/columnists/bkelley/triggerswhatsnew.asp
0
 
LVL 1

Author Comment

by:ravl
ID: 16841036
Thanks - that is an interesting article and something I didn't know about. But it doesn't really deal with my problem. The trigger does its thing OK. Access just seems to have a problem with the identity key. All I can do is trap the error and bypass it.
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 16841068
I just thought that Access may be getting confused by looking for the wrong Identity value.

never mind. if it works >> it works!!!

thanks for the points.




0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question