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

x
  • 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
me.TextboxName=getNextNumber()
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
Else
newVal = "11-" & "0001"
End If
getNextNumber = newVal
End Function

Open in new window

0
garycoon7
Asked:
garycoon7
  • 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 & "')"
Next
End Sub
0
 
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,
Gary
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

0
 
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.


0
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?
0
 
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")

rs.movefirst
do until rs.eof
     rs.edit
     rs!case_no= getNextNumber()
     rs.update
rs.movenext
loop
rs.close

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
0
 
garycoon7Author Commented:
Thank you for all of your help.
0

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