Plus 1 code for field in new record.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I have a field in a table that is set to L-0000. I have over a thousand
records. I want to be able to when adding a new record have this field add 1
to the list (ex. L-1053 next recored L-1054) how can i do this since the
field has text along with numerics?

Thanks
Derek
 
strNext = DMAX("[FieldNameHere]","TableNameHere")
intNext = Cint(Replace(strNext, "L-",""))
strNext=left(strNext, 2) & format(intNext + 1, "0000")
 
Hi I have a field in a table that is set to L-0000. I have over a thousand
records. I want to be able to when adding a new record have this field add 1
to the list (ex. L-1053 next recored L-1054) how can i do this since the
field has text along with numerics?

Thanks
Derek
Click to expand...

If the text is always identically L-, don't store it in the field at
all; you can instead use the Format property of the field to display
it: a format of

"L\-0000"

will do so.

To automatically increment the (number, now) field, you can use the
BeforeInsert event of your form:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtLNumber = NZ(DMax("[LNumber]", "[YourTable]")) + 1
If Me!txtLNumber > 9999 Then
Cancel = True
MsgBox " "Ran out of numbers. Turn off the computer and go home."
End If
End Sub


John W. Vinson[MVP]
 
Great Idea for the message thanks a bunch!!!!
Derek

John Vinson said:
Hi I have a field in a table that is set to L-0000. I have over a thousand
records. I want to be able to when adding a new record have this field add 1
to the list (ex. L-1053 next recored L-1054) how can i do this since the
field has text along with numerics?

Thanks
Derek
Click to expand...

If the text is always identically L-, don't store it in the field at
all; you can instead use the Format property of the field to display
it: a format of

"L\-0000"

will do so.

To automatically increment the (number, now) field, you can use the
BeforeInsert event of your form:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtLNumber = NZ(DMax("[LNumber]", "[YourTable]")) + 1
If Me!txtLNumber > 9999 Then
Cancel = True
MsgBox " "Ran out of numbers. Turn off the computer and go home."
End If
End Sub


John W. Vinson[MVP]
Click to expand...
 
Back
Top