Alpha Numeric auto-generated number based on a combo box...

[Neowynds]

Hello all,

I am done with my Security Incident Report database except for one thing. I need it to auto generate a case number for each file as they are entered in. The format for the number needs to be NM1234. The two text characters will be dependant upon which location they choose from a drop down combobox which is based off of a locations query using fields from tblLocations. When the user pulls the drop down box to a certain location (i.e. podunk site) I need the database to automatically assign a case number such as ps0001. Obviously this needs to be a unique number for each incident, however I am terribly inexperienced with number systems, the one time I tried to do it in the past it screwed up like I was told it would by some people in here. Sorry for doubting you guys. I should have known better

Neowynds

 

[hymn]

I used the code below for a squential No for categories in a Stock db years ago you might find it helpful

'to set sequential number
Set Db = CurrentDb()
Set rds = Db.OpenRecordset("Select distinctrow Max(tbl_stock.CatNo) as MaxOfcatno from tbl_stock Where((tbl_stock.category='" & Forms![frm_stock]![Category] & "'));", DB_OPEN_DYNASET)
Me![CatNo] = IIf(IsNull(rds![Maxofcatno]), 1, rds![Maxofcatno] + 1)
Me![CatNo].Requery
Me!PartNo = [Category] & [CatNo]

Hope this helps
Hymn

 

[Neowynds]

Looking at the code with my limited knowledge it looks like it would work for me, however I am a bit of a newbie, so where exactly would it go? In an event and if so which one?

Thanks for the Quick response

Neowynds

 

[hymn]

This is a afterupdate event on your Combo
Private Sub Category_AfterUpdate()


Set Db = CurrentDb()
Set rds = Db.OpenRecordset("Select distinctrow Max(YourTableName.YourFieldName(CatNo) thathasthe number ) as MaxOfcatno from YourTableName Where((YourTableName.YourComboName='" & Forms![YourFormName]![YourComboName] & "'));", DB_OPEN_DYNASET)
Me![CatNo] = IIf(IsNull(rds![Maxofcatno]), 1, rds![Maxofcatno] + 1)
Me![CatNo].Requery
'Me!PartNo = [Category] & [CatNo]

Hope this helps
Hymn

 

[MichaelRed]

numbering schemes of this nature fail in multiuser stuations. The root cause of hte failure is the lack of locking the recordset during the retrievial, updating and re-writting process. this failure permits other users to attempt to achieve the same update, thus attempting to create duplicate values. the 'problem' gets worse with the growth of the recordset (the lookup takes longer).

see faq700-184 for an example of a module which does properly lock the record set (using a seperate table as the source). It would need some considerabe modification to suit your purposes, some of which have been recently discussed in these fora you should be able to find the discussions with the search feature using the faq number as the keyword.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Neowynds]

Thank you both for the quick responses,

If the recordset was locked properly, would the code provided above be feasible?

Neo

 

[Neowynds]

Michael,

I do not have enough programming experience to properly modify the code listed in your FAQ. Also the FAQ does not cover this particular instance where I need the two letters within the Case Number to be dependant upon which post location is selected in the dropdown combobox (i.e. Ferris Park is selected in dropdown box case number is FP1234, with 1234 being the next in a sequence.) The prefix is the only variable, the 4 digit number needs to be a self increasing "autonumber". I hope this explanation is what descriptive enough for you to understand what the needs of the database are, I can get pretty scatterbrained with my explanations sometimes, because as I said I dont have the knowledge or experience to do the "heavy modifications" that you said would be needed on the code in the FAQ.

 

[MichaelRed]

I cannot customize the FAQ routine to suit your specific situation.

'Properly' locking the table/recordset of most any routine which retrieves the appropiate field-value would at least be closer. In general terms, routines which lock a recordset will have an adverse impact on multiuser processes, as the time the recordset is locked is proportional to the number of records. In some relatively light use systems, routines which both lock the recordset and provide the re-try mechanisim will generally appear to be acceptable for some (small?) nimber of records, but will become more problematic as the number of records and the number of users adding records increase.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places