Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Angry Unanswered: unique reference numbers

    Dear All,

    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?

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    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.

    OR

    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

    S-

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    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....
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2003
    Posts
    6
    Many thanks for the info, and the sample DB.

    Actually I probably didn't explain myself too well and the DB does rather more than I intended. Most of the correspondence is hard copy and filed in paper folders.

    So the main Access issue for me is just the unique reference and not the filing. I'm new to coding and struggling to understand it for the form MAIN in the sample.

    what is the relevant part of the coding and how do I use it in my own DB? Sorry, i know this is a rather basic question.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    This function is what generates your so called "special ID":

    Code:
    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.
        rst.FindLast strCriteria
        ' Check if record is found.
        If rst.NoMatch Then
            MsgBox "This category contains no entries yet. we will start one now."
            PlaceSpecialID = Strg & "0001"
        Else
            Dim a As Long
            a = CLng(Mid$(rst!SpecialID, 4, Len(rst!SpecialID)))
            a = a + 1
            PlaceSpecialID = Strg & Format(a, "0000")
        End If
        rst.Close
        Set dbs = Nothing
    End Function
    It is located within the Gerneral section of the "Main" form module.

  6. #6
    Join Date
    Nov 2003
    Posts
    6
    Thanks again CyberLynx,

    I've been looking through the code and I think I'm beginning to get the gist of it now and separate out the filename stuff from the specialID stuff

    It seems that as I'm using Access 2000 I also have to change the references to include DAO 3.6. Does that sound right?

    Regards

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    I don't think you have to change anything. Not sure though.

  8. #8
    Join Date
    Nov 2003
    Posts
    267
    If you are using Access 2000 and DAO

    You need to dim your recordset like:

    Dim rst As DAO.Recordset

    Or you will get an error.



    And you should include a reference to the most current DAO (which is 3.6) that you can run from your system, and others that you plan on distributing it to.
    S-

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    There ya go

  10. #10
    Join Date
    Nov 2003
    Posts
    6

    Wink

    Thanks again folks.

    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)?

    I'd be grateful for any pointers.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •