How to Make "Autonumber" Invisible When Adding a New Record

[wilderfan]

Is there a way to make the string "autonumber" invisible when adding a new record?

Currently, when I add a new record, "autonumber" shows up in the primary key textbox.

My preference would be for the textbox to remain blank until I have completed filling in the other textboxes (fields) of the form.

Thanks in advance for any advice.

 

[boblarson]

I never make it visible in the first place. If done correctly, the user never need see the autonumber - at all.

 

[wilderfan]

Are you saying that the form should not contain a textbox for the primary key?

Or is there a way to keep the PK textbox on the form without showing the string "autonumber" every time a new record is added?

The users appear interested in seeing the autonumbers once they are generated. But don't necessarily want to see the string "autonumber" show up on their form.

 

[KenHigg]

Could you have an unbound text box that references the text box that has the autonumber field as its recordsource and hide the bound text box?

 

[gemma-the-husky]

first, you can make the textbox for the bound primary key not visible - it will still get populated, and you dont need to enter the control, do you

alternatively, however, sometimes I (as developer) am interested in seeing it, so i make it enabled false/locked false to grey it out

 

[boblarson]

Are you saying that the form should not contain a textbox for the primary key?

Or is there a way to keep the PK textbox on the form without showing the string "autonumber" every time a new record is added?

I am saying that I keep the text box in visible. Autonumbers, are not meant for human consumption. They are normally "behind the scenes" items that should really not have meaning to the user. So, they never need be seen.

The users appear interested in seeing the autonumbers once they are generated. But don't necessarily want to see the string "autonumber" show up on their form.

You can do as Ken suggests, but I'd still argue against the use of Autonumbers for anything more than behind the scenes Primary Keys, UNLESS you know the risks of doing so and are fully prepared for the potenial results.

 

[KenHigg]

Personally - I think the numbers generated by autonumber are fine for use by the end user as long as they realize not to rely on the sequential element. A lot of times it a user needs a ref for a record and if you have a composite pk they have to use that and that can be cumbersome...

 

[missinglinq]

I'm afraid that I,as well as the vast majority of experienced developers, agree with Bob on this one. AutoNumbers simply shouldn't be referred to by end user for anything. If you need an reference number for you users then generate one.

Having said that, I believe the OP only wants the (AutoNumber) that appears before the actual autonumber is populated not to show up, and this can be done (in Access 2000 or later) by using Conditonal Formatting.

Select the field and go to Format - Conditional Formatting

Set Condition1 to

Expression Is

IsNull([YourFieldName])

and change the font/fore color to white.

The textbox will appear blank until the form is dirtied and the Autonumber is populated.

 

[boblarson]

I'm afraid that I,as well as the vast majority of experienced developers, agree with Bob on this one. AutoNumbers simply shouldn't be referred to by end user for anything. If you need an reference number for you users then generate one.

Having said that, I believe the OP only wants the (AutoNumber) that appears before the actual autonumber is populated not to show up, and this can be done (in Access 2000 or later) by using Conditonal Formatting.

Select the field and go to Format - Conditional Formatting

Set Condition1 to

Expression Is

IsNull([YourFieldName])

and change the font/fore color to white.

The textbox will appear blank until the form is dirtied and the Autonumber is populated.

Hey, that is a great suggestion!

 

[missinglinq]

I'd worked that up for someone else before. BTW, since a couple of people mentioned keeping the AutoNumber invisible on the form; the AutoNumber doesn't even have to be on the form at all, it'll still get populated in the table. And AutoNumbers are locked by default if they appear on a form.

 

[pbaldy]

I'm afraid that I,as well as the vast majority of experienced developers, agree with Bob on this one. AutoNumbers simply shouldn't be referred to by end user for anything. If you need an reference number for you users then generate one.

Well, Bob actually said (bold added):

"I'd still argue against the use of Autonumbers for anything more than behind the scenes Primary Keys, UNLESS you know the risks of doing so and are fully prepared for the potenial results"

I agree with Bob (and Ken), and I've used Autonumbers as a field visible to the users on several occasions. I just finished a reservation system where I used Autonumber as the reservation number. They didn't care if numbers were skipped, etc. There are certainly many times when the autonumber isn't appropriate to be visible, but why go through the work of generating another number and having an extra field if that one serves the purpose?

 

[KenHigg]

I'm afraid that I,as well as the vast majority of experienced developers, agree with Bob on this one. AutoNumbers simply shouldn't be referred to by end user for anything. If you need an reference number for you users then generate one.

Thats an interesting proclamation.

How do you propose to generate a reference number?

Edit: Your condidtional formatting suggestion was a good idea - Thanks for the tip

 

[wilderfan]

It seems like I've triggered a debate of sorts !

I'll try the conditional formatting suggested by missinglinq.

Thanks !

 

[gemma-the-husky]

i still think it is useful to see them on some occasions - eg if you are trying to trace what a complex process is doing, it is nice to see the actual record numbers (pks) that are involved. so you can follow it through to related tables, eg explicitly set the autonumber as a query criteria to see what is happening.

its for a similar reason we hate lookup fields in tables isnt it - its nice to see exactly what data the system is really trying to handle

 

[KenHigg]

I've yet to hear a good reason why the end user shouldn't see (and use as a record reference) an autonumber pk. -? Having a second custom number seems a bit redundant to me - I suppose if you're building the system you can do as you please -

 

[missinglinq]

Thats an interesting proclamation.

How do you propose to generate a reference number?

Edit: Your condidtional formatting suggestion was a good idea - Thanks for the tip

I' m just stating what any review of the on line literature will show you, Ken. Doesn't mean you're not entitled to your opinion! If it works for you that's great. But the fact of the matter is most users do care about skipped numbers, with the exception of those for deleted records, which they seem to understand. And I've seen bunches of posts where developers did as Paul did, being assured that skipped numbers didn't matter, only to have the clients come back after the fact and demand that this be "fixed," which is much harder to do after the fact.

The standard way of rolling your own auto-incrementing number is some variation of this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub

 

[KenHigg]

You're still facing the same issues with a roll your own. A record gets deleted and you have a gap. The acid fix for gaps if that is a business requirement is to never allow the users actually delete a record but rather mark it as deleted in a field.