Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    17

    Unanswered: Generate bob name

    Hello

    I have a database that we register samples in. We put the samples in boxes with space for 49 pcs. (A01 - G07) per box.
    I have made a function to write the same box name, but the users have to put in the name of the box every time the function runs. Is it possible to get access to change the name such as RNA1 first time, run the function, and then on the next run generate the box name RNA2.

    Geir Arne

  2. #2
    Join Date
    Nov 2011
    Posts
    413

    Generate bob name

    Yes, you can do this. Attached is an example of how to do it.
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you probably need to write a fucntion that gets the next available value
    the following is untested and so may not work for you you without tweaking
    Code:
    Public Function GetNextCellID(Optional LastCellID As String = "A00") As String
    'function to return a specific id given the optional LastID
    'the requirememnt is for a two part nujber to be returned int he form X99, where
    'X is an alpha in the range A to G
    '99 is a numeric indein the range 01 to 07
    Dim Prefix As Long 'the alpha component represented as its ASCII value
    Dim ID As Integer 'the index
    'OK so lets do some validations
    If Len(LastCellID) <> 3 Then 'supplied parameter must be 3 characters in length
        LastCellID = "A00"
    Else 'so the parameter is 3 characters long, is it the correct format
        ' first character MUST be in range A to G and characters 2& 3 MUST be numeric
        'we aren't over worried about 'performance' so convert to the Ascii value
        Prefix = Asc(Left(LastCellID, 1))
        If Prefix < 65 Or Prefix > 71 And IsNumeric(Mid(LastCellID, 2, 2)) Then 'Ascii 65 = A, Ascii 71 = G
            'its crap data so reset to A1
            Prefix = 65
            ID = 1
        Else 'OK so the data is theoretically valid, lets do a range check
            ID = CInt(Mid(LastCellID, 2, 2))
            If ID < 0 Then ID = 1 '
            If ID >= 7 Then 'if the ID is at or above 7 then reset to one
                ID = 1
                If Prefix = 71 Then 'if the current prefix is G then reset to A otherwise set it to prefix +1
                    Prefix = 65
                Else
                    Prefix = Prefix + 1
                End If
            Else 'otherwise just add one ot the current value
                ID = ID + 1
            End If
        End If
    End If
    GetNextCellID = Chr(Prefix) & Format(ID, "00") 'recreate the cell id
    End Function
    usage is
    =GetNextCellID(LastCellID)
    or for the first time just call it as GetNextCellID
    and then return the value as the parameter next time round
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2011
    Posts
    17
    I think you misunderstand what I mean. Here is the code that I use:

    Public Function FillBox(strBoxName As String)

    Dim intNum As Integer
    Dim intASC As Integer
    Dim strLetter As String
    Dim strPos As String
    Dim strSQL As String

    For intASC = 65 To 71
    strLetter = Chr(intASC)
    For intNum = 1 To 7
    strPos = strLetter & Format(intNum, "00")
    strSQL = "INSERT INTO SampleLocations(BoxName,Position) " & _
    "VALUES(""" & strBoxName & """,""" & strPos & """)"
    CurrentDb.Execute strSQL, dbFailOnError
    Next intNum
    Next intASC

    End Function

    Private Sub cmd_FillBox_Click()
    Call FillBox("RNA1")
    End Sub

    What I want is that when user press next time on the command button
    , Access change the box name +1

    Is this possible, if so how?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Geir Arne View Post
    I think you misunderstand what I mean.
    don't think so, seems quite clear, you want a mechanism that will allocate the next available cell number in range A01..A07, B01..B07....G07

    Quote Originally Posted by Geir Arne View Post
    What I want is that when user press next time on the command button
    , Access change the box name +1

    Is this possible, if so how?
    by using the function supplied above
    supply the previous allocated value as the parameter and you will get the next suitable value, or A01 if the parameter isn't supplied or is invalid
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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