VBA, Code to generate unique ID

mike
#1 / 5

Code to generate unique ID

I am trying to build a query that generates an unique ID
field. I keep thinking it should be easy to create a n,
n+1 loop to generate this field but I can't seem to pull
it off. Or am I making this more complicated than it
needs to be. Any help would be greatly appreciated.

Fri, 02 Dec 2005 23:47:27 GMT

Tim Ferguso
#2 / 5

Code to generate unique ID

Quote:

> I am trying to build a query that generates an unique ID
> field.

Private Sub Form_BeforeUpate(Cancel as Integer)

' don't overwrite if it's already there!
If IsNull(Me!txtMyIDNumberField) Then

' look up using DMax() and add one
' DMax returns null if there are no records, so
' we need to wrap it up in a NZ to prevent the error
Me!txtMyIDNumberField = _
NZ(DMax("MyIDNumberField","MyTable"),0) +1

End If

End Sub

Hope that helps. There are safer versions if you are in a multiuser system.

Tim F

Sat, 03 Dec 2005 02:21:04 GMT

mike
#3 / 5

Code to generate unique ID

Tim,
Is there a way to do this in a query as a function and not
a form? I also do not have an number ID field already to
Dmax on.

Quote:

>-----Original Message-----

>> I am trying to build a query that generates an unique
ID
>> field.

> Private Sub Form_BeforeUpate(Cancel as Integer)

> ' don't overwrite if it's already there!
> If IsNull(Me!txtMyIDNumberField) Then

> ' look up using DMax() and add one
> ' DMax returns null if there are no records, so
> ' we need to wrap it up in a NZ to prevent the error
> Me!txtMyIDNumberField = _
> NZ(DMax("MyIDNumberField","MyTable"),0) +1

> End If

> End Sub

>Hope that helps. There are safer versions if you are in a
multiuser system.

>Tim F

>.

Sat, 03 Dec 2005 02:35:34 GMT

Andrew Smit
#4 / 5

Code to generate unique ID

I frequently make things more complicated than necessary. As a result of a
recent case of this I had to do the same thing that you want to do. I
created this function to return a random long integer:

Public Function RandomLong() As Long
'Returns a random long integer.
Randomize
RandomLong = Int(2 ^ 32 * Rnd - 2 ^ 31)
End Function

There is a slight chance that this will return a value that you've already
used, but I think that's an acceptable risk as there are about 4.3 billion
possible long integers. You'll need to change this a bit to use it in a
query though as it will probably give the same result in every row - look at
help for the Rnd function and it may give you clues on how to do this.

Quote:

> I am trying to build a query that generates an unique ID
> field. I keep thinking it should be easy to create a n,
> n+1 loop to generate this field but I can't seem to pull
> it off. Or am I making this more complicated than it
> needs to be. Any help would be greatly appreciated.

Sat, 03 Dec 2005 03:37:53 GMT

Tim Ferguso
#5 / 5

Code to generate unique ID

Quote:

> Is there a way to do this in a query as a function and not
> a form? I also do not have an number ID field already to
> Dmax on.

I am now confused: I was thinking of a UniqueID field in terms of a do-it-
yourself autonumber.

What exactly are you trying to uniquely identify, and what are you going to
do with the number afterwards?

B wishes

Tim F

Sun, 04 Dec 2005 03:06:26 GMT