• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 57
  • Last Modified:

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
0
gsilouisvilleic
Asked:
gsilouisvilleic
  • 6
  • 5
  • 3
  • +2
3 Solutions
 
Nick67Commented:
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'.

random
0
 
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.
0
 
gsilouisvilleicAuthor Commented:
Here is an attachment of the database
Incident-Report-Database---Copy.accdb
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Nick67Commented:
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.
0
 
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.

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.
0
 
PatHartmanCommented:
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.
0
 
Nick67Commented:
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?
0
 
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.

Antonio.
0
 
Nick67Commented:
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.
1
 
PatHartmanCommented:
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.
0
 
Gustav BrockCIOCommented:
Nick, that sums it nicely up.

/gustav
0
 
Nick67Commented:
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.
0
 
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.
0
 
PatHartmanCommented:
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.
0
 
Nick67Commented:
maybe iam not explaining myself right.
Perhaps.

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?
1
 
gsilouisvilleicAuthor Commented:
yes that's what i want Nick67.  sorry guys iam still learning new thing with Access.
0
 
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.

Antonio.
Incident-Report-Database---ASR.accdb
0
 
gsilouisvilleicAuthor Commented:
thank you all for your help.  sorry for being a pain lol.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now