0

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?

AngelicCore
  • 1,413
  • 3
  • 22
  • 40

2 Answers2

0

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.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Sadly the user considers this unacceptable. He wants it to appear correctly from the first time If I tell access that that field is readonly to prevent it from trying to enter a value there it shows #Deleted on all of the fields And..both solutions work on the backend, but both don't on the front end – AngelicCore Aug 15 '13 at 19:45
  • 1
    Sounds like you have it open in datasheet view. Can you change it to a continuous form and add a "Copy" button that will select the current record, paste it to the table and then refresh the form? There's no way to actually do what you're asking, so you need to find an acceptable workaround and that's one of them. – Johnny Bones Aug 15 '13 at 19:57
0

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.

AngelicCore
  • 1,413
  • 3
  • 22
  • 40