Assign Consecutive Number

  • Thread starter Thread starter SparrowCE
  • Start date Start date
S

SparrowCE

Need to assign/save a consecutive order number based on a
location site ID, then once the number is assigned, form is
opened so user can add further details against that number.
Example, each site has its own 6 digit number prefix,
starting number is 5000, so each new order for each new
site will be Prefix5000, Prefix5001, etc. Main form is a
drill down where user chooses appropriate site, once site
is chosen, filtered form opens assigning the number. I can
get the assign number form to open based on the SiteID
filter, but am unable to get it to automatically populate
SiteID and number prefix assigned to SiteID that would
automatically be combined with next consecutive number.
Once form opens it gets the next number, but the SiteID and
prefix are blank. Am needing them to default to the set
value for the SiteID chosen, then assign the number in
order for more data to be manually entered against it.

New number comes from below, field on form is set at
default to GetNextPackageNo().

Public Function GetNextPackageNo()
'gets next free Package Number

GetNextPackageNo = Nz(DMax("[PackageNo]", "[MasterDetail]")
+ 1, 1)
End Function

Would appreciate any help, thank you.
 
I have a chunk of VBA code I copied and then adapted to do this. You need a
table set up with the following:

tblSystemValues
Fields:
ID - just a number
Type - human readable description - not used in the code
Prefix - the fixed prefix characters you want to add to your numbers, eg IN-
Format - the number format - to set how many digits, eg 0000 for a 4 digit
number
Number - the last running number in the series

Then each record can have an entry for as many running numbers as you want,
I set up for
Parts Quote, Sales order, Invoice etc

Call from wherever you need to generate the next number, eg an Add Record
button:
me.myTextbox = GetNextID(1) for example gets the next number associated with
a Parts Quote

Put the following in a Public module:

Public Function GetNextID(ID As Long) As String
'Pass the ID from call
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Invoice
'returns the next quote/order/invoice number with correct prefix as a string
'prefix, format and current numbers held in the tblSystemValues table

Dim mySQL As String

Dim Getnum As Long
Dim Prefix As String

Dim DigFormat As String




mySQL = "SELECT tblSystemValues.ID, tblSystemValues.Type,
tblSystemValues.Prefix, tblSystemValues.Format, tblSystemValues.Number"
mySQL = mySQL & " FROM tblSystemValues "
mySQL = mySQL & " WHERE (((tblSystemValues.ID)= " & ID & "));"


With CurrentDb.OpenRecordset(mySQL, , dbDenyRead) 'lock recordset for
reading while updating
'this ensures two users cant get the same number




Prefix = !Prefix 'set the quote prefix

DigFormat = !Format 'set the format




.Edit
!Number = !Number + 1 'update to next number
.Update
Getnum = !Number 'and get it


.Close

End With



GetNextID = Prefix & Format(Getnum, DigFormat)


End Function


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

SparrowCE said:
Need to assign/save a consecutive order number based on a
location site ID, then once the number is assigned, form is
opened so user can add further details against that number.
Example, each site has its own 6 digit number prefix,
starting number is 5000, so each new order for each new
site will be Prefix5000, Prefix5001, etc. Main form is a
drill down where user chooses appropriate site, once site
is chosen, filtered form opens assigning the number. I can
get the assign number form to open based on the SiteID
filter, but am unable to get it to automatically populate
SiteID and number prefix assigned to SiteID that would
automatically be combined with next consecutive number.
Once form opens it gets the next number, but the SiteID and
prefix are blank. Am needing them to default to the set
value for the SiteID chosen, then assign the number in
order for more data to be manually entered against it.

New number comes from below, field on form is set at
default to GetNextPackageNo().

Public Function GetNextPackageNo()
'gets next free Package Number

GetNextPackageNo = Nz(DMax("[PackageNo]", "[MasterDetail]")
+ 1, 1)
End Function

Would appreciate any help, thank you.
Click to expand...
 
Thank you very much. I apologize for any confusion, I
accidentally posted this twice yesterday at different
times. My knowledge is very limited when it comes to code,
please bear with me. I've set up my table per below, set
up my prefix data, there are 24 preset prefixes. How do I
use the type and ID fields, what is the data they are
looking for? Also, set up a form to call the code but I
don't have something right on the add record button, not
sure how to set up the call listed below. Am I assuming ok
that the
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Invoice
Click to expand...
listed below would equate to my prefixes?
I appreciate very much your valuable time,thank you.
-----Original Message-----
I have a chunk of VBA code I copied and then adapted to do this. You need a
table set up with the following:

tblSystemValues
Fields:
ID - just a number
Type - human readable description - not used in the code
Prefix - the fixed prefix characters you want to add to your numbers, eg IN-
Format - the number format - to set how many digits, eg 0000 for a 4 digit
number
Number - the last running number in the series

Then each record can have an entry for as many running numbers as you want,
I set up for
Parts Quote, Sales order, Invoice etc

Call from wherever you need to generate the next number, eg an Add Record
button:
me.myTextbox = GetNextID(1) for example gets the next number associated with
a Parts Quote

Put the following in a Public module:

Public Function GetNextID(ID As Long) As String
'Pass the ID from call
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Invoice
'returns the next quote/order/invoice number with correct prefix as a string
'prefix, format and current numbers held in the tblSystemValues table

Dim mySQL As String

Dim Getnum As Long
Dim Prefix As String

Dim DigFormat As String




mySQL = "SELECT tblSystemValues.ID, tblSystemValues.Type,
tblSystemValues.Prefix, tblSystemValues.Format, tblSystemValues.Number"
mySQL = mySQL & " FROM tblSystemValues "
mySQL = mySQL & " WHERE (((tblSystemValues.ID)= " & ID & "));"


With CurrentDb.OpenRecordset(mySQL, , dbDenyRead) 'lock recordset for
reading while updating
'this ensures two users cant get the same number




Prefix = !Prefix 'set the quote prefix

DigFormat = !Format 'set the format




.Edit
!Number = !Number + 1 'update to next number
.Update
Getnum = !Number 'and get it


.Close

End With



GetNextID = Prefix & Format(Getnum, DigFormat)


End Function


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

SparrowCE said:
Need to assign/save a consecutive order number based on a
location site ID, then once the number is assigned, form is
opened so user can add further details against that number.
Example, each site has its own 6 digit number prefix,
starting number is 5000, so each new order for each new
site will be Prefix5000, Prefix5001, etc. Main form is a
drill down where user chooses appropriate site, once site
is chosen, filtered form opens assigning the number. I can
get the assign number form to open based on the SiteID
filter, but am unable to get it to automatically populate
SiteID and number prefix assigned to SiteID that would
automatically be combined with next consecutive number.
Once form opens it gets the next number, but the SiteID and
prefix are blank. Am needing them to default to the set
value for the SiteID chosen, then assign the number in
order for more data to be manually entered against it.

New number comes from below, field on form is set at
default to GetNextPackageNo().

Public Function GetNextPackageNo()
'gets next free Package Number

GetNextPackageNo = Nz(DMax("[PackageNo]", "[MasterDetail]")
+ 1, 1)
End Function

Would appreciate any help, thank you.
Click to expand...


.
Click to expand...
 
Yes, you are nearly there.
I used my names ( from an ordering database ), but you can use anything you
like.
The ID number is just an integer, set by you, to allow you to call the
GetNextID function with a reference number, to get the correct prefix and
formatting. The Type is only for human readable reference, not used within
the code.

So suppose you want the prefix SQQV for your next number, and this is
assigned ID=18, and last used number for this prefix was 44, then setting

myNewNumber = GetNextID(18)

will put the next number SQQV0045 in the variable myNewNumber. ( and leave
the number 45 in the Number field of the table, ready the next for next
call.)

You need to put this code in the Click event of a command button so that it
is run when the button is clicked.

As an aside - I notice you have posted nearly the same question twice, so
you will now get two sets of answers. This will cause both you and other
parties some confusion. This particular question has nearly as many answers
as there are programmers, so there are bound to be other approaches
suggested.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
SparrowCE said:
Thank you very much. I apologize for any confusion, I
accidentally posted this twice yesterday at different
times. My knowledge is very limited when it comes to code,
please bear with me. I've set up my table per below, set
up my prefix data, there are 24 preset prefixes. How do I
use the type and ID fields, what is the data they are
looking for? Also, set up a form to call the code but I
don't have something right on the add record button, not
sure how to set up the call listed below. Am I assuming ok
that the
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Invoice
Click to expand...
listed below would equate to my prefixes?
I appreciate very much your valuable time,thank you.
-----Original Message-----
I have a chunk of VBA code I copied and then adapted to do this. You need a
table set up with the following:

tblSystemValues
Fields:
ID - just a number
Type - human readable description - not used in the code
Prefix - the fixed prefix characters you want to add to your numbers, eg IN-
Format - the number format - to set how many digits, eg 0000 for a 4 digit
number
Number - the last running number in the series

Then each record can have an entry for as many running numbers as you want,
I set up for
Parts Quote, Sales order, Invoice etc

Call from wherever you need to generate the next number, eg an Add Record
button:
me.myTextbox = GetNextID(1) for example gets the next number associated with
a Parts Quote

Put the following in a Public module:

Public Function GetNextID(ID As Long) As String
'Pass the ID from call
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Invoice
'returns the next quote/order/invoice number with correct prefix as a string
'prefix, format and current numbers held in the tblSystemValues table

Dim mySQL As String

Dim Getnum As Long
Dim Prefix As String

Dim DigFormat As String




mySQL = "SELECT tblSystemValues.ID, tblSystemValues.Type,
tblSystemValues.Prefix, tblSystemValues.Format, tblSystemValues.Number"
mySQL = mySQL & " FROM tblSystemValues "
mySQL = mySQL & " WHERE (((tblSystemValues.ID)= " & ID & "));"


With CurrentDb.OpenRecordset(mySQL, , dbDenyRead) 'lock recordset for
reading while updating
'this ensures two users cant get the same number




Prefix = !Prefix 'set the quote prefix

DigFormat = !Format 'set the format




.Edit
!Number = !Number + 1 'update to next number
.Update
Getnum = !Number 'and get it


.Close

End With



GetNextID = Prefix & Format(Getnum, DigFormat)


End Function


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

SparrowCE said:
Need to assign/save a consecutive order number based on a
location site ID, then once the number is assigned, form is
opened so user can add further details against that number.
Example, each site has its own 6 digit number prefix,
starting number is 5000, so each new order for each new
site will be Prefix5000, Prefix5001, etc. Main form is a
drill down where user chooses appropriate site, once site
is chosen, filtered form opens assigning the number. I can
get the assign number form to open based on the SiteID
filter, but am unable to get it to automatically populate
SiteID and number prefix assigned to SiteID that would
automatically be combined with next consecutive number.
Once form opens it gets the next number, but the SiteID and
prefix are blank. Am needing them to default to the set
value for the SiteID chosen, then assign the number in
order for more data to be manually entered against it.

New number comes from below, field on form is set at
default to GetNextPackageNo().

Public Function GetNextPackageNo()
'gets next free Package Number

GetNextPackageNo = Nz(DMax("[PackageNo]", "[MasterDetail]")
+ 1, 1)
End Function

Would appreciate any help, thank you.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top