Autonumber not committing with SQL backend

[hanusborb]

Thank you for taking a look.

I originally started with Access2003 frontend and backend. Things worked fine. When my form openned, "(Autonumber)" would be in the ID field. I would type a letter/name in the name field and the ID field would populate with an autonumber. I could click my "NEW" button to carry my ID number and open the next form. The row would insert into the table.

I upsized my data backend to SQL.

Now when my form opens, there is "(Autonumber)" in my ID field, but when i type a letter/name in the name field the ID field goes blank. When I click my "NEW" button, to open the next form and carry the ID number, I get the error "Invalid Use of Null". It appears that SQL is holding an autonumber for me but won't put it in my form until i update the table with the information from the form. For example, if i switch from form view to design view the number appears. Then i can move forward.

The problem is that i need the ID number to continue forward. Can i get this autonumber when the form opens?
has anyone run into this before?
Thanks.

 

[pbaldy]

Similar problem to this one:

http://www.access-programmers.co.uk/forums/showthread.php?t=184269

Though usually I wouldn't use more than one form for the data entry.

 

[Max D]

In MS SQL autonumber is assigned only when the data is inserted it the database. You'd better ignore the autonumber in this case and use some other way to start creating new record, like DoCmd.GoToRecord ,,acNewRec

 

[RuralGuy]

What code is behind your "New" button?