Generating an auto number with a defalt value

[W E B G U R L]

Maybe auto number is the correct thing I should be using.

I have to enter new listings for a real estate agent into a data base.
He wants a unique number for each new listing to use as a reference for the specific
listing.

How would I best do this? He would prefer to have something like ABC123, ABC124, ABC125

As you can guess, I am a beginner, so if there is a tutorial on this (if it is
complicated), I would appreciate the URL as well.


Many thanks

 

[Joseph Meehan]

Maybe auto number is the correct thing I should be using.

I have to enter new listings for a real estate agent into a data base.
He wants a unique number for each new listing to use as a reference
for the specific listing.

How would I best do this? He would prefer to have something like
ABC123, ABC124, ABC125

As you can guess, I am a beginner, so if there is a tutorial on this
(if it is complicated), I would appreciate the URL as well.


Many thanks

If he wants consecutive numbers, then you don't want to use autonumbers.
They are designed to be unique, but they often end up not being consecutive.
The ABC part can be easy or a problem, again depending on what is desired.
If all numbers will have the ABC prefix, then you can just display the
autonumber with an ABC prefix but not store the ABC as part of the number.
Keep in mind that the data in the table is not for human consumption. Only
forms queries and reports are for human consumption and in those you can
show the number with the ABC.

 

[W E B G U R L]

Thanks.

He really wants Access to generate the number for him, into his database which is used on
his website. Auto number seems to be the easiest way to do this I thought, however he does
NOT want his listings to be shown as 1, 2, 5 - what ever is generated. He would prefer to
create a starting point for the numbers, then allow Access to choose the next number for
each new listing.

What is an alternative for him then? Can Access give a new number for a new entry, other
than an auto number?




| W E B G U R L wrote:
| > Maybe auto number is the correct thing I should be using.
| >
| > I have to enter new listings for a real estate agent into a data base.
| > He wants a unique number for each new listing to use as a reference
| > for the specific listing.
| >
| > How would I best do this? He would prefer to have something like
| > ABC123, ABC124, ABC125
| >
| > As you can guess, I am a beginner, so if there is a tutorial on this
| > (if it is complicated), I would appreciate the URL as well.
| >
| >
| > Many thanks
|
| If he wants consecutive numbers, then you don't want to use autonumbers.
| They are designed to be unique, but they often end up not being consecutive.
| The ABC part can be easy or a problem, again depending on what is desired.
| If all numbers will have the ABC prefix, then you can just display the
| autonumber with an ABC prefix but not store the ABC as part of the number.
| Keep in mind that the data in the table is not for human consumption. Only
| forms queries and reports are for human consumption and in those you can
| show the number with the ABC.
|
| --
| Joseph Meehan
|
| Dia duit
|
|

 

[Guest]

(Q) I have a field which contains records in the format "REC-1", "REC-2",
"REC-3" etc. For new records, how can I automatically determine the value of
this field for a new record such that the numerical part of the field gets
incremented by 1.

(A) Use the Right and DMax function to return the numeric part of the
highest value of the field "FOO" and add one to it, concatenating the result
in the end to "REC-". You can either use the following example as the
DefaultValue of the field or assign it manually on the form using AfterUpdate
event of another control. For example, if the field name in the above example
is "FOO" and the tableName FOOTable, then the expression would be

="REC-" & right(DMax("FOO", "FOOTable"), _
Len(DMax("FOO", "FOOTable")) - _
InStr(1, DMax("FOO", "FOOTable"), "-")) + 1
Note: As the multiple calls to DMax function can slow down this operation on
a large table, I'd suggest against using such an expression as DefaultValue.
Instead, assign this new value to a hidden control on your form which is
bound to field FOO. This way you only have to use DMax once. For example,

Private Sub SomeField_AfterUpdate()
Dim strMax as string
strMax =DMax("FOO", "FOOTable")
me!HiddenFooCtl = "REC-" & right(strMax, _
len(strMax) - _
Instr(1,strMax, "-")) +1
End Sub


Also you can go at:
http://www.mvps.org/access


Thanks

 

[Rick Brandt]

Thanks.

He really wants Access to generate the number for him, into his
database which is used on his website. Auto number seems to be the
easiest way to do this I thought, however he does NOT want his
listings to be shown as 1, 2, 5 - what ever is generated. He would
prefer to create a starting point for the numbers, then allow Access
to choose the next number for each new listing.

What is an alternative for him then? Can Access give a new number for
a new entry, other than an auto number?

You can assign the number in the BeforeUpdate event of the form...

If Me.NewRecord Then
Me!NumField = Nz(DMax("NumField","TableName"), 0) + 1
End If

Replace the zero above as appropriate to give you the desired starting number
then display with...

=Format(NumField, "\A\B\C\-0000")