New records disappearing, modified records okay

[eksortso]

I have a table on a SQL Server back end, and my Access 2003 client connects to the table via ODBC. The table has an identity field (similar to AutoNumber) which serves as the table's primary key.

My problem is this: I have a form which is bound to this table. If I enter a new record and type in some data, then switch to another record, the original record is replaced with a record that already exists. For instance, let's say I create entry #11768. I switch to another record, and suddenly, entry #877 appears instead. Access is saving the new record properly, because I can close and reopen the form (or requery the data) and see the new record. What's more, it seems that (continuing the example) if I created entry #11769 (one higher), then entry #878 (one higher) appears instead!

This only happens when I insert a new record. If I modify an existing record, then switch, then I still see the record with its modifications.

This behavior occurs on every form and subform which is bound to this table. New records disappear and reappear, but edited records stay in place. This doesn't happen on forms bound to other linked tables.

I want to fix this problem without always requerying after adding records, and without resorting to VBA to programmatically add new records. How can I keep new records from disappearing?

 

[scking]

If you requery the main form as you are implying the form will move to the first record each time. If you do a Me.Requery at any time you will have this problem. I'm not sure that you do but it's worth a try.



---------------------
scking@arinc.com
---------------------

 

[missinglinq]

Exactly how are you "switching" then "switching back?"

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[eksortso]

scking: I am not requerying the form at all. I just don't want to have to resort to requerying.

I should have mentioned that all of the forms that are bound to this table are continuous forms. If I click on another record on the screen after I insert a new record (Missinglinq: that's what I meant by "switching"), Access doesn't display the new record, but rather, a completely different record in the same table.

It's strange, because after this happens, I can scroll up to see the old record that appeared unexpectedly, then scroll down to see the same record in the spot where the new record ought to be!

If I requeried, then the new record that I entered would actually appear. But I don't want to requery. Likewise, if I created a record on this table in the subform (which causes the abnormal behavior), moved to different main-form record, then moved back to the original main-form record, then all the subform records appear properly, including the record I just created.

I've worked around this problem: users run a back-end procedure to start a new record, then they modify that record. So this isn't a high-priority problem anymore. But I'd still like to know why it's acting the way it is.

I could share the SQL code that was run to generate the table, if it would help. Like I said, this only happens on this one peculiar linked table.

 

[eksortso]

I've isolated the problem. There are triggers on the table on SQL Server. When a new recorded is inserted using Access, the fluke occurs. When I remove the triggers, new records are displayed correctly.

There may be a way around this problem. For instance, what if I didn't use the identity field as the primary key? Given time, I'd look into it further. But I'm done for now. My workaround (mentioned before) is a better solution to my problem.