Can anyone help me? I need to create a database for logging all letters/correspondence that comes in to my department. each item will have a new record comprising date received etc. and I would like Access to give it a unique ID/number automatically but i would like this to consist of a number and also a prefix according to folder which the letter gets filed in. So if it goes in sales folder the iD would be SAL0001 or if admin ADM0001. The folder name will be another of the fields in the table.
I can't use autonumber because it won't use the prefix from the folder name and also it only has one range of numbers, whereas I need one for each correspondence folder.
Most databases i've used seem to be able to generate this kind of customised reference number. How can I do it with Access?
I'm assuming (best on the when you referred to folders instead of tables that all of these records are going into the same table.
here is two options
You query the table for the last record with that prefix and seperate out the prefix from the number portion and add 1 to that number, recombine the prefix and number and use it.
Have a indpendent table with two field
Prefix | CurrentNumber
When you go to add the record you want, determine the prefix to be used, find the last number used in your new table, add 1 to it and create your unique ID and use it, write back to the new table what the last new number used for that prefix was. The advantage of this one is that you can control which number you are going to use next with out any impact or your code
Microsoft ACCESS is a very powerfull tool, but like any tool, it never does everything we want it to do. Fortunately, we are permitted to manipulate Access to do exactly what we want. and moreso through code. There is definately a solution to your quest but the answer can a rather long winded one (like this note) to place into this forum.
sbaxter has it right...and a sample of how it can be accomplished (in the first part) would be the best way to show you how to attack your problem. I have taken the liberty to write a small Access database which can demonstrate to you how to apply a special ID number (ie: SAL0001, ADM0022, etc.) to your documents. It is attached with this note.
Although very basic, here is what it does:
- Permits you to set up 3 letter abbreviations which are bound to specific folders you select via a dialog box. You can have as many abbreviations and folders as you like. Abbreviations are displayed and selected from a combo box located in the main (startup) form.
- Permits you to either enter or select (via dialog) the incoming document from you disk system, enter the date received, the name of the person it is from, and a description of what the document is about.
- Permits you to select where the document is to automatically be stored (via combo box with abbreviations). A special ID code is automatically supplied and the incoming document is named by this code number when stored. Code numbers are auto incremented as well.
- Permits you to view the document in MS Word (if availalbe), or MS WordPad (if available), or MS NotePad (if available), or in a report if none of the aforementioned readers are available.
Although quick and crude, I hope this small db sample assists you in some way....
This function is what generates your so called "special ID":
Public Function PlaceSpecialID(Strg As String) As String
Dim dbs As Database, rst As Recordset
Dim strCriteria As String
' Return reference to current database.
Set dbs = CurrentDb
' Define search criteria.
strCriteria = "[SpecialID] LIKE '" & Me.LocationToPutIt & "*'"
Set rst = dbs.OpenRecordset("Main", dbOpenDynaset)
' Find first matching record.
' Check if record is found.
If rst.NoMatch Then
MsgBox "This category contains no entries yet. we will start one now."
PlaceSpecialID = Strg & "0001"
Dim a As Long
a = CLng(Mid$(rst!SpecialID, 4, Len(rst!SpecialID)))
a = a + 1
PlaceSpecialID = Strg & Format(a, "0000")
Set dbs = Nothing
It is located within the Gerneral section of the "Main" form module.
It's all working well but now I have a follow up question:
How would I alter the code so that in the form's combo box the user can select the full folder name (eg Sales or Personnel) from the list and the SpecialID is created in the same way but using only the first 3 characters of the folder name (eg sal0001 or per0001)?