Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006

    generating random numbers on form


    I am working on a project (due today) where we have a form that populates the orders table. They now want the order number field(O_Num) to be randomly generated.This also happens to be the primary key the number should start with a T or G, then generate a 4-5 digit number. We already have an autogenerate number on this table so it wont let me use that setting. Any suggestions would be GREATLY appreciated.

  2. #2
    StepUP is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    Dec 2005
    Here is some code you can try. Just remember that if you are in a multi-user environment, you will have to check if the routine is in process and then put in a wait loop until the current process is completed:

    Function IncAlphaNumValue(OriginalValue As String)

    'Assumes AlphaNumeric Input
    'Assumes Not 0 Length

    Dim Counter As Integer 'Loop Control
    Dim Place As Integer
    Dim ValueLength As Integer 'Length Of Value
    Dim ValueCharArray() As String * 1 'Array To Hold Value as Characters
    Dim CheckChar As String * 1 'Character Being Tested
    Dim CarryFlag As Integer '
    Dim NewValue As String '

    CarryFlag = False
    NewValue = ""
    OriginalValue = UCase(OriginalValue)

    'Determine length of value in question and prepare an array to hold that many characters.

    ValueLength = Len(OriginalValue)
    ReDim ValueCharArray(1 To ValueLength)

    'Break old value into an array of characters

    For Counter = 1 To ValueLength
    ValueCharArray(Counter) = MID$(OriginalValue, Counter, 1)
    Next Counter

    Place = ValueLength
    CheckChar = ValueCharArray(Place)
    IncCharacter CheckChar, CarryFlag
    ValueCharArray(Place) = CheckChar
    Place = Place - 1
    Loop Until Not (CarryFlag) Or Place = 0

    'Compile New Value

    For Counter = 1 To ValueLength
    NewValue = NewValue & ValueCharArray(Counter)
    Next Counter
    IncAlphaNumValue = NewValue

    End Function

    Sub IncCharacter(Character As String, Carry As Integer)

    Carry = False
    If Character = "Z" Then
    Carry = True
    Character = "0"
    ElseIf Character = "9" Then
    Carry = False
    Character = "A"
    Character = Chr$((Asc(Character) + 1))
    If Character = "O" Then Character = "P"
    If Character = "I" Then Character = "J"
    Carry = False
    End If

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. noob prob, generating next report
    By flash319 in forum Reports
    Replies: 2
    Last Post: 08-04-2008, 03:10 PM
  2. random record
    By wasim_sono in forum Programming
    Replies: 1
    Last Post: 02-06-2008, 02:42 PM
  3. Insert Dash between set of Numbers
    By Jhankish in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 08:02 AM
  4. Replies: 2
    Last Post: 09-01-2006, 04:03 PM
  5. When Week Numbers Collide!
    By Schwagr in forum Queries
    Replies: 3
    Last Post: 03-18-2006, 10:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums