Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2013
    Posts
    24

    Unanswered: auto-incrementing numbers with text prefixes in Access 2007

    Hi,

    Firstly let me appologise if this has been asked before (I've tried searching and not yet found anything I can use )

    My boss has asked me to automatically generate a number for leads that get entered into our DB via a form.

    The problem is that these numbers need to be prefixed with either an AG or BG (depending on which of our 2 offices the lead relates to). Selecting A or B is easy enough but what I need help with is some code to find the highest number with the selected prefix and give the new record that number + 1.

    For example you may have a table with these lead numbers:
    AG1
    AG2
    BG1
    BG2
    BG3
    AG3
    AG4
    AG5
    AG6
    AG7
    BG4

    If I were to add a new record relating to the AG office I want the code to see that AG7 is the current highest number and call my new record AG8.
    Once that number has been generated I want it (including the prefix) stored in a field for that record (if possible I'd like this to be the primary key as all values will be unique).

    I hope that makes sense, please ask if you need any more info.

    Many thanks,
    Gav

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    Do you like what is attached?
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2013
    Posts
    24
    Hi Eremija,

    Unfortunately when I try opening that attachment I just get an error saying it might be corrupted. Would you be able to resend it please?

    Thanks,
    Gav

  4. #4
    Join Date
    Nov 2010
    Posts
    84
    Probably you are using Windows WinZip's better to use older versions of unzip program. My colleague @Sinndho spoke about this in a previous post. The sign of my post you link my website and you can download it from the instance.

  5. #5
    Join Date
    Apr 2013
    Posts
    24
    I've got it now thanks.

    That's exactly what I needed and I was using DMax when I had it without the prefix, I can't believe it didn't occur to me to add in the "Office ID =" bit D'oh!

    Thanks very much for your help.
    Gav

  6. #6
    Join Date
    Apr 2013
    Posts
    24
    Hi Eremija,

    Sorry about the gap between posts, I have been out of the office for a while.

    I just wanted to ask for some clarification on the names you have used in the code:
    Code:
    ID = Nz(DMax("[ID]", "Document", "OfficeID = '" & [OfficeID] & "'"), 0) + 1
    DocumentID = Trim(Me.OfficeID) & Trim(Str(Me.ID))
    Please could you just confirm to me which of the names (ID, OfficeID, etc) are variables, which are tables and which are items on the form etc?

    The reason I ask is because I'm trying to substitute my items/tables etc and not sure which goes where.

    Thanks,
    Gav

  7. #7
    Join Date
    Nov 2010
    Posts
    84
    I introduced you to the table Office is an identification label OfficeID
    Table Document is identified DocumentID field to be built in such a way as to select a tag as Office and with the same label in the table document ID field incremental increases. The moment you change the label ID OfficeID counter continues to count on that kind of office.

  8. #8
    Join Date
    Apr 2013
    Posts
    24
    Ok, that's great thank you.

    It turns out that I had mistaken a text box for a label on your form. It works fine now.

    Thanks again,
    Gav

Tags for this Thread

Posting Permissions

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