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.
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 subFunction 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 FunctionASKER
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
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
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.
you can use VBA code to update the recordset.
ASKER
Since I retired 2.5 years ago i have not done any code work, is there any way you can help me with this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for all of your help.
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