Record Number Format

V

vb_Dumb

I was wondering if anyone knew how to create a record number that
would automatically change one increment at a time but in a weird
format like this:
ETP000001
ETP000002
ETP000003...... ect Just a thought would I like to have that instead
of
a autonumber because of skipping numbers
 
B

Beetle

You asked this question on Friday an got three answers from three
very knowledgable people. Did you not see the answers, or perhaps
not understand them?
 
V

vb_Dumb

You asked this question on Friday an got three answers from three
very knowledgable people. Did you not see the answers, or perhaps
not understand them?
Click to expand...

didnt understand and i think i miss worded the question and did not
get a solution that would work well it was my mistake
 
B

Beetle

The method that was suggested by the responses to your other post
is actually the best method for doing what you want. You store the text
and numeric portions in separate fields an then concantenate them
together for display purposes.
 
L

Linq Adams via AccessMonster.com

Searching here on your user name and also on ETP000001 doesn't bring up your
other post, so I don't know what has already been offered, but here's a hack
I use for this sort of thing, modified for your prefix of ETP.

Where

txtAINumber

is the texbox on your form holding the auto-incremented numer

AINumber

is the field in your table that holds the number, and

TableName

is the actual name of your table:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.txtAINumber = "ETP000001"
Else
Me.txtAINumber = "ETP" & Format(DMax("val(Right([AINumber],6))",
"TableName") + 1, "000000")
End If
End If
End Sub
 
V

vb_Dumb

Searching here on your user name and also on ETP000001 doesn't bring up your
other post, so I don't know what has already been offered, but here's a hack
I use for this sort of thing, modified for your prefix of ETP.

Where

txtAINumber

is the texbox on your form holding the auto-incremented numer

AINumber

is the field in your table that holds the number, and

TableName

is the actual name of your table:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.txtAINumber = "ETP000001"
 Else
  Me.txtAINumber = "ETP" & Format(DMax("val(Right([AINumber],6))",
"TableName") + 1, "000000")
 End If
End If
End Sub

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200811/1
Click to expand...

I tried your method Linq but it stops and says compile error Expected:
line number or label or statement or end of statement here is the code
(* is where it stops)

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.DMR_Number = "ETP000001"
Else
* Me.DMR_Number = "ETP" & Format(DMax("val(Right([AINumberETP],6))",
"ETP_Table") + 1, "000000") *
End If
End If
End Sub
 
Top