Microsoft SQL need help with an autonumber

Jon123

Registered User.
Local time
Today, 15:31
Joined
Aug 29, 2003
Messages
668
In access I had a table with a primary key set to auto update and uni records. When you would enter a new record this field would update with a new number. In SQL I have the same primary key the data type is set to var and I have the identity set to yes and increments to 1. When I start a new record it does not auto update this field with a new number?
Why?

jon
 
You have set the data type to var? surely you mean int?
 
sorry Yes you are correct
 
What about when you finish a record? I believe that SQL Server doesn't assign that number until the record is SAVED unlike Access which does it when you start a new record.
 
What about when you finish a record? I believe that SQL Server doesn't assign that number until the record is SAVED unlike Access which does it when you start a new record.

Ahhh. I'm way new to SQL Server, and am in the process of uploading my backend Access 2003 .MDB's to SQL Server, with help from some outside IT help. I have a couple of sample submission forms that were bugging out over this issue, and thanks to your post I now know why.

The tables behind these forms have an autonumber field, called recordID and a text field called labID (table's PK). In my data entry forms, a textbox called txtlabID in the forms copies the autonumber value of the form's txtrecordID as a text value. I was doing this in the form's Before Update event. In Access, that went just fine, but now trying with SQL Server tables, it is giving me issues because of a null value in txtlabID. It looks like I need to save the record so an autonumber value is available to copy; is there another event in the form where I need to do this?

Thank you.
 
We ended up making a seeding table for these 2 tables, and wrote a public module to grab the value and update it in the seed table. Seems to be a good workaround.
 

Users who are viewing this thread

Back
Top Bottom