Record Number Format

  • Thread starter Thread starter vb_Dumb
  • Start date Start date
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
 
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?
 
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
 
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.
 
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

Message posted via AccessMonster.comhttp://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
 
Back
Top