How do I make a unique ID# for drop down box items?
in [Tables]
|
From: saintjohn4059 via AccessMonster.com on 2 Jul 2008 23:08 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#. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200807/1
From: strive4peace on 3 Jul 2008 00:04 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 wrote: > 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#. >
From: saintjohn4059 via AccessMonster.com on 3 Jul 2008 07:49 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 strive4peace wrote: >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#. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200807/1
From: Klatuu on 3 Jul 2008 11:33 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. -- Dave Hargis, Microsoft Access MVP "saintjohn4059 via AccessMonster.com" wrote: > 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#. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200807/1 > >
From: strive4peace on 3 Jul 2008 12:50 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 :) * Klatuu wrote: > 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.
|
Next
|
Last
Pages: 1 2 Prev: How to user Dataset created by Visual Studio 2008 Next: Little stevie is soliciting again. |