ADP Form Problem

[Steve101]

I have a form in datasheet view with an associated recordsource which looks something like this:
[tt]
SELECT COP.CompanyOrderId, COP.ProductId,
COP.Quantity,
COP.UnitPrice, COP.BillTo, COP.SendTo
FROM tblCompanyOrder CO
INNER JOIN tblCompanyOrderProduct COP
ON CO.CompanyOrderId = COP.CompanyOrderId
INNER JOIN tblProduct P
ON COP.ProductId = P.ProductId
WHERE CO.CompanyId = 262
AND P.ServiceId = 1
AND CO.OrderDate IS NULL
[/tt]
This all works OK, and populates the form as required. However, when I attempt to add a new record, I get the following message:

"The data was added to the database but the data wont be displayed in the form because it does not satisfy the criteria in the underlying record source"

The new record is then removed from display,l although the new record is generated into the COP record (which I have designated in the form as the 'unique table').

My concern here is that I am unable to use a multi table recordsource without this problem. I'm currently re-writing the recordsource to use a single table query with correlated subqueries, but I shouldnt have to do this.

Anyone come across this problem before? Perhaps I'm missing something.

Any help would be greatly appreciated,

Cheers,








Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[yaya013]

Try to set the UniqueTable
Me.UniqueTable = "tblCompanyOrder"

 

[pharcyder]

Hi Steve,

In my ADP-Project this happened when one table of my recordsource had a field with a Standard-Value set on the SQL-Server.

If you select ALL fields of ALL tables in the query it works, the problem is that you can't use the asterisk because you would get double field names...

Or try getting the fields with the Standard-Value (perhaps even fields from a replication?) in your query.

Greetings from Germany,
Jens

 

[Steve101]

Thanks for your input guys.

Yaya13: Tried setting the UniqueTable to tblCompanyOrder, but this predictably prevented data entry of any of the tblCompanyOrderProduct fields.

Jens: Greetings from Australia. Unfortunately, I don't want to incorporate all fields from all tables; I'm looking for an answer which requires just the essential fields.

I think I'm onto the solution, but need to research it a bit further. It involves usage of the resynch property, which seems to bring back the updated record into the datasheet. Further work required to figure this out though, so any further input by someone who's done this before would be appreciated,

Cheers,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

Situation update. I've got it to work by simply adding an AfterUpdate event to the form; with nothing in it! Sounds strange, but somehow, it seems to fix it. Most curious.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[leighflo1]

I am having the exact same problem as Steve101 have tried all the above and ResyncCommand and am still getting error message
'The data was added to the database but the data won't be displayed in the form because it does not satisfy the criteria in the underlying recordsource'
As with Steve101 the new record is then removed from display although record is added.

Have looked up the error message on msdn and it says it is caused when using SQL-Server 97. But is fixed with 2000.

But I am using 2000!

Any further help would be appreciated.

 

[Steve101]

leighflo1,

I seem to have fixed this problem by adding the following code to the resync property:

SELECT *
FROM tblYourTable
WHERE YourPrimaryKeyField = @@Identity

In my case, my primarykey on the table being updated is an Identify field. Read up a little more about the @@Identity SQL system variable; it essentially returns the identity of the last record added to the database.

I'm not sure if this method is bullet proof in a multi user environment, so it might need some embelishment; meanwhile its got me out of trouble.

Hope this helps; keep me posted if you have any enhancements to add,

Regards,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[leighflo1]

Thanks Steve101

I did what you suggested but no improvement. I am working with a form (based on a view) containing four subforms all based on views. Each view has one or more joins in it. But only one table needs to be updated.
I tried everything suggested above but it just didn't make any difference, the error message still occured and the records disappeared.

So I rebuilt the tables in sql-server and the error message went once I had removed the Triggers I had written to create an audit trail on all the updates to the UniqueTables of each view.

I also got the same error message when one of my view had a inner instead of outer join where the FK was not a required field.

Anyway changed all this and have not had the error message again. But have not yet tried yet rewritting the triggers another way.

However, am stll having problem of dissappearing UniqueTable and ResyncCommand properties on the forms, even when I save in design view.

The support for this error message we are getting on msn is useless. I think it is probably an error message that can appear for a number of different reasons, but the reason they give on the website isn't the end of it.

Let me know if you have any more insights

 

[cmmrfrds]

Are you SURE that there is a unique index (is the identity field a primary key) on the tables you are trying to update? Another thing you can try is adding a timestamp data type field to your table and include on your select statement, this way you are absolutely sure that the table will have an unique index or field. ADO, on which the Access project is based, will use all the fields that are returned to establish uniqueness if there is not a unique field on the table. If anything got changed in any of these fields along the way before you issue an update, ADO will give the error your described.

I suspect that if you hunt around your app, you will find that ADO cannot identify the record you are trying to update/return because of the lack of a unique identity. On an insert what may be happening is that the identity field is not being returned to the Form when you issue the insert and thereby be out of sync with the record on the Form. Normally ADO will return the identity field if it is an unique index.

 

[Steve101]

Leighflo1,
Concur with what Fred says above. Like I say, I seem to have got on top of this problem by having the unique PK, and doing a 'callback' selection on it on the ResyncCommand property.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ChakaTheApeMan]

I ran into this problem today. It seems that Microsoft has a Knowlde Base artical on the subject. It seems to be telling me that all my problems would go away if I upgraded my MS Sql 7.0 --> 2000. Other wise use a view for a resolution. That would be an icky resloution, can't update views.

http://support.microsoft.com/default.aspx?kbid=291091

 

[bbrendan]

Hi

Updating to mssql2000 wont fix the bug. I run an Order entry app which hits this same "DAM" issue.

Im using MSsql2000 with Access XP.

One thing I did notice is that I get this error when I add a new record. But the server Filter has an older record.

Like [orderID]= 1000

I have tried resetting the server filter. Which seems to fix it

My 2 cents

 

[ChakaTheApeMan]

This server filter issue seems to be more specific then previous resolutions.

I was thinking that the cause of this issue was linking to a primary key that is an identity. Maybe other users have more insight on that?

I would like to try resetting the sever filter first. Bbrendan if you could post the event and code, I would appreciate it.

I might also try linking my sub table to a PK field that isn't an identity. Just to see if that is the cause.

 

[ChakaTheApeMan]

Whoops, I got ride of this error by making the child tables link the primary key. I had the PK set to an identity, allowing me to change names without orphaning records. Whatever, Access didn’t like this. Not sure doing this makes good db normalization sense either. What can I say, Dose this make me a bad person?

I couldn’t make things work with server filters or resync settings or empty events. Seems to me that this is the error you get when the primary key of the parent table isn’t doesn’t match the Primary key for the child table. That seems to fix my issue. Maybe if I knew more about ADO I could prove it. In the end I think I had a db design problem. Should I join a support group?

If you think about it this is an error that says, I added you record. I don’t know how to display it right.