Auto generate a label/number

  • Thread starter Thread starter RA
  • Start date Start date
R

RA

I would like to track orders and have it assign a new, consecutive order
number to each new record. Example we have order MCO25, the next new record
I would like to have it auto generate MCO26
 
hi,
I would like to track orders and have it assign a new, consecutive order
number to each new record. Example we have order MCO25, the next new record
I would like to have it auto generate MCO26
Click to expand...
The 1000th order would be MCO1000?

Try this:

Public Funtion NextOrderNo() As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([OrderNo], 4))) " & _
"FROM [yourTable]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function



mfG
--> stefan <--
 
Thanks, but where does this go?

Stefan Hoffmann said:
hi,
I would like to track orders and have it assign a new, consecutive order
number to each new record. Example we have order MCO25, the next new record
I would like to have it auto generate MCO26
Click to expand...
The 1000th order would be MCO1000?

Try this:

Public Funtion NextOrderNo() As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([OrderNo], 4))) " & _
"FROM [yourTable]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function



mfG
--> stefan <--
Click to expand...
 
I'm afraid like so many others here I don't know code. Can you indicate what
aret he things I need to modify and the format?

RA said:
Thanks, but where does this go?

Stefan Hoffmann said:
hi,
I would like to track orders and have it assign a new, consecutive order
number to each new record. Example we have order MCO25, the next new record
I would like to have it auto generate MCO26
Click to expand...
The 1000th order would be MCO1000?

Try this:

Public Funtion NextOrderNo() As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([OrderNo], 4))) " & _
"FROM [yourTable]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function



mfG
--> stefan <--
Click to expand...
Click to expand...
 
hi,
I'm afraid like so many others here I don't know code. Can you indicate what
aret he things I need to modify and the format?
Click to expand...
Go to the database window, select the module pane. Create a new standard
module. Paste the code into it. Run Compile in the Debug menu, this
should happen without error message.

Go to your form, open the property editor, select the event page. Create
a event procedure After Insert:

Private Sub Form_AfterInsert()

Me![yourField] = NextOrderNo()

End Sub



mfG
--> stefan <--
 
"SELECT Max(CLng(Mid([OrderNo], 4)))

Invalid outside procedure

Stefan Hoffmann said:
hi,
I'm afraid like so many others here I don't know code. Can you indicate what
aret he things I need to modify and the format?
Click to expand...
Go to the database window, select the module pane. Create a new standard
module. Paste the code into it. Run Compile in the Debug menu, this
should happen without error message.

Go to your form, open the property editor, select the event page. Create
a event procedure After Insert:

Private Sub Form_AfterInsert()

Me![yourField] = NextOrderNo()

End Sub



mfG
--> stefan <--
Click to expand...
 
RA said:
"SELECT Max(CLng(Mid([OrderNo], 4)))

Invalid outside procedure
Click to expand...
Have you copied the entire line? The ampersand and underscore are necessary.


mfG
--> stefan <--
 
first I posted just as you posted:

Option Compare Database ******(added by new module)*******

Public Funtion NextOrderNo() As String *****(is red and expected end of
statemant error)*****

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

strSQL = "SELECT Max(CLng(Mid([OrderNo], 4))) " & _ ******(if I
remove earlier error this becomes the error mentioned in the previous
reply)*******
"FROM [yourTable]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

NextOrderNo = "MCO" & rs.Fields.Item(0).Value + 1

rs.Close
Set rs = Nothing
Set db = Nothing

End Function


Stefan Hoffmann said:
RA said:
"SELECT Max(CLng(Mid([OrderNo], 4)))

Invalid outside procedure
Click to expand...
Have you copied the entire line? The ampersand and underscore are necessary.


mfG
--> stefan <--
Click to expand...
 
hi,
first I posted just as you posted:

Option Compare Database ******(added by new module)*******

Public Funtion NextOrderNo() As String *****(is red and expected end of
statemant error)*****
Click to expand...
Don't copy typos :) There is a 'c' missing, it must be 'Function'.


mfG
--> stefan <--
 
hi,
Dim db As DAO.Database *****User-defined type not defined*******
Click to expand...
Take a look at Tools/References. There must be an active reference to
Microsoft DAO Library.

mfG
--> stefan <--
 
Hey we're getting closer.

NextOrderNo = ***Compile Error: function call on left-hand side of
assignemtn must return Variant or Object****
 
Getting closer:

NextOrderNo =

***Compile error: Function call on left-handed side of assignment must
return Variant or Object*****
 
Got it, put the event in Before insert, as soon as you type anything in the
information the order number pops up.

Thanks again
 
Crap, I thought I was through. Putting the information into my larger table
I started getting a new error:

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

***Run-Time Error '306' Too Few Paremeters expected 1***
 
SOLVED - I had two ticket names and it got confused. I cahnged them all to
the same name and thing work great.

Thanks
 
Back
Top