Randomize autonumber

[Orazio]

Hi everybody,

i try to change an autonumber with "New Values" set to
Increment by code (using tdf.fields
(xxxx).defaultvalue="GenUniqueID()").
However, when i put new filed in this table the new values
is setting to an autonumber but when i saw the table at
design view the propertis "new value" is "Increment" and
not randomize!!!!

What's up, where's the bug?

Thanks to help

 

[Tim Ferguson]

i try to change an autonumber with "New Values" set to
Increment by code (using tdf.fields
(xxxx).defaultvalue="GenUniqueID()").
However, when i put new filed in this table the new values
is setting to an autonumber but when i saw the table at
design view the propertis "new value" is "Increment" and
not randomize!!!!

The DefaultValue is a data property, and not part of the User Interface --
this means that it knows nothing of VBA or modules or Windows or anything.
You can use a small number of functions that are known to the DBEngine
(Date, Year, and so on).

In your case, you would have had to put in an equals sign, like this:

.DefaultValue = "=GenUniqueID()"

but even then it would not have worked. As it did, it saw the string
literal and provided that as a text default, which is what you would
expect. I am intrigued that you wanted an autonumber and obviously designed
a Text field.

You can use the BeforeUpdate event on the form to call the GenUnique()
function, but that will obviously not fire when you use the datasheet, VBA,
SQL, Excel or anything else to insert new records. If you absolutely have
to have a Trigger facility, you'll need to move up to a real database like
MSDE/ SQL Server or MySQL or Oracle etc.

Hope that helps


Tim F

 

[orazio]

thanks for your answer,
but GenUniqueID is an internal function of Access that
allow to generate a random number.
I need to know why this function works correctly but in
design view of the table Access don't write Random.
Maybe a notorius bug or where i make a mistake?

thanks

 

[Tim Ferguson]

thanks for your answer,
but GenUniqueID is an internal function of Access that
allow to generate a random number.

Not on mine... although I only have Access 2000. Regardless, it has to be a
Jet function (rather than an Access function) to get it to work in a
..DefaultValue property.

I need to know why this function works correctly but in
design view of the table Access don't write Random.
Maybe a notorius bug or where i make a mistake?

Okay... a little googling has revealed this page

<http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dao360/html/daprodefaultvalue.asp>

and I did not know before about this use. It does seem to work, however,
and putting

=GenUniqueID()

in the Default Value box in table design does seem to generate some kind of
random autonumber. It is odd that you get a curious #Name? error until the
first field is filled, when the error is replaced by a random value. I
assume that doing the same in dao would work too:

.DefaultValue = "=GenUniqueID()"

but I am not tempted to try it as I cannot for the life of me see (a) what
advantage this has over simply setting the field to be autonumber and
choosing a random or counter type, or (b) why anyone would want to do this
in code.

The answer is that you can do this by using the correct DefaultValue
string, but it seems to me to be a very strange thing to want...

Best of luck


Tim F