Copy/Paste records in Tables that have autonumber => same ID until refresh

Question

I have a silly nuisance on an error.

I have access front end and sql back end. In a form with a Record Source: Select * from ViewX(View of 2 tables each with autoID)

The problem is..I copy a row and paste it underneath..it works but the AUTOID column doesn't update...I have to manually press F5 to reload the whole thing and THEN it updates..

ViewX(Removed some details):

SELECT TOP (100) PERCENT dbo.Trial.TrialID, dbo.Culture.CultureID, dbo.Culture.Crop

FROM dbo.Trial LEFT OUTER JOIN dbo.Culture ON dbo.Trial.CultureID = dbo.Culture.cultureID

TrialID and CultureID are identity columns in their own tables.

Any ideas?

Answer 1

You're trying to paste a number into an autonumber field. First of all, can you do this in code? Secondly, do you really NEED to see that autonumber immediately? The table needs to refresh, so whether you press F5 or whether you close the table/query, either one will update the autonumber. The next time you open it, you'll see the new number.

Answer 2

I was able to fix the problem by forgoing the View and just have access select from the 2 tables directly..which the View was doing.

My guess is that access wasn't able to get the new ID because the insertion was not direct. It required a trigger because it affected 2 tables and that INSTEAD OF INSERT trigger screwed up with access..just a guess but I am happy it worked.