Need Invoice sequential number series

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box on a
data entry form, to be able to get the InvoiceNo. control to automatically
list the next number in line when the form is opened, or, when a record is
saved and a new record is ready to be entered. The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite right,
particularly he
"InvoiceNo Like 'Invoice*'")

Plus...I am not sure in which Event this should be placed, Before or After
Update.

***********************Start Code**********************
Private Function NextInvoiceNo() As String

Dim strMaxNum As String

strMaxNum = vbNullString & _
DMax("InvoiceNo, tblSalesRecord", _
"InvoiceNo Like 'Invoice*'")

If Len(strMaxNum) = 0 Then
NextInvoiceNo = "000001"
Else
NextInvoiceNo = _
"InvoiceNo" & Format(1 + CLng(Mid(strMaxNum, 7)), "000000")
End If

End Function
*************End Code***************************

I would truly appreciate some suggestions on this process


Jan

On Sat, 4 Mar 2006 19:07:48 -0500, "Jan Il"
wrote:

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box on a
data entry form, to be able to get the InvoiceNo. control to automatically
list the next number in line when the form is opened, or, when a record is
saved and a new record is ready to be entered.

I'd suggest neither one: instead, the best place may be the Form's
BeforeInsert event, which executes as soon as you start entering a new
record (but not when you just open the form to look at existing
records, or when you edit an existing record).

This can be a bit of a problem in a multiuser situation - there are
some pretty much foolproof systems involving a table with the next
avaialable number and VBA code to open the table exclusively, get the
number, increment it and store it back. It's probably not necessary if
you use the code below but it's certainly more robust. It can be found
in Getz and Litwin's _Access version Developer's Handbook_.

The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite right,
particularly he
"InvoiceNo Like 'Invoice*'")

Do your invoices actually start with a text string "Invoice"? and do
you not care which one you're getting???

I'd use a Long Integer InvoiceNo field (you can use the Format
property to display the leading zeros); in the Form's BeforeInsert
event you could put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing value of InvoiceNo in the table
named Invoices; if there are no records NZ() will return 0, otherwise
you'll get the largest. The code will add 1 to that result and store
it in the new record's InvoiceNo (via the textbox txtInvoiceNo bound
to that field). The Me.Dirty = False line will immediately save the
record to disk so another user can't accidentally grab the same number
(this won't work if you have other required fields in the table,
though).

John W. Vinson[MVP]

Hi John :-)

On Sat, 4 Mar 2006 19:07:48 -0500, "Jan Il"
wrote:

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box on
a
data entry form, to be able to get the InvoiceNo. control to
automatically
list the next number in line when the form is opened, or, when a record is
saved and a new record is ready to be entered.

I'd suggest neither one: instead, the best place may be the Form's
BeforeInsert event, which executes as soon as you start entering a new
record (but not when you just open the form to look at existing
records, or when you edit an existing record).

Yes...that sounds more feasible, as there will be times when invoices will
need to be edited. Some invoices for some types of work at some locations
will need to be pre-printed with specific information, then have the rest of
the information added to the invoice when the work is completed. I am hoping
this will not create a problem, as I am not sure how to call the previous
invoice up in the form. Could this be done using the Invoice control as
well? How would it affect the resaving of the invoice number? I have some
idea of how to do this, but, not sure if that would be a good idea, or, if I
should add another control to do the call up. Or, perhaps a button that
will open another form for the editing. Either way, the invoice number will
have to be saved again.

This can be a bit of a problem in a multiuser situation - there are
some pretty much foolproof systems involving a table with the next
avaialable number and VBA code to open the table exclusively, get the
number, increment it and store it back. It's probably not necessary if
you use the code below but it's certainly more robust. It can be found
in Getz and Litwin's _Access version Developer's Handbook_.

I have the 2002 edition. I will check into that as well.

The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite
right,
particularly he
"InvoiceNo Like 'Invoice*'")

Do your invoices actually start with a text string "Invoice"? and do
you not care which one you're getting???

I had thought about it, that is why I used that i the code, but, no...I
really don't want it to start with that, but, wasn't sure if something of
that nature would be needed.

I'd use a Long Integer InvoiceNo field (you can use the Format
property to display the leading zeros); in the Form's BeforeInsert
event you could put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing value of InvoiceNo in the table
named Invoices; if there are no records NZ() will return 0, otherwise
you'll get the largest. The code will add 1 to that result and store
it in the new record's InvoiceNo (via the textbox txtInvoiceNo bound
to that field). The Me.Dirty = False line will immediately save the
record to disk so another user can't accidentally grab the same number
(this won't work if you have other required fields in the table,
though).

Yes, it will be important to secure the new number for that record even
before it is saved. I don't have any other required fields in the table, so
this will likely work out well. I want the numbering to start with 0001001
and then build from there. But, I am concerned that the Invoice number may
get bypassed and nothing entered if the number is not entered automatically,
so I think I need to have that control get the focus first, and then code it
so that the User can't proceed until a number has been added.

I'll test with this and see how I get do with it. Thank you very much for
your time and help, I really do appreciate it. :-)

Jan


John W. Vinson[MVP]

Hi Ken :-)

Jan -

The class module for unique numbers that I'd posted in our private ng does
this sequential number process.

Really? Was this recently? What was the name of the thread? I don't recall
it.

Jan

--

Ken Snell
MS ACCESS MVP




"Jan Il" wrote in message
...
Hi John :-)

On Sat, 4 Mar 2006 19:07:48 -0500, "Jan Il"
wrote:

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box
on a
data entry form, to be able to get the InvoiceNo. control to
automatically
list the next number in line when the form is opened, or, when a record
is
saved and a new record is ready to be entered.

I'd suggest neither one: instead, the best place may be the Form's
BeforeInsert event, which executes as soon as you start entering a new
record (but not when you just open the form to look at existing
records, or when you edit an existing record).

Yes...that sounds more feasible, as there will be times when invoices
will need to be edited. Some invoices for some types of work at some
locations will need to be pre-printed with specific information, then
have the rest of the information added to the invoice when the work is
completed. I am hoping this will not create a problem, as I am not sure
how to call the previous invoice up in the form. Could this be done
using the Invoice control as well? How would it affect the resaving of
the invoice number? I have some idea of how to do this, but, not sure if
that would be a good idea, or, if I should add another control to do the
call up. Or, perhaps a button that will open another form for the
editing. Either way, the invoice number will have to be saved again.

This can be a bit of a problem in a multiuser situation - there are
some pretty much foolproof systems involving a table with the next
avaialable number and VBA code to open the table exclusively, get the
number, increment it and store it back. It's probably not necessary if
you use the code below but it's certainly more robust. It can be found
in Getz and Litwin's _Access version Developer's Handbook_.

I have the 2002 edition. I will check into that as well.

The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound
to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite
right,
particularly he
"InvoiceNo Like 'Invoice*'")

Do your invoices actually start with a text string "Invoice"? and do
you not care which one you're getting???

I had thought about it, that is why I used that i the code, but, no...I
really don't want it to start with that, but, wasn't sure if something of
that nature would be needed.

I'd use a Long Integer InvoiceNo field (you can use the Format
property to display the leading zeros); in the Form's BeforeInsert
event you could put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing value of InvoiceNo in the table
named Invoices; if there are no records NZ() will return 0, otherwise
you'll get the largest. The code will add 1 to that result and store
it in the new record's InvoiceNo (via the textbox txtInvoiceNo bound
to that field). The Me.Dirty = False line will immediately save the
record to disk so another user can't accidentally grab the same number
(this won't work if you have other required fields in the table,
though).

Yes, it will be important to secure the new number for that record even
before it is saved. I don't have any other required fields in the table,
so this will likely work out well. I want the numbering to start with
0001001 and then build from there. But, I am concerned that the Invoice
number may get bypassed and nothing entered if the number is not entered
automatically, so I think I need to have that control get the focus
first, and then code it so that the User can't proceed until a number has
been added.

I'll test with this and see how I get do with it. Thank you very much
for your time and help, I really do appreciate it. :-)

Jan


John W. Vinson[MVP]

Hi John :-)

On Sat, 4 Mar 2006 19:07:48 -0500, "Jan Il"
wrote:

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box on
a
data entry form, to be able to get the InvoiceNo. control to
automatically
list the next number in line when the form is opened, or, when a record is
saved and a new record is ready to be entered.

I'd suggest neither one: instead, the best place may be the Form's
BeforeInsert event, which executes as soon as you start entering a new
record (but not when you just open the form to look at existing
records, or when you edit an existing record).

This can be a bit of a problem in a multiuser situation - there are
some pretty much foolproof systems involving a table with the next
avaialable number and VBA code to open the table exclusively, get the
number, increment it and store it back. It's probably not necessary if
you use the code below but it's certainly more robust. It can be found
in Getz and Litwin's _Access version Developer's Handbook_.

The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite
right,
particularly he
"InvoiceNo Like 'Invoice*'")

Do your invoices actually start with a text string "Invoice"? and do
you not care which one you're getting???

I'd use a Long Integer InvoiceNo field (you can use the Format
property to display the leading zeros); in the Form's BeforeInsert
event you could put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing value of InvoiceNo in the table
named Invoices; if there are no records NZ() will return 0, otherwise
you'll get the largest. The code will add 1 to that result and store
it in the new record's InvoiceNo (via the textbox txtInvoiceNo bound
to that field). The Me.Dirty = False line will immediately save the
record to disk so another user can't accidentally grab the same number
(this won't work if you have other required fields in the table,
though).

Ok...I just tried typing in a new Invoice number and immediately the
debugger threw up a
Runtime error 3078

....he

Me!txtInvoiceNo = Nz(DMax("[InvoiceNo]", "[Invoices]")) + 1

which I rather thought it would. I have made all the other changes but
didn't get far enough along to see it they worked yet. g

Jan

John W. Vinson[MVP]

"Ken Snell (MVP)" wrote in message
...
Jan -

The class module for unique numbers that I'd posted in our private ng does
this sequential number process.

OK...I think I found it.....October 05. Right?

Jan

--

Ken Snell
MS ACCESS MVP




"Jan Il" wrote in message
...
Hi John :-)

On Sat, 4 Mar 2006 19:07:48 -0500, "Jan Il"
wrote:

Hi all :-) Windows XP Pro SP2 - Access 2003

I need to be able to have a sequential numbering system for a text box
on a
data entry form, to be able to get the InvoiceNo. control to
automatically
list the next number in line when the form is opened, or, when a record
is
saved and a new record is ready to be entered.

I'd suggest neither one: instead, the best place may be the Form's
BeforeInsert event, which executes as soon as you start entering a new
record (but not when you just open the form to look at existing
records, or when you edit an existing record).

Yes...that sounds more feasible, as there will be times when invoices
will need to be edited. Some invoices for some types of work at some
locations will need to be pre-printed with specific information, then
have the rest of the information added to the invoice when the work is
completed. I am hoping this will not create a problem, as I am not sure
how to call the previous invoice up in the form. Could this be done
using the Invoice control as well? How would it affect the resaving of
the invoice number? I have some idea of how to do this, but, not sure if
that would be a good idea, or, if I should add another control to do the
call up. Or, perhaps a button that will open another form for the
editing. Either way, the invoice number will have to be saved again.

This can be a bit of a problem in a multiuser situation - there are
some pretty much foolproof systems involving a table with the next
avaialable number and VBA code to open the table exclusively, get the
number, increment it and store it back. It's probably not necessary if
you use the code below but it's certainly more robust. It can be found
in Getz and Litwin's _Access version Developer's Handbook_.

I have the 2002 edition. I will check into that as well.

The new number should based
upon the last number saved or listed in the table, that way each time a
number is saved a new number will automatically be displayed in the
InvoiceNo. box, ready for the next entry.

The Control name for the Invoice number is txtInvoiceNo, which is bound
to
the InvoiceNo field on the tblSalesRecord

I have tested with the following code, but, I don't think it is quite
right,
particularly he
"InvoiceNo Like 'Invoice*'")

Do your invoices actually start with a text string "Invoice"? and do
you not care which one you're getting???

I had thought about it, that is why I used that i the code, but, no...I
really don't want it to start with that, but, wasn't sure if something of
that nature would be needed.

I'd use a Long Integer InvoiceNo field (you can use the Format
property to display the leading zeros); in the Form's BeforeInsert
event you could put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
Me.Dirty = False
End Sub

This will look up the largest existing value of InvoiceNo in the table
named Invoices; if there are no records NZ() will return 0, otherwise
you'll get the largest. The code will add 1 to that result and store
it in the new record's InvoiceNo (via the textbox txtInvoiceNo bound
to that field). The Me.Dirty = False line will immediately save the
record to disk so another user can't accidentally grab the same number
(this won't work if you have other required fields in the table,
though).

Yes, it will be important to secure the new number for that record even
before it is saved. I don't have any other required fields in the table,
so this will likely work out well. I want the numbering to start with
0001001 and then build from there. But, I am concerned that the Invoice
number may get bypassed and nothing entered if the number is not entered
automatically, so I think I need to have that control get the focus
first, and then code it so that the User can't proceed until a number has
been added.

I'll test with this and see how I get do with it. Thank you very much
for your time and help, I really do appreciate it. :-)

Jan


John W. Vinson[MVP]

"Jan Il" wrote in message:
...

The class module for unique numbers that I'd posted in our private ng does this sequential number
process.

OK...I think I found it.....October 05. Right?

Correct.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

Hey Jeff :-)

"Jan Il" wrote in message:
...

The class module for unique numbers that I'd posted in our private ng
does this sequential number process.

OK...I think I found it.....October 05. Right?

Correct.

Got it! I missed it as I was still in San Diego on my way back from the
Summit. No Internet at dad's since I'm not there anymore, so I didn't see
it posted at that time.

Thank you!

Jan

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

On Sat, 4 Mar 2006 21:40:53 -0500, "Jan Il"
wrote:

Ok...I just tried typing in a new Invoice number and immediately the
debugger threw up a
Runtime error 3078

Error message? I don't have all the numbers memorized...

...he

Me!txtInvoiceNo = Nz(DMax("[InvoiceNo]", "[Invoices]")) + 1

which I rather thought it would. I have made all the other changes but
didn't get far enough along to see it they worked yet. g

Does your Form in fact have a control named txtInvoiceNo, bound to the
InvoiceNo field? Is your table in fact named Invoices? Does it contain
a number field named InvoiceNo?

John W. Vinson[MVP]

On Sat, 4 Mar 2006 21:16:06 -0500, "Ken Snell \(MVP\)"
wrote:

The class module for unique numbers that I'd posted in our private ng does
this sequential number process.

Go for it, Jan... that'll be a lot better!

John W. Vinson[MVP]