Access Autonumber - SQL Primary Key

[jcruzAME]

I had another thread that as more specific but I figured I'd ask another question that is broader to help my understanding of how these work when creating a new record.

Let me give some background. We have a form that pulls from a table called AssetLiability. It contains assets for all the clients of this company. When you choose a client or an asset of a Client from another form in the program, it pulls up a form called AssetDetails in which it shows the asset you clicked on, but also pulls all the other records for that client as well.

Now the trouble I'm having is when I try to create a new record (asset) for a client. The last program was a front end and back end in Access. The new one is SQL back end Access front end. In the old program it seems to create a new AssetLiabilityID for a new record when I click new record. In the new one with an SQL back end it doesn't seem to do the same.

Does this have anything to do with how SQL and Access deal with creating a new record/table entry? In Access it seems to maintain all the information for the Client when you create a new record whereas it doesn't seem to in the new program.

 

[Mr Smin]

Hi,

Is your form bound to a query which is non-updateable? Some querys return result sets which cannot be updated as any update would be ambiguous. If you have used 'group by' in the query for instance.

Also
Are there any error messages when you click 'new record'?
Does it present you with a new blank form?
Did you use the Access tool (I think it's called upsizer) for migrating to SQL Server or migrate manually?

 

[jcruzAME]

No error messages, it just never sets a new ID. I'm going to try something here shortly and I'll report back with my findings.

 

[rodmc]

SQL assigns the autonumber last and not first as access does

 

[harmankardon]

I think you have to commit the record before an autonumber is assigned. In SQL Server it's called Identity btw.

 

[boblarson]

I think you have to commit the record before an autonumber is assigned.

That is correct.