Auto number generator on a form

[ChrisTheJaysFan]

Hey all,

What i have is a form users input into which is received from phone calls. Right now the users have the ability to provide service numbers to those who call in looking for them. This has to be a randomly generated number so there will be no duplicates Right now they are using a web applet to produce the number then copying that number into the form and saving the record.

What i want is a command button on the form called cmdNumberGen. When clicked this button will populate a text field on the form with the number which is then written back to the table the form is based on for reporting purposes. Ideally the number generated would work with a short text string at the beginnning...........for example when clicked the number ISSP1 is entered for the first code........when clicked again by another or the same user ISSP2 is generated and so on.

Is there a simple way for a non-programmer to accomplish this? I have something very similar but unfortunately do not have the coding in front of me at work.

Thanks in advance,
Chris

 

[smedvid]

I sometimes use date & time for a Unique identifier. htwh,

Private Sub Command0_Click()
Dim ldTime As Date
Dim lcID As String
ldTime = Now()
lcID = "IPPS"
lcID = lcID & Hour(ldTime) & Minute(ldTime) & Second(ldTime)
' or add MONTH(ldTime) & DAY(LdTIME) & YEAR(ldtime) ...
Me.ID.Value = lcID
Me.Recalc
End Sub


Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[ChrisTheJaysFan]

Hi,

First off thanks for the reply.....i appreciate it.

It is a good idea to use date and time however i have multiple users giving multiple approvals at virtually the same time so they could conflict. What i really need is a number scheme giving ISSP1, ISSP2, ISSP3 etc. Is there a way to generate these by clicking a command button and having that value go in the assigned text box?

Thanks again
Chris

 

[smedvid]

In order to check the next available number, you would need to do a DMax() LookUp or SQL Query to locate the highest key value. Then increment that value by 1. Of course, the risk is that someone else may get that number at that specific moment. In that case, you would need to write an error handler to check if the SQL used to Append fails and if so, retry with the next value? A little bit of work. Is it possible to store User ID with a Date/Time Stamp to establish a unique record? That way, you could use date/time. Yet another option with date/time is to use date/time and attahced a one or two digit random number or alpha character. htwh,

Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...