Results 1 to 2 of 2

Thread: auto populate

  1. #1
    Join Date
    Feb 2014

    Unanswered: auto populate

    We have 3 plants that use the same access D/B. The plants are 22, 33, 44. We have a generic form that launches (using a simple macro) where operators from each plant can input their samples daily. Each plant has continuous numbers but together they don't........... meaning 22-101, 22-103 but 21-100. so if you were in plant 21 the next number should be 101 and if in plant 22 then its 104.

    Currently the operator determines the next sequential #.
    I would like to automate this so, When a record is first added to this particular database Im working on, I also would like to be able to automatically assign each record with a unique alphanumeric identifier (e.g. 22-104) when a record is first created.

    I would appreciate any suggestions as to how to go about accomplishing this. I would also settle for some alternative in automatically assigning some kind of unique alphanumeric identifier.

    Thanks for the help.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    With the table being named DailySamples and the column containing the plant number being named PlantNumber, you can use the following function:
    Function GetNextNumber(ByVal Plant As Long) As String
        Dim strPlantNumber As String
        strPlantNumber = Nz(DMax("PlantNumber", "DailySamples", "PlantNumber Like '" & Plant & "-*'"), "")
        If Len(strPlantNumber) > 0 Then
            GetNextNumber = Plant & "-" & Val(Mid(strPlantNumber, InStr(strPlantNumber, "-") + 1)) + 1
            ' Not found: Handle error.
        End If
    End Function
    Have a nice day!

Posting Permissions

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