Random populate ID

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'.

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.

Here is an attachment of the database
Incident-Report-Database---Copy.accdb

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.

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

Select allOpen 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.

Antonio.

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?

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.

Antonio.

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.

Nick, that sums it nicely up.

/gustav

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.

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.

yes that's what i want Nick67.  sorry guys iam still learning new thing with Access.

thank you all for your help.  sorry for being a pain lol.