automating text sequencial numbering

Posted on 2011-05-11
Last Modified: 2012-05-11
In my last question I received an answer that works very well with a text box on a form.
If I add 100 new records to my database, is there a way to automate the adding of the numbers for my text field?
For instance my first number in my text field is 11-394, I am adding records to go to 11-494.
Right now I have to open each record and click into the text field. This works but is very time consuming.
I'm hoping that there is a way to use a query to add these sequencial numbers.
to use the function, you will place something like this, when you add a record using a form

private sub form_current()
if me.newrecord then
end if

end sub

Open in new window

Function getNextNumber() As String
Dim intMaxNumber, curVal, newVal
curVal = Nz(DMax("TextField", "Table1"),0)

If Len(curVal & "") > 0 Then
intMaxNumber = Val(Mid(curVal, 4))

intMaxNumber = Format(CStr(intMaxNumber + 1), "0000")
newVal = Left(curVal, 3) & intMaxNumber
newVal = "11-" & "0001"
End If
getNextNumber = newVal
End Function

Open in new window

Question by:garycoon7
    LVL 119

    Expert Comment

    by:Rey Obrero
    you can use a click event of a button in a form

    Private Sub Command2_Click()
    Dim xNum As Long, j As Long
    xNum = InputBox("Enter Number of records to add")
    For j = 1 To xNum
        CurrentDb.Execute "insert into Table1([TextField]) Values('" & getNextNumber & "')"
    End Sub

    Author Comment

    If I have already added the records and I want to add the sequencial numbers how to I specify where to start the code above? It worked and added the numbers but not with the records that were just added.
    Please see attachment
    Thanks for your patience,
    test_id	case_no	fname	lname
    1	11-0001	gary	coon
    6	11-0002	sam	coons
    7	12-001	bill	jones
    8	12-0002	davey	crockettr
    9	13-0001	sam	fritx=z
    10	13-0002	allison	henery
    11		joe	
    12		billy	
    13		donny	
    14		mary	
    15		sue	
    16	13-0003		
    17	13-0004		
    18	13-0005		
    19	13-0006		
    20	13-0007

    Open in new window

    LVL 119

    Expert Comment

    by:Rey Obrero
    for the records that have other fields already filled, you have to update the case_no field.
    you can use VBA code to update the recordset.


    Author Comment

    Since I retired 2.5 years ago i have not done any code work, is there any way you can help me with this?
    LVL 119

    Accepted Solution

    first you have to delete the records with test_id  16 to 20

    place this codes in a click of button event

    private sub cmdUpdate_click()
    dim rs as dao.recordset
    set rs=currentdb.openrecordset("select * from tablex where case_no is null order by test_id")

    do until rs.eof
         rs!case_no= getNextNumber()

    end sub

    note: you have to add to your references the microsoft dao x.x object library
            from VBA window Tools > references
           look for the microsoft DAO reference and check, then click OK

    Author Closing Comment

    Thank you for all of your help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article describes some very basic things about SQL Server filegroups.
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now