Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2013
    Posts
    7

    Unanswered: Add an auto incrementing number to the end of code

    I have this code already

    Code:
    'Create RecordNum using current date
    
    'set variables to use in future operations
    
        Dim sMonth As String
    
        Dim sDay As String
    
        Dim sYear As String
    
        'trim clears all spaces and other symbols like  / in 04/16/2013 
    
        sMonth = Trim(Str(Month(Date)))
    
        sDay = Trim(Str(Day(Date)))
    
        sYear = Trim(Str(Year(Date)))
    
       
    
        If sMonth < 10 Then
    
        sMonth = "0" & sMonth
    
        End If
    
     
    
        'assign the textbox value to a concatenation of the variables
    
        Me.txtRecordNumber.Value = sMonth + sDay + sYear
    And it does exactly what I need it to do. But I would like to add 3 numbers to the end of the output. They need to start at 001 and increase by one each time I click the button I have this code assigned to. In addition, they need to reset to 001 everytime the date changes. Can you help me with this?

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Apr 2013
    Posts
    7

    This worked wonderfully! but...

    I was playing around with this, and I need the format to be "mmddyy" and when I change this in the code, the leading zero on today's date (i.e. 042213) gets dropped and I cannot seem to get it to stay. Any thoughts?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Don't use the "mmddyy" format, use "yymmdd" instead, this will allow you to sort chronologically, which would be impossible with "mmddyy".

    And also, instead of:
    Code:
        Dim sMonth As String
        Dim sDay As String
        Dim sYear As String
    
        'trim clears all spaces and other symbols like  / in 04/16/2013 
        sMonth = Trim(Str(Month(Date)))
        sDay = Trim(Str(Day(Date)))
        sYear = Trim(Str(Year(Date)))
        If sMonth < 10 Then
            sMonth = "0" & sMonth
        End If
        'assign the textbox value to a concatenation of the variables
        Me.txtRecordNumber.Value = sMonth + sDay + sYear
    simply use:
    Code:
    Me.txtRecordNumber.Value = Format(Date, "mmddyy")
    or (if you decide to change the format):
    Code:
    Me.txtRecordNumber.Value = Format(Date, "yymmdd")
    Have a nice day!

  5. #5
    Join Date
    Apr 2013
    Posts
    7

    One more question

    How can I have the application ask the user "How many records do you want to generate?" and then have the user input a number, say 150, and then have access create 150 records with just the PK field filled in so the user can go back and fill in the blank fields when they have time. The thing is the records have to have a specific PK that identifies them as having been generated on a specific day and if we can just generate a specified number of records it would make it way easier down the road. It may break some normalization rules but its the best solution I can come up with.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a possibility (although I would never create an application using this kind of mechanism). It can only work if the date part of the Id is in "yymmdd" (or "yyyymmdd")format. In the example, MyTable is the name of the table and RowId is the name of the Primary Key column. It is defined as Text, which is not optimal but prevents overflows to occur.
    Code:
    Sub CreateRows()
    
        Const c_SQL As String = "INSERT INTO MyTable ( RowID ) VALUES ( '@I' );"
        
        Dim strStart As String
        Dim strDate As String
        Dim lngStart As Long
        Dim lngNumber As Long
        Dim i As Long
        
        lngNumber = InputBox("Number of row(s) to be created:", "Create new row(s)", 1)
        If lngNumber > 0 Then
            strStart = Nz(DMax("RowID", "MyTable"), "")
            If Len(strStart) > 0 Then
                strDate = Left(strStart, 6)
                If strDate = Format(Date, "yymmdd") Then  ' Row(s) already exist for today.
                    lngStart = CLng(Mid(strStart, 7))
                    lngNumber = lngNumber + lngStart
                    lngStart = lngStart + 1
                End If
            End If
            For i = lngStart To lngNumber
                CurrentDb.Execute Replace(c_SQL, "@I", Format(Date, "yymmdd") & Format(i, "000")), dbFailOnError
            Next i
        End If
        
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Apr 2013
    Posts
    7
    Okay, I have another idea. Here is the code I have that creates just what I want using today's date. I want to change it so it creates a number using a number input by a user. So basically I have a form that the user places a number into, and then I want the first six digits of the automatically created number to be that number they input. So I really just want to replace the "date" part of the following code with the value from the form. Here's my code

    Code:
    Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
    Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String
    
    'get the highest existing value from the table
    dmval = Nz(DMax(strField, strTable), 0)
    
    'if returned value is 0 then the table is new and empty
    'create autonumber with current date and sequence 001
    If Val(dmval) = 0 Then
       dv = Format(Now(), mmddyy) * 1000 + 1
       Autonum = dv
       Exit Function
    End If
    
    'format the number as an 9 digit number
    dv = Format(dmval, "000000000")
    'take the 3 digit sequence number separately
    Seq = Val(Right(dv, 3))
    'take the date value separately
    dt1 = Left(dv, 6)
    'get today's date
    dt2 = Format(Now(), "mmddyy")
    'compare the latest date taken from the table
    'with today's date
    If dt1 = dt2 Then 'if both dates are same
       Seq = Seq + 1 'increment the sequence number
       'add the sequence number to the date and return
       Autonum = Format(Val(dt1) * 1000 + Seq)
       Exit Function
    Else 'the dates are different
       'take today's date and start the sequence with 1
       Autonum = Format(Val(dt2) * 1000 + 1)
    End If
    
    End Function
    Any thoughts?

Posting Permissions

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