Workaround: ...because it doesn't satisfy the criteria in the underlying record source

[Malcolm Cook]

Symptoms:

ADP, ACC2002 SP3, SQLServer 2000

1) "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 "
2) You're sure that the data DOES in fact match the underlying record source
(for instance, the underlying recordsource is the whole table).
3) You've already made sure that you trigger performs a 'SET NOCOUNT ON' (or
you do it globally on the current connection), becuase you know already that
ACC2002 will be confused if you don't.
4) You come to the point that you realize that Access's dependency on
@@IDENTITY is being thwarted by, say, a trigger on your table which does
inserts on other tables
5) The workarounds offered in
http://support.microsoft.com/default.aspx?scid=kb;en-us;275090 prove
unsatisfactory
Why? Because the workarounds refresh the recordset and change the
current record.
So, for example, if Access writes the current record to the database as
a result of, say, tabbing into a subform, or the user selecting "Records >
Save Record" there is
NO WAY to navigate to the newly entered record, and even if you COULD,
you're going to rerun your Form_Current which may not be what your
application expects
(nor should it).

WHAT TO DO?

First, get MS Access developers to look into using
IDENT_CURRENT('table_name') instead of @@IDENTITY (assuming access can
figure out which table to ask for, which is not TOO hard, and could use a
hint if needed such as might be supplied in the forms unique table
property). (This assumes IDENT_CURRENT works, which I can't first hand
vouch for). How do I do this? Are you listening?

Second, In the mean time, hack/recode the triggers on your tables with
triggers to call a new stored procedure as the final thing they do which
will reset the @@IDENTITY to that appropriate to the table holding the
trigger.

For example, from a trigger of mine I had on a table name 'plate' , the last
line was:
execute sp_reset_IDENTITY_from_IDENT_CURRENT 'plate'

And, after my sig, is the definition of the stored procedure.

Questions? Complaints? Better workarounds?

Cheers,

--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


CREATE PROCEDURE [dbo].[sp_reset_IDENTITY_from_IDENT_CURRENT]
(@tablename as varchar(80))

/**

Pupose: reset global @@IDENTITY to be the appropriate for the database
table @tablename.

Usage: As the last thing done in a trigger, if you think that some client
application is expecting @@IDENTITY to reflect the last inserted row for
the triggers base table (hellooooo MS Access developers!!!!)
instead of, perhaps, using IDENT_INSERT.

Author: Malcolm Cook ([email protected])

Kudos: to Nik Sestrin ([email protected]) who described the
workaround in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=O1BcqJTQBHA.1788@tkmsftngp05

**/

AS

DECLARE @strSQL varchar(50)

set nocount on --not doing this can confuse Access ADPs as well

SET @strSQL=N'SELECT Identity (Int, ' + Cast(IDENT_CURRENT(@tablename) As
Varchar(10)) + ',
1) AS id INTO #Tmp'

EXECUTE (@strSQL)
GO

 

[Malcolm Cook]

I might add a quick improvement myself....

The lengths of the string to exectu and the platename should be bumped
up....

better is:

(@tablename as varchar(255))

DECLARE @strSQL varchar(300)

cheers,

malcolm

 

[Mingqing Cheng [MSFT]]

Hi Malcolm Cook,

Admittedly, the workaround in KB: 275090 may encounter problems as it's
records will be changed.

Thanks for sharing your workaround with us in the Newsgroup I think it is
a great idea to have such expand feature for individual item/row, I'd
recommend that you forward the recommendation to the Microsoft Wish Program:

Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.

Let us know how we can improve our products.

Product Enhancement suggestions can include:

Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.

All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.

World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:

http://www.microsoft.com/ms.htm

Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site

http://register.microsoft.com/mswish/suggestion.asp

and then complete and submit the form.

E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, (e-mail address removed).

FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.

NOTE : Address the FAX to the attention of the Microsoft Wish Program.

US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:

Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399

MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.

All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.


Thank you for your patience and cooperation again. If you have any
questions or concerns, don't hesitate to let me know. We are here to be of
assistance!


Sincerely yours,

Mingqing Cheng
Microsoft Developer Community Support