auto generate random reference number for a record

[antonyx]

is there anyway to have the database autogenerate a random 8 digit reference number for each record in my 'jobs' table.. this of course must be unique..

maybe something like

7C7CYU99, or HJUHU889... im sure you get the picture.

 

[MichaelRed]

the picture I get is of a bad idea gone awry. i'm sure you have a rationale for wanting to do this, but generating random and unique alpha & numeric values will get to be a difficult chore as the recordset size grows. Autonumber -or a simple variation on the these can easily provide the unique ID or a random number generator can provide the randomness fo a set. however given an existing set of random values, getting another random value and assuringf it's uniquness to the existing set obviously requires comparing the new value to the existing set and -when there is a match- getting a replacement value and testing that in a loop until you get the new and unique to the existing set value. This can be either just time consumingly boring or simply impossible depending on the size of the recordset and the procedure for generating the new value.




MichaelRed

 

[TheAceMan1]

How are ya antonyx . . .

I could'nt agree more with [blue]MichaelRed[/blue].

[blue]Your reference# only has to be unique![/blue] . . . so what does it matter if its an eight digit number thats incremented on each new record (easily done in code)?

Perhaps you can give us more specifics on why reference# needs to be this way . . . (don't make things hard on yourself!).

See Ya! . . . . . .

 

[antonyx]

ok, well basically the database is a cab company..and the database will be storing jobs carried out by our drivers..
eg. payment details, customer details.. driver details, pickup and destination.. and so on

i use an autonumber (job id) which is the primary key for each record and which i use to link the job to the rest of my tables.

however, many customer ask for reference numbers when they book a job.. the problem is i dont really want to use the autonumber as the reference number for the customer, even tho the id will definately uniquely identify the record, it will sound a bit lame if a customer asks and i say..

"ok your reference number is 5"

so basicaly if possible can you tell me how to create a simple numberic reference value then for each job, would that be a lot simpler?

 

[Remou]

Autonumbers do not have to start at one. Would that suit?

 

[antonyx]

yes, could i make them start at 1150?

 

[Remou]

How you do this depends on whether you already have data entered or not. This:

[tt]DoCmd.RunSQL "ALTER TABLE tblTable ALTER COLUMN Field1 COUNTER(1150,1)"[/tt]

Should start the next number at 1150, but will not change existing numbers. Each table can only have one autonumber, so I suppose this would be a primary key for the table you are using.

 

[stix4t2]

Sometimes you can use codes in the code to make it easier on the one looking up the reference. So you may want to concat together the month and year and unique id to create the code.

02060001

This way when someone calls in with a reference, part of the code reflects when the task was performed. And if the number is 05220032, you know they are using the wrong number, or from the future.

 

[antonyx]

yes, that is what i would have ideally wanted stix. a ref no. that has some relation to the actual record

so lets say i wanted the code that you suggested..

2 digits for the month, 2 digits for the year and then 4 digits.

so feb 2006 will be 0206 and if its the first record the last 4 would be 0001

how could i edit my job table - job id field to do this..

and no, i have no records in my table at the moment.

 

[stix4t2]

You will want to leave job id alone because it ties your pickup records to the job record. Plus you really don't want to link on a text field. You probable want to create another field for reference, make it text to hold any preceding zeros.

The trick is to know when to build the reference. You may want to do it when the first key is entered into a record, use the Form_BeforeInsert. Or you may want to do it after the date is entered. And this would only be the case if date is not defaulting to current date. If date is entered it may be better to use Date_AfterUpdate

Private Sub date_AfterUpdate()
buildReference
End Sub
or
Private Sub Form_BeforeInsert(Cancel As Integer)
buildReference
End Sub

private sub buildReference()
dim maxRef as string, maxID As Integer
dim codeDate as string, maxDate as string

codeDate = format( date, "MMYY")
maxRef = dmax( "Ref", "Job" ) 'get last ref
maxDate = left(maxRef,4) 'get date code
if( maxdate < codeDate ) then 'test for new month
maxID = 0 'reset id to 0
else
maxID = CInt(right(maxRef, 4)) 'convert to int
end if
Me.ref = codeDate & format(maxID + 1, "0000")
end sub

Remember the number of zeros is place holder and can be as many zeros as you need, but the right function must use the same number. In the example if you don't use more than 10000 jobs in a month, resetting to 1 for each month, you could use 0000 within an 8 character code 2-month 2-year 4-id

So what does this do for you. Well you get the month year and the id's start recounting at 1 for each month. What are the pitfalls, or risk assessment. If they change the date the reference could be wrong. If two users are entering records at the same time, you may want to use the form_BeforeUpdate so the lookup happens right before writing the record. Why, cause if two users pull the same max before either are saved, they will calculate the same reference. But you have to tell me what you think the risks are.

Also, after hearing that this is a cab company, you should seriously consider the two tab form for easier use. I would think that pickup is almost guaranteed to the job, and having to open/close a form will suck after awhile.

Good Luck

 

[antonyx]

yes i see what you mean.

firstly there will only ever be one user using the database.

secondly my user will always be writing the details on paper and then entering them. this is because sometimes a call will come for a job, and they wont be by the computer and for various other reasons. so when its written on paper they can freely enter it into the database. the tabbed form would be alot easier for live calls, but this database wont be used to enter jobs live.

Private Sub Job_Date_AfterUpdate()
    
dim maxRef as string, maxID As Integer
dim codeDate as string, maxDate as string
    
    codeDate = format( [b]date[/b], "MMYY")
    maxRef = dmax( [b]"Ref"[/b], "Job" )    'get last ref
    maxDate = left(maxRef,4)         'get date code
    if( maxdate < codeDate ) then    'test for new month
         maxID = 0                   'reset id to 0
    else
         maxID = CInt(right(maxRef, 4))   'convert to int
    end if
    Me.ref = codeDate & format(maxID + 1, "0000")

End Sub

ok i have added a reference number (text datatype) to my job table.

in my form the control for the ref no. field is called
'Reference_No', should i change the above bolded "Ref" to Reference_No?

my control for the job date (which is where the code will go in the after update is called 'Job_Date'. shall i change the bolded 'date' to Job_Date?

also my form is called 'Booking_Main', shall i change in the code where it says "job" to Booking_Main?

 

[stix4t2]

Your getting it good. But lets consider one last point. If I enter a April 1st and then enter a March 31st. Oops are DMAX is going to return 04060001, but the March date needs the last 0306???? number.

So lets change the DMAX function to this
DMAX( "Ref", "Job", "Ref like '" & codeDate & "*'")

by adding the filter, we can isolate to the month.

Here's another issue, what if maxRef comes back empty because of the new filter.

dim maxRef as variant, maxID As Integer
dim codeDate as string, maxDate as string

codeDate = format( date, "MMYY")
maxRef = DMAX( "Ref", "Job", "Ref like '" & codeDate & "*'")
if( IsNull(maxRef)) then 'test for new month
maxID = 0 'reset id to 0
else
maxDate = left(maxRef,4) 'get date code
maxID = CInt(right(maxRef, 4)) 'convert to int
end if
Me.ref = codeDate & format(maxID + 1, "0000")

Note that we changed the maxRef type to Variant to accept the null if the value is not found, strings error when you try to use a null. We probably should of done this from the first, cause the first record being created would of generated the error. Then we test to see if the table has any records for the month, and if it does, we get the last.

While I am sitting here lets discuss two other things. The tab idea wasn't just if they were entering in front of the computer, I find it annoying to open and close forms over and over again to add some other information. Also the use of a continuous form lets me be sure I am not adding something twice, or to double check I didn't miss anything.

Lastly lets talk about test. Entry is only part of the battle. It is important that you also go back and check the tables to be sure they are right also. Enter some information and check the tables to verify the keys are right, the data is right. Try to think of what the user might do to mess things up. Too many times in the past I have been burnt not checking the tables. Well enough times to want to pass it on to you. Good luck, it sounds like a fun project and you are doing good.

 

[antonyx]

yes, i got it working,, woohoo

thank you, thank you.

see i dont get tabbed forms. i would only want to use a tabbed form where each tab would represent a dif table.

eg. having three tabs, job, driver, pickup.. or woteva

when i create a form in design view, and click tabbed form, and place it in my form.. in my ideal world i would be able to set the record source for each tab, which would allow me to place the correct fields from the different tables in the tabs.

i cant seem to do this tho..i realise that a tabbed form can be used to fit many fields in a form when u want to avoid scrolling down the page..

can tabbed forms only be used in a form when you base that form on a query.. hence using multiple fields from multiple tables??

 

[MichaelRed]

faq700-184


MichaelRed

 

[stix4t2]

Tabs merely add to the real estate and organization of the form. You can spread fields across them or use sub forms within them. There is no tie to recordsource. I use them to classify data within a data set. What does that mean, different parts of information may be important to different users, or fields may just seem to go together. A tab can be used as a classifier, so the user gets a better understanding of the type of data on the tab and and overall appreciation of the data by its tabs. Its about the informing the user.

 

[MichaelRed]

... cant use subforms ... "?

At least in my less than perfect memory, subform is just another control. Addressing some of the controls properties is a bit more arcane than the average text box, but all controls are addressable.

the only real restriction is tha the sub form needs to be a continuious form?






MichaelRed

 

[stix4t2]

Michael, the tab discussion was relevent to another conversation antonyx and I were having about entry. It is my fault for mentioning it on this thread. Sorry about the confusion.