Forms and SQL Autonumber Fields

We had a split Access database, and recently moved the back-end from Access to SQL (by "upsizing it").  The front-end remains in Access.  There is a slight performance difference that is affecting some people, that I am trying to figure out the best way to address.

We have some entry Forms that are linked directly to the underlying tables.  One of the fields is an Autonumber field.  When the back-end was in Access, the Autonumber field would be populated as soon as they begin to enter data in any field on the Form.  Now that the back-end is in Access, it appears that this Autonumber field does not get updated until they move off the record (I am guessing that this is the point at which the record is actually written to the table).

This slight change is causing an inconvenience for our users.  They sometimes need to note the Autonumber ID field.  Before they could see it as soon as they begin entering the information.  Now, they are hitting the a record selector (forward/previous/new record), and then going back to see it.

What is the best way of allowing them to see the Autonumber ID without having to first move off the record?
I thought about adding VBA code to "write/update" the record to the table (not exactly sure the exact syntax to do that).   Then I could maybe add a button that they can click to run that VBA code.  Or maybe put it in the AfterUpdate event of some key fields.  Of course, one concern might arise is what if they make an error and want to remove that record completely.  So I would probably need to address that.

Thoughts?

Thanks
JoeMiskeyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
In the current event of the form, you can have something like:

If me.NewRecord Then
    Me.Text1.Text=' '
   'then save it via vb code
   Me.Text1.Text=''
End if
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<it appears that this Autonumber field does not get updated until they move off the record >>

 That is correct.  SQL doesn't create an identity value until you move off the record.  the other way besides forcing an update to occur is to message the user in the after update event   "Record xxxx created".

Jim.
0
JoeMiskeyAuthor Commented:
If me.NewRecord Then
    Me.Text1.Text=' '
   'then save it via vb code
   Me.Text1.Text=''
End if

Open in new window

No luck there.  Even after I corrected the syntax, it still didn't appear to do anything.

Private Sub Form_Current()

    If Me.NewRecord Then
        Me.Comments.SetFocus
        Me.Comments.Text = " "
   'then save it via vb code
        Me.Comments.Text = ""
    End If

End Sub

Open in new window


the other way besides forcing an update to occur
That is what I am asking, how do I force an update to occur without moving off the record?

The AfterUpdate event idea is a workaround we can probably live with, but I want to know if there are other better ways.

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
'then save it via vb code

needs to be replace with your code to save
0
JoeMiskeyAuthor Commented:
needs to be replace with your code to save
That is precisely what I am looking for.  I am not sure what that code needs to look like.
I thought about adding VBA code to "write/update" the record to the table (not exactly sure the exact syntax to do that).

Just clarify, this is a bound form.
0
Nick67Commented:
Of course, one concern might arise is what if they make an error and want to remove that record completely.  So I would probably need to address that.


You have yourself a pretty pickle.
Generally, the autonumber shouldn't be something that the users attach value to.
I do display it on all my forms, but I don't give it a label, and I don't put it in a prominent location.
Since you don't have a guarantee that numbers won't be skipped, deleted etc, it doesn't stay sequential, and that can drive users bonkers, anyway.

But now you have users accustomed to the use of the autonumber, and it doesn't behave the same in SQL Server.

Now, they are hitting the a record selector (forward/previous/new record), and then going back to see it.
 What is the best way of allowing them to see the Autonumber ID without having to first move off the record?
 I thought about adding VBA code to "write/update" the record to the table (not exactly sure the exact syntax to do that).   Then I could maybe add a button that they can click to run that VBA code.


A command button with a caption of Save Record is simple
The code can be a simple as
DoCmd.RunCommand acCmdSaveRecord

I would address this, though:
They sometimes need to note the Autonumber ID field.
Why?
What UI elements haven't you given them that they are 'noting' an autonumber ID?
And then how do they use what they have 'noted'?
CTRL-F in a textbox or field?
Should they have some UI element that opens other forms/reports using that number?
Until that record gets written, they can't use the number, anyway.
And you should generate unique, human-meaningful identifiers that are independent of the autonumber for the users -- in most folks' opinion, anyway.

Those are my thoughts, at any rate.
Nick67
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<in most folks' opinion, anyway.>>

Not in mine.  I know this is an age old debate, but if you treat an autonumber as a surrogate key, then there is no reason not to expose it.

By using it as a surrogate, all it means is that you can't re-assign the key value, and who does that anyway?    As far as not controlling it, so what?  If it's just a matter of it being unique (which is what it should be), then it doesn't matter if sequential or not.

<<And you should generate unique, human-meaningful identifiers that are independent of the autonumber for the users -- >>

 Really?  What a pain.  In fact I'm dealing with a design right now that does just that, and it's a royal pain.   Every time I need to present the human readable identifier to a user, I need to do a join against the main table just to get it.  

 If they would have just stuck using the value of the autonumber, life would have been a lot simpler.   Even the managers I work with can see it.

 Only time I've seen an autonumber being a pain is if the seed value resets to some ungodly high number (like 84838448).  Then you've got a problem.   But I've only seen that happen once in the past 20 years.

Jim.
0
JoeMiskeyAuthor Commented:
First, let me preface this by saying that this is not a database I created.  It is one I inherited.  There are many things about it that I would have designed differently.

Generally, the autonumber shouldn't be something that the users attach value to.
They aren't.  All that autonumber is being used for is a unique identifier (precisely what Autonumber was intended for).  They just need to record the id number for certain records for audit purposes.  So they just need a unique value that they can search for at a later date.

So in this scenario, I tend to agree with Jim here.  Why create another variable just to do what Autonumber already does (provide a unique identifier)?  

A command button with a caption of Save Record is simple
The code can be a simple as
DoCmd.RunCommand acCmdSaveRecord
Thanks.  This is the line of code I was looking for.
0
Nick67Commented:
@Jim
then it doesn't matter if sequential or not.
Certain folks can be very ... orderly ... in their thought processes.
A value that is almost perfectly sequential can drive those kinds of folks insane.
Every time I need to present the human readable identifier to a user, I need to do a join against the main table just to get it.
That's a design issue.  The linchpin table is the one that contains the autonumber AND the unique identifier in my setup.  There isn't a multi-table query I have that won't have that table in there by default, so there is no pain point
Your mileage clearly varies.

And of course, there's the very issue at the heart of this question.  Depending upon circumstances and setup, the value displayed in an autonumber field has some volatility.  ESC in Access can cause a number to be chewed up, but then never actually committed.  Back in the day, my users made mistakes about how and what the autonumber was.
I still get occasions where users will hammer in a whole whack of data into a wrong location -- and I can go in and change foreign keys to 'move' it to where it needs to be.  That works because the keys are meaningless to users.

But it is a question of style and circumstance and the debate is very old.
You figure out what works and run with it.

@JoeMiskey  Glad I could help.
0
JoeMiskeyAuthor Commented:
Line of VBA code to force new record add was what I was looking for, but AfterUpdate event code also a good alternative.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.