Random populate ID

I have a form in access that has a text box named T_Incident Record ID.  I want the text box to generate a random number as a ID when i open the form.  is there a way to do it?

thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

If the form is on a new record, and you want a new primary key value, and not sequential but random, you change the autonumber from 'increment' to 'random'.

gsilouisvilleicAuthor Commented:
The problem is that in the text field i see New and i wanted to show a number already generated before i start inputting data in the form.
gsilouisvilleicAuthor Commented:
Here is an attachment of the database
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

I wanted to show a number already generated before i start inputting data in the form.

Access will not show the number until the record is created.
You may hit ESC and decide to not commit your changes (among many, many other ways that you can decide not to save the entry of a new record!) so Access does not display the number in case the user (or coder) decides to use it before it is actaully commited.
This is by design, and cannot be changed.
Antonio Salva RipollCommented:
Hi gsilouisvilleic.

An option I use frecuently is to generate an alphanumeric ID. This ID consists in a string of 5 chars indicating the RecordType ("Fact_" for example), the string conversion of current date and time.

The code I have is similar to this one:

Function AutoNum() As String
    Const strStKey As String = "Fact_"
    Dim strDateKey As String
    strDateKey = Format(Now, "yyyymmddhhnnss")
    AutoNum = strStKey & strDateKey
End Function

Open in new window

Then in the form Current event I call the previous function and store the value in a variable, also, the value can be shown in a textbox or label.

Later, when the user press the Ok button, I open the recordset and store the values.

Best regards.


P.D. This is a very simple way to get a solution for your problem, but I can give a more complex and elaborated solution, using the Windows APIs, and wich generates a quasi random number (25 char string).

Normally, I use the simplest solution; it runs quick and is very difficult to duplicate the generated keys.
What is the point of seeing the number first?  To do that, you would need to add code to the form's current event to generate the number.  If you don't use the form's current event, you won't ever be able to add more than one record each time the form is opened.  That code would need to commit the number as well just to make sure no other user generated the same number.   Why would you want to save an empty record with nothing but the primary key?  You will end up with many empty records in the table since users frequently start something but don't finish it so the mere fact of opening a form causes an empty record to be saved?  Not a good idea.
I'll agree with @PatHartman that Access does not display the number for a reason, and that ensuring that users REALLY want to save a record before doing so is much, much better than fighting this and trying to clean up orphan records created by user mistakes.

If you are coming from a different database system. like SQL Server, you may be used to seeing the identity value being available before a record is committed and want the same behavior -- but really, I think the way Access hides the autonumber until the record is committed is superior.

When you see New you know that you are on a virgin, uncommited record.  If you get the number to display, you now cannot know if you are on a new record, or a record that has been left empty except for the autonumber field -- and that might indeed wind up being a critical distinction when the app goes into service.

What operational need are you trying to address?
Antonio Salva RipollCommented:
Hi @gsilouisvilleic.

Although I agree with Nick67 @ @PatHartman and that Access does not show the value of the next AutoNumber, it is also true that we often want to "reserve" that value, and display it on the form.

Previously I have made a simple routine, I have other, more complex, but also generate virtually unrepeatable values. If you are interested, I'll put them in another answer.

Best regards.

it is also true that we often want to "reserve" that value,
Access, when you make a bound form dirty, will provisonally select an autonumber value.
That value will either be committed to the record, or if the changes are discarded, it will NEVER be used by Access, so that value is in that sense 'reserved'

What Access does, sensibly enough, is not display it until it is committed.
It is easy enough, once a SaveRecord action is completed, to .Requery the control with the autonumber in it so that it displays the now-committed value.

But, and this is an important But! , generally, it is always a poor idea to have the user or business processes involved with the primary key values.  Those are for uniqueness, referential integrity, system and developer use.  Generate unique numbers for your users if you need them.  Don't use autonumbers for that purpose.  Sooner or later, you'll come to grief over it if you do.
When the bound table is Jet/ACE, Access generates the Autonumber as soon as the user dirties the record and it shows immediately.  The generated number is committed and if the user discards the new record before saving, that autonumber is discarded and never generated again.

When the bound table is ODBC, the Identity column is generated by the database engine when it saves the record.  This is disconnected from Access and so Access cannot see the ID until after the record is saved since the ID isn't even generated until the record is saved.  Typically, I add a Save button to forms bound to ODBC tables just for this reason.  In most cases my forms don't even show the autonumber but in the cases where it is actually used as an ID, the user may need to see it prior to closing the record.
Gustav BrockCIOCommented:
Nick, that sums it nicely up.

ID: 41762991

I stand corrected.  I had it backward.
Access bound to Access will show it
Accces bound to other RDBMS systems will not.

In most cases my forms don't even show the autonumber
I usually display it, in the far upper right, unlabeled, 8 point font and unassuming presentations
I use incremental numbers
When users notice it at all, they'll note that the numbers get bigger over time, and the preceptive notice that forms launched from one form to add additional information about a job to another form will have that number match up.
But they don't get hung up on the order, or any gaps!, in the autonumber sequence, which is the intent, of course.
gsilouisvilleicAuthor Commented:
maybe iam not explaining myself right.  so i have a form that i will do some data entry.  What i want is one of the text box to generate a number to identify the specific data i'am entering.  the reason is i want the person to know the id.  so if later he is looking for that entry, i am gonna have a search bar for them to type it in and retrieve the information.
Are you already using an Autonumber as the primary key?  Display that field.

If the tables are SQL Server, you'll have to save the record before the user can see the generated number.

We do not understand why you want to see the number BEFORE the user starts doing data entry.  That is the issue.  The Autonumber for a Jet/ACE table is generated as soon as the first character is typed into the form and if you are displaying the field, becomes visible to the user.  You have to work a little harder to show the Identity column for an ODBC table but not much harder.  However, in NO case is it reasonable to generate a number and save it before the user actually starts typing.
maybe iam not explaining myself right.

You asked
I want the text box to generate a random number as a ID when i open the form.
Now, most times for
so if later he is looking for that entry, i am gonna have a search bar for them to type it in and retrieve the information.
you are looking to generate a unique and User-friendly number, and not a random one.

Now, the easiest way to generate a unique AND random number is through a random-configured autonumber -- which we have discussed -- but really, an autonumber shouldn't be meaningful to the user, and having it displayed before a user first saves their entry is a problem.

I think what you may have wanted to ask is:
I want to generate and display a user-friendly unique ID for the new record immediately when a user opens my form for Data Entry and before they save their entered data.

Note that 'random' is not in that phrasing.
Is that what you want to do?
gsilouisvilleicAuthor Commented:
yes that's what i want Nick67.  sorry guys iam still learning new thing with Access.
Antonio Salva RipollCommented:
Hi @gsilouisvilleic.

I've prepared a sample with your database, using the simplest code I exposed you.

I made some changes, to the form and the T_Incident Table. In the form, I removed all macros and changed them with VBA code.  Also, instead of store the client name ant the type of incident text, I store the correspondant ID.

In the table, I changed your autonumeric by a text field, to accomplish with the generated ID, and instead of having a Input date and input time fields, now you have a Timestamp (date and time) field.

This is what I think you are looking for.

Here is attached.

Best regards.


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
gsilouisvilleicAuthor Commented:
thank you all for your help.  sorry for being a pain lol.
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 Access

From novice to tech pro — start learning today.