Solved

Random populate ID

Posted on 2016-08-19
18
37 Views
Last Modified: 2016-08-20
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
Comment
Question by:gsilouisvilleic
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 41762778
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
 

Author Comment

by:gsilouisvilleic
ID: 41762786
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
 

Author Comment

by:gsilouisvilleic
ID: 41762812
Here is an attachment of the database
Incident-Report-Database---Copy.accdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41762828
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
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41762849
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41762857
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
 
LVL 26

Expert Comment

by:Nick67
ID: 41762879
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
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41762945
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
 
LVL 26

Expert Comment

by:Nick67
ID: 41762969
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Expert Comment

by:PatHartman
ID: 41762991
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41762992
Nick, that sums it nicely up.

/gustav
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41763031
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
 

Author Comment

by:gsilouisvilleic
ID: 41763163
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
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 41763184
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 100 total points
ID: 41763200
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
 

Author Comment

by:gsilouisvilleic
ID: 41763262
yes that's what i want Nick67.  sorry guys iam still learning new thing with Access.
0
 
LVL 2

Accepted Solution

by:
Antonio Salva Ripoll earned 300 total points
ID: 41763404
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
 

Author Closing Comment

by:gsilouisvilleic
ID: 41763591
thank you all for your help.  sorry for being a pain lol.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now