automatic consecutive numbering

[Guest]

Hello,

I appreciate having this vehicle available to me to ask my questions and I appreciate the knowledgeable people taking the time to answer them.

I have a very simple database that tracks the equipment numbers assigned to equipment located in my facility. I would like to design the Equipment Number field to automatically insert the next consecutive number available (no duplicates) every time the "Add New Record" button is used (for example, EQF001, EQF002 etc.)

Would I use an expression to do this? If so, could someone give me an example of what this expression would look like.

Thanks

 

[Guest]

Use an AutoNumber data type for your EquipmentID field. You can add the characters with an InputMask

----- pflynn wrote: ----

Hello

I appreciate having this vehicle available to me to ask my questions and I appreciate the knowledgeable people taking the time to answer them

I have a very simple database that tracks the equipment numbers assigned to equipment located in my facility. I would like to design the Equipment Number field to automatically insert the next consecutive number available (no duplicates) every time the "Add New Record" button is used (for example, EQF001, EQF002 etc.

Would I use an expression to do this? If so, could someone give me an example of what this expression would look like

Thank

 

[Roger Carlson]

I'm sorry, but I believe using an Autonumber for this purpose is a very bad
idea. First of all, autonumbers can develop gaps that can never be
recovered. Secondly, the number can never be changed and in any field with
real-world meaning, inevitably someone is going to want to change it some
time. Third, while the EQF can be added with an input mask, it will not be
stored, just displayed. Any Find function would have to strip it off.

Autonumbers should never be used for anything other than primary key values
without any real-world significance.

A better idea would be to simply create your own using the DMax() function.
On my website is a small sample database called AutonumberProblem.mdb which
illustrates how.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Use an AutoNumber data type for your EquipmentID field. You can add the characters with an InputMask.

----- pflynn wrote: -----

Hello,

I appreciate having this vehicle available to me to ask my questions

and I appreciate the knowledgeable people taking the time to answer them.

I have a very simple database that tracks the equipment numbers

assigned to equipment located in my facility. I would like to design the
Equipment Number field to automatically insert the next consecutive number
available (no duplicates) every time the "Add New Record" button is used
(for example, EQF001, EQF002 etc.)

Would I use an expression to do this? If so, could someone give me

an example of what this expression would look like.

 

[lbrinkman]

I agree with Roger Carlson. First, parse the number from the EquipmentNumber
field using the MID() function: strEQF = Mid("EquipmentNumber", 4). For
EQF100, it will yield a string/variant value of 100; for EQF2005, it will
yield a string/variant value of 2005.
Then use the CInt() function to change strEQF to an Integer OR CLng() to
change strEQF to a Long Integer: intEQF = CInt(strEQF) OR lngEQF =
CLng(strEQF).
Then use the DMAX() function to get the highest value: intHighest =
DMax("intEQF") OR lngHighest = DMax("lngEQF").

The next NEW number will be: intNew = intHighest + 1 OR lngNew =
lngHighest + 1
Then attach the EQF to the beginning: strNewEqf = "EQF" & intNew OR
strNewEqf = "EQF" & lngNew.

You can do this using code, but you could also do this in a query by having
functions inside other functions.
--- Phil Szlyk

Use an AutoNumber data type for your EquipmentID field. You can add the characters with an InputMask.

----- pflynn wrote: -----

Hello,

I appreciate having this vehicle available to me to ask my questions

and I appreciate the knowledgeable people taking the time to answer them.

I have a very simple database that tracks the equipment numbers

assigned to equipment located in my facility. I would like to design the
Equipment Number field to automatically insert the next consecutive number
available (no duplicates) every time the "Add New Record" button is used
(for example, EQF001, EQF002 etc.)

Would I use an expression to do this? If so, could someone give me

an example of what this expression would look like.

 

[Guest]

Thank you, this was extremely helpful. You helped answer my concerns regarding gaps and I knew with my limited knowledge that I could have only one primary key.

I will go to your website and study your example.

Thanks again
--
paula

I'm sorry, but I believe using an Autonumber for this purpose is a very bad
idea. First of all, autonumbers can develop gaps that can never be
recovered. Secondly, the number can never be changed and in any field with
real-world meaning, inevitably someone is going to want to change it some
time. Third, while the EQF can be added with an input mask, it will not be
stored, just displayed. Any Find function would have to strip it off.

Autonumbers should never be used for anything other than primary key values
without any real-world significance.

A better idea would be to simply create your own using the DMax() function.
On my website is a small sample database called AutonumberProblem.mdb which
illustrates how.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Use an AutoNumber data type for your EquipmentID field. You can add the characters with an InputMask.

----- pflynn wrote: -----

Hello,

I appreciate having this vehicle available to me to ask my questions

and I appreciate the knowledgeable people taking the time to answer them.

I have a very simple database that tracks the equipment numbers

assigned to equipment located in my facility. I would like to design the
Equipment Number field to automatically insert the next consecutive number
available (no duplicates) every time the "Add New Record" button is used
(for example, EQF001, EQF002 etc.)

Would I use an expression to do this? If so, could someone give me

an example of what this expression would look like.

 

[Guest]

Thanks for the detailed response. Because I am such a beginner, when I first read your response I though my head was going to explode. However, I kept and keep reading it over and over again and I understand more and more. I do not understand how to actually implement your instructions (either by coding or functions within functions) but feel if you were to get me started I may be able to struggle along. I think I can pick it up by trial and error. Is this an unreasonable or unrealistic request?

Thanks,

--
paula

I agree with Roger Carlson. First, parse the number from the EquipmentNumber
field using the MID() function: strEQF = Mid("EquipmentNumber", 4). For
EQF100, it will yield a string/variant value of 100; for EQF2005, it will
yield a string/variant value of 2005.
Then use the CInt() function to change strEQF to an Integer OR CLng() to
change strEQF to a Long Integer: intEQF = CInt(strEQF) OR lngEQF =
CLng(strEQF).
Then use the DMAX() function to get the highest value: intHighest =
DMax("intEQF") OR lngHighest = DMax("lngEQF").

The next NEW number will be: intNew = intHighest + 1 OR lngNew =
lngHighest + 1
Then attach the EQF to the beginning: strNewEqf = "EQF" & intNew OR
strNewEqf = "EQF" & lngNew.

You can do this using code, but you could also do this in a query by having
functions inside other functions.
--- Phil Szlyk

Use an AutoNumber data type for your EquipmentID field. You can add the characters with an InputMask.

----- pflynn wrote: -----

Hello,

I appreciate having this vehicle available to me to ask my questions

and I appreciate the knowledgeable people taking the time to answer them.

I have a very simple database that tracks the equipment numbers

assigned to equipment located in my facility. I would like to design the
Equipment Number field to automatically insert the next consecutive number
available (no duplicates) every time the "Add New Record" button is used
(for example, EQF001, EQF002 etc.)

Would I use an expression to do this? If so, could someone give me

an example of what this expression would look like.

 

[Chris Nebinger]

Keep in mind that the Primary Key can consist of more than
one field:

Field1 Field2
A 1
A 2
A 3
B 1
B 2
B 3

As long as the combination of those two fields is unique,
than it guarentees uniqueness.

Also, you can have more than one unique index. That would
essentially be the same as a Primary Key, but slightly
different...



Chris Nebinger

-----Original Message-----
Thank you, this was extremely helpful. You helped answer

my concerns regarding gaps and I knew with my limited
knowledge that I could have only one primary key.

I will go to your website and study your example.

Thanks again