Sequential numbered fields

S. McCann

Registered User.
Local time
Today, 02:28
Joined
Nov 23, 2001
Messages
10
I have a field called "Sample_ID" within a form. For each new record I enter I want to copy the previous "sample_id" value and add 1 automatically.
Example:

samp_id
565
566
567
568
569
Any suggestions are welcome.
 
First Suggestion:
Make "Sample_ID" an AutoNumber Field
This will do exactly what you want (no fuss no muss)

Alt Suggestion:
Use the BeforeUpdate Event use a function that returns that last "Sample_ID" used and Add 1 to get the value of "Sample_ID"

For both suggestion I recommend Locking the "Sample_ID" field to avoid user entry errors.
 
THanks Travis.
smile.gif
 
Travis...The autonumber is not an option for me as my "sample_id" field often contains text as part of the value. I had minor success with the "afterupdate" function and adding 1 to the previous record but get stymied with the text characters in the field.
Example (record 1):
Samp_id
GH184-1
GH184-2
GH185-3

Example (record 2):
Samp_id
PR23-1
PR23-2
PR23-3

Example (record 3:
Samp_id
17231
17232
17233

With those above examples what kind of code do I need to get the "samp_id" to automatically increment by 1?
 
You will need to Isolate the Incrementing Number:

Example:
Use "Instr" to find the "-" and use "Mid" to get the value and "Left" to get the Static characters

X=Instr(1,"GH184-1","-") '= 6
sStatic=Left("GH184-1",X)'="GH184-"
nIncr=Mid("GH184-1",X+1) '= 1
 
Travis....
I appreciate your help immensly, obviously Iam newbie to VB! I have included my code below. I keep getting error message "varible not defined". What am I doing wrong?

Private Sub samp_id_AfterUpdate()
If Not IsNull(Me.samp_id) Then
Me.samp_id.DefaultValue = "=" & x
Dim MyVar
MyVar = x
x = InStr(1, "GH184-1", "-") '= 6
sStatic = Left("GH184-1", x) '="GH184-"
nIncr = Mid("GH184-1", x + 1) '= 1

End If

End Sub
 
Private Sub samp_id_AfterUpdate()
Dim MyVar as Variant
Dim X as Integer
Dim sStatic as String
Dim nIncr as Integer

If Not IsNull(Me.samp_id) Then
MyVal = Me.samp_id
'Gets the Location of the "-" Character
X = InStr(1, MyVal, "-")
'Gets the First Half of the ID
sStatic = Left(MyVal, X)
'Gets the Incrementing Half of the ID
nIncr = Val(Mid(MyVal, X + 1))
'Adds One
nIncr = nIncr + 1
'Sets the DefaultValue so on the next entry
'The next value will be set.
Me.samp_id.DefaultValue = "=" & sStatic & nIncr
End If

End Sub
 
Travis...
still no luck. I changed the MyVal in the code you sent to MyVar. Otherwise an "variable not defined" error came up. In the samp_id field the code will add 1 to a numeric previosly entered value, but it will only do it for one record. If I enter a value such as GH184-1 then the code will enter #name?
 

Users who are viewing this thread

Back
Top Bottom