How do I make a unique ID# for drop down box items?

[saintjohn4059 via AccessMonster.com]

Hi, I'm brand new to MS ACCESS and I am using MS ACCESS 2007. I would like to
make a drop down list of different animal species for an animal shelter
database. The list is no problem but I would like to have a unique ID# appear
when a specific species is selected. For example if someone clicks DOG then a
ID# would appear that begins with the letter "D", CAT would have an ID# that
begins with "C", Bovine=ID# begining with "B", etc. Then I would like to use
the unique ID# for a listing of each species in a Report.

Is any of this possible? As a newbie I can create a table with a progressive
ID# list but cannot figure out how to give each item a unique ID#.

 

[strive4peace]

this is possible ... but what about different species that begin with
the same letter?

the unique ID of a record does not need to be shown to the user; it
really doesn't matter what it is; the user can pick from text values and
a numeric ID can be stored

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal


*
have an awesome day
*

 

[saintjohn4059 via AccessMonster.com]

Hi Crystal,

The species selection is aviary, bovine, canine, equine, and feline (no
exotics or other species offered care). This is going to be a no-kill
facility with large volumes of animals and the text values can be highly
repetitive (i.e. dogs and/or cats named Sam) and with the unique number the
hard copy records can be better managed (much like a human medical care
office that files with a numbering system). Plus the ID numbering system
allows for better cooridnation of the animal through the various kennel
procedures such as medical and labratory procedures, outside services
required, grooming, adoption and/or fostering, etc. Thank you for the
"strive4peace" link and I will check it out, and may you too have an awesome
day!

Sincerly,
JSSherm

this is possible ... but what about different species that begin with
the same letter?

the unique ID of a record does not need to be shown to the user; it
really doesn't matter what it is; the user can pick from text values and
a numeric ID can be stored

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal

*
have an awesome day
*

Hi, I'm brand new to MS ACCESS and I am using MS ACCESS 2007. I would like to
make a drop down list of different animal species for an animal shelter

[quoted text clipped - 6 lines]

Is any of this possible? As a newbie I can create a table with a progressive
ID# list but cannot figure out how to give each item a unique ID#.

 

[Klatuu]

It would be best to use two fields to do this. Once using the letter
indentifier for the species and another for the unique number. You can then
use queries to combine the two fields for display. To assign the number, you
can use the After Update event of the combo box to assign the number and
present it in an unbound text box. You should have a hidden text box that is
bound to the unique number field. For example purposes:

Table fields
SpeciesID - Text(1)
AnimalNumber

Form Controls
cboSpecies - Your combo to select a species bound to SpeciesID
txtAnimalNumber - Hidden control bound to AnimalNumber
txtUniqueID - Display Control

Make the Control Source of txtUniqueID =cboSpecies & txtUniqueID

Private Sub cboSpecies_AfterUpdate()

Me.txtAnimalNumber = Nz(DMax("[AnimalNumber]", "tblAnimals", "[SpeciesID
= """ & Me.cboSpecies & """"), 0) + 1
Me.ReCalc

End Sub

The DMax find the highest existing number for the species selected. If no
records exsit for the species, the Nz converts the Null that would be
returned in that case to a 0, then 1 is added to it to get the next number.
The ReCalc causes cacluated controls to be recalculated. this is necessary
because populating a bound control programmatically will not recalc
calculated controls.

 

[strive4peace]

adding on...

Yes, it would be best to store the information separately ...
I would NOT make this the Primary Key for the table; multi-field keys
are more difficult to work with than single-field keys; this combination
can be unique in the table so it can be the identifier for human eyes.

Personally, I would use an autonumber field in the table for the Primary
Key -- and use that in related tables as the foreign key. The users
never need to see this number

~~~

The only reason to store the letter identifier would be if the species
is changed (maybe a mistake was made when the record was created) and
the animal is already tagged -- but if the Species is edited and you
would then tag it differently, then I would not store the species letter
at all -- you can calculate it anytime...

{Hi Dave <smile>}

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

you're welcome, JSSherm (what is your name?)

I added on under Klatuu's message with comments about storing the data

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

The species selection is aviary, bovine, canine, equine, and feline (no
exotics or other species offered care). This is going to be a no-kill
facility with large volumes of animals and the text values can be highly
repetitive (i.e. dogs and/or cats named Sam) and with the unique number the
hard copy records can be better managed (much like a human medical care
office that files with a numbering system). Plus the ID numbering system
allows for better cooridnation of the animal through the various kennel
procedures such as medical and labratory procedures, outside services
required, grooming, adoption and/or fostering, etc. Thank you for the
"strive4peace" link and I will check it out, and may you too have an awesome
day!

Sincerly,
JSSherm

this is possible ... but what about different species that begin with
the same letter?

the unique ID of a record does not need to be shown to the user; it
really doesn't matter what it is; the user can pick from text values and
a numeric ID can be stored

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal

*
have an awesome day
*

Hi, I'm brand new to MS ACCESS and I am using MS ACCESS 2007. I would like to
make a drop down list of different animal species for an animal shelter

[quoted text clipped - 6 lines]

Is any of this possible? As a newbie I can create a table with a progressive
ID# list but cannot figure out how to give each item a unique ID#.

 

[Klatuu]

Hi, Crystal,

I agree about the primary key. I did not address that in my previous post,
but it is a good point.

I'm not sure I agreee about not storing the species identifier. I'm not
sure a cat is going to change into a dog (although one of my cats acts like a
dog), nor will anyone confuse a dog with a cat.

The numbering technique I suggested uses different numbering for each
species. You would have a D00001 and a C00001. You say you could calculate
the species. How would you propose to do that?

--
Dave Hargis, Microsoft Access MVP

adding on...

Yes, it would be best to store the information separately ...
I would NOT make this the Primary Key for the table; multi-field keys
are more difficult to work with than single-field keys; this combination
can be unique in the table so it can be the identifier for human eyes.

Personally, I would use an autonumber field in the table for the Primary
Key -- and use that in related tables as the foreign key. The users
never need to see this number

~~~

The only reason to store the letter identifier would be if the species
is changed (maybe a mistake was made when the record was created) and
the animal is already tagged -- but if the Species is edited and you
would then tag it differently, then I would not store the species letter
at all -- you can calculate it anytime...

{Hi Dave <smile>}

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

It would be best to use two fields to do this. Once using the letter
indentifier for the species and another for the unique number. You can then
use queries to combine the two fields for display. To assign the number, you
can use the After Update event of the combo box to assign the number and
present it in an unbound text box. You should have a hidden text box that is
bound to the unique number field. For example purposes:

Table fields
SpeciesID - Text(1)
AnimalNumber

Form Controls
cboSpecies - Your combo to select a species bound to SpeciesID
txtAnimalNumber - Hidden control bound to AnimalNumber
txtUniqueID - Display Control

Make the Control Source of txtUniqueID =cboSpecies & txtUniqueID

Private Sub cboSpecies_AfterUpdate()

Me.txtAnimalNumber = Nz(DMax("[AnimalNumber]", "tblAnimals", "[SpeciesID
= """ & Me.cboSpecies & """"), 0) + 1
Me.ReCalc

End Sub

The DMax find the highest existing number for the species selected. If no
records exsit for the species, the Nz converts the Null that would be
returned in that case to a 0, then 1 is added to it to get the next number.
The ReCalc causes cacluated controls to be recalculated. this is necessary
because populating a bound control programmatically will not recalc
calculated controls.

 

[John W. Vinson]

Hi Crystal,

The species selection is aviary, bovine, canine, equine, and feline (no
exotics or other species offered care). This is going to be a no-kill
facility with large volumes of animals and the text values can be highly
repetitive (i.e. dogs and/or cats named Sam) and with the unique number the
hard copy records can be better managed (much like a human medical care
office that files with a numbering system). Plus the ID numbering system
allows for better cooridnation of the animal through the various kennel
procedures such as medical and labratory procedures, outside services
required, grooming, adoption and/or fostering, etc. Thank you for the
"strive4peace" link and I will check it out, and may you too have an awesome
day!

It's best NOT to mix meanings within a field. The species really doesn't
belong in the identifying number field; they are two different fields with two
different purposes. The animal-shelter database I've developed just uses a
sequentially assigned numeric identifier; at the shelter's insistance, I've
bent my purity a bit and include the year in it - e.g. 08-02481 would be a
"SoftSlip" identifier (the name if for historical reasons) issued during
fiscal year 2008. There's a separate field, entered using a combo box, for the
species (and another for the breed, for that matter).

If you embed the species in the unique identifier you'll need some VBA code to
assign it, the maintenance will be a chore (Hey! C-0451 got put into the
system, but that must have been a transcription error, he's a poodle!!), and
you'll have to restructure your database if you decide to start accepting
ferrets, wildlife, or llamas; or you might decide that you want to break the
"avian" down below the taxon Aves.

If you'ld like to discuss the issues of animal shelter database systems
offline (free of charge) contact me at jvinson <at> wysard of info <dot> com.
Edit the punctuation and remove the blanks.

 

[strive4peace]

Hi Dave,

"I'm not sure a cat is going to change into a dog (although one of my
cats acts like a dog), nor will anyone confuse a dog with a cat."

LOL! ... our cat is as big as a dog! ... and our large dog 'looks up'
to our cat, who is the king of the house! Our dog is also learning,
from the cat, how to be a mouser <smile>

No, the species would not change -- but there may be a data entry error
-- and that is one reason I usually use artificial instead of natural keys

"You would have a D00001 and a C00001. You say you could calculate
the species. How would you propose to do that?"

if you have, for instance:

Species
- SpeciesID, autonumber
- Species, text

Animals
- AnimalID, autonumber
- SpeciesID, text
- AnimalNumber, long integer

and SpeciesID is a combobox on the form with the following properties:

RowSource:
SELECT SpeciesID, Species
FROM Species
ORDER BY Species

Columncount --> 2
ColumnWidths --> 0;1.5
ListWidth --> 1.7

then, to calculate AnimalNumber, you could do this:

'~~~~~~~~~~~~~~~
dim strSQL as string _
mAnimalNumber as long

dim db as dao.database _
, r as dao.recordset

if isNull(me.SpeciesID) then exit sub

strSQL = "SELECT S.SpeciesID " _
& ", Max(A.AnimalNumber) as MaxNum " _
& " FROM Species as S INNER JOIN Animals as A " _
& " ON A.SpeciesID = S.SpeciesID" _
& " WHERE A.SpeciesID = " & me.SpeciesID

set db = currentdb
set r = db.openrecordset(strSQL, dbOpenSnapShot)

if r.eof then
mAnimalNumber = 1
else
mAnimalNumber = r!MaxNum +1
end if

me.AnimalNumber = mAnimalNumber

r.close
set r = nothing
set db = nothing
'~~~~~~~~~~~~~~~~

in addition to running when a new record is made, this code would also
run on the AfterUpdate event of SpeciesID

anytime the "human identifier" is required, you could join the Species
table to get the letter

.... but since it is only one character, I would make the human
identifier a text field and store the combined value

Animals
- AnimalID, autonumber
- SpeciesID, text
- AnimalCode, text, 7
(assuming you won't have more than 100,000 animals in a species; if you
will, you can change the length)

then, to calculate AnimalCode, you could do this:

'~~~~~~~~~~~~~~~

dim mAnimalCode as text _
mNum as long

if isNull(me.SpeciesID) then exit sub

mNum = nz(dMax("cLng(right(AnimalCode,6))" _
,"Animals","SpeciesID" = me.SpeciesID),0) + 1

me.AnimalCode = left(me.SpeciesID.column(1),1) _
& format(mAnimalNumber, "000000")

'~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

*
have an awesome day
*