Autonumber not working after upsizing

[Holly]

I upsized to a SQL server with an Access FE. I have a form that creates a
new record if the user clicks on a command button from a previous screen or
it displays the record selected from a list. The selected record part works
fine, but when creating a new record, the autonumber field just shows up
blank. What do I have to do to get this to work?

 

[Allen Browne]

Holly, I think you will find that SQL Server's timing is different.

Access assigns the autonumber as soon as you being typing into the new
record. SQL Server assigns the number at the last possible moment before the
record is saved.

 

[Holly]

Got it, thanks. Any words of wisdom for deleting records from a form using a
command button? This code:

DoCmd.RunCommand acCmdDeleteRecord

only works for the first deleted record, then on the second try the computer
hangs for a minute and then I get a Run time error 2501: The RunCommand
action was cancelled.

 

[Allen Browne]

It should work, unless there is no current record:

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If

 

[Holly]

The record is current, otherwise I think it wouldn't work at all. But, it
only works once. Then if you close the form, and re-open it with a new
record, it hangs for a minute, then errors out. However, if I click debug, I
can step throught the code manually, and it deletes the record fine. Also, I
noticed if I add a new record, after I delete a record, the delete process
works fine. Any ideas?

 

[Holly]

I tried using the code suggested, nothing changed. Sometimes it deletes,
sometimes it hangs then errors out (I can always step through code to
complete the delete)

 

[Allen Browne]

What version of Access is this?

(I am aware of a flaw in Access 2002 Service Pack 3 that relates to this
error message.)

 

[Holly]

What version of Access is this?
Access 2003 SP1.

I also tried to delete records staraight from the table and that works fine.
I noticed it's more random thant I thought. Sometimes I can delete, sometimes
I can't. But I can always step through the code and do it manually, after the
error. It's very inconsistent.

 

[Allen Browne]

The inconsistency implies that something else is interfering with the
process.

It could be anything: another event (e.g. the Timer event in some form), or
an interaction with some other process (even one outside of Access), or a
particular set of circumstances that could be masked on something like an On
Error Resume Next, or ...

 

[Holly]

another event (e.g. the Timer event in some form)
I don't have the timer event in any form

an interaction with some other process (even one outside of Access)

like what?

particular set of circumstances that could be masked on something like an On
Error Resume Next, or ...

I have commented out the On Error code already

It's so weird. I opened up the database and was able to delete 8 records
without a problem. Then I added some new records. As soon as I tried to
delete one of the new records, I had the same problem. This is driving me
crazy! Does it matter that my Sql server is running from my laptop? (I am in
testing mode at this point)

 

[Allen Browne]

I'm out of suggestions, Holly.

The fact that you are using SQL Server rather than JET could make a
difference perhaps.

 

[Holly]

Thanks for your help anyway.

 

[Holly]

I just wanted to let you know that I re-upsized the mdb to SQL server located
on a separate server, instead of my local. The delete problem is now gone!