Unique Number will not create

[cocoonfx]

Hello i want to create a tracking number based on information entered into a form.

So i have my main table called "MainData" and this is in the backend and linked through to the front end where my form is.

I then have a query which is called "calllog" which has a field called "Call Number" inwhich i placed the following

=Year([Date]) & Left([CustomerName],3) & Right([Date Created],2)

This then gives me a unique number. Once i have entered a job in it will generate a number. Now this part works fine but it does not update the Maindata. How can i get the this to transfer the data to the maindata table???

 

[MStef]

The field "Call Number" link on the field in yoru table.

In Sub "Form Before_Update" put your code.
Me.Call Number = your code here

 

[Bat17]

This then gives me a unique number.

Are you sure? what happens if 2 Mr smiths ring up the same day?

Peter

 

[cocoonfx]

Hello

Excellent that works....

with regards to two mr smiths calling at the sametime in the date entered is = Now() which gives me the seconds of the date. so if both Mr Smiths rang at same day one at 1:30PM & 30 Seconds and the other mr Smith at 1:30 & 40seconds then the formula would give me:

2007Smi30 for the first call
2007Smi40 for the second call

My logic is that it would be like winning the lottery to get two mr smiths to ring at the same time on the same day at the same second. So this should hold up

 

[Bat17]

I bet your odds are quite high of getting a duplicate in the first year!

you are not using the day, only the seconds, so there are only 60 possible combinations and after 30 you have a better than even chance of duplicating!

the more names you have the more rapidly the chances of getting a duplicate rises!

Peter

 

[cocoonfx]

Yes just think of it, your right.... what if i did created a field which creates an autonumber and change that to ramdom numbers and then did a right or left trim on the number. Do you think this is possible?

 

[Bat17]

just use a standard auto number to give you a unique ID and use that as your reference

Peter