none
auto generating numbers based on a number of criteria RRS feed

  • Question

  • I have zero VBA knowledge and I need to complete a very specific task in an Access database. I need to write code that will automatically generate a unique ID number for each subject who has met three criteria (in the form of check boxes). I cannot use an Autonumber because we I am already using autonumber for a different variable in the database. The ID numbers need to start with the number 8530 and continue up by 1 (with no duplicates or skips). Any thoughts would be really really helpful, thanks!
    Friday, July 22, 2011 2:24 PM

All replies

  • There are 2 ways to do it.  You will need to set the first one to 8530

    1. Do a query on the table like this

    Select max(uniqueID) + 1 as nextid from table1

    When you insert the record insert nextid into uniqueID.

    2.  The other way is to create a table with one column and use a similar approach to #1.

    I use #1 most of the time since users are creating limited number of records daily and it is easier to keep track of.  You can index uniqueID to make the max function faster.

     

    Friday, July 22, 2011 11:26 PM
  • Hi Gceps

    gcpeds wrote:

    I have zero VBA knowledge and I need to complete a very specific task in
    an Access database. I need to write code that will automatically generate
    a unique ID number for each subject who has met three criteria (in the
    form of check boxes). I cannot use an Autonumber because we I am already
    using autonumber for a different variable in the database. The ID numbers
    need to start with the number 8530 and continue up by 1 (with no
    duplicates or skips). Any thoughts would be really really helpful,

    Sounds like a strange db design...

    anyway: You could use a function to generate this number:

    Public Function getUniqueNumber() As Long
     getUniqueNumber = Nz(DMax("YourNumberField", "YourTable"), 8529) + 1
    End Function

    place this function in a standard modul and adapt it to your table/fieldnames.
    Then you may call it when ever you want to get the next number. It will return you the same number until the recordset is written to the database and visible. Be careful with it in a multiuser environment, call it in such an environment in the BeforeUpdate event of a bound form in such a way (as last statement):

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ...
     If IsNull(Me!YourNumberField) Then
       Me!YourNumberField = getUniqueNumber()
     End If
    End Sub

    HTH
    Henry

    Monday, July 25, 2011 7:15 AM