Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

automating text sequencial numbering

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

  • 3
  • 3
1 Solution
Rey Obrero (Capricorn1)Commented:
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
garycoon7Author Commented:
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

Rey Obrero (Capricorn1)Commented:
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

garycoon7Author Commented:
Since I retired 2.5 years ago i have not done any code work, is there any way you can help me with this?
Rey Obrero (Capricorn1)Commented:
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
garycoon7Author Commented:
Thank you for all of your help.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now