Need Invoice sequential number series

[Jan Il]

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 here:
"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

 

[John Vinson]

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

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 here:
"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]

 

[Jan Il]

Hi John

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.

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

 

[Ken Snell \(MVP\)]

Jan -

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

Ken Snell
<MS ACCESS MVP>

Hi John

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.

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]

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>

Hi John

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

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 here:
"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]

Hi John

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

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 here:
"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

....here:

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

 

[Jan Il]

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>

Hi John

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

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 here:
"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]

 

[Jeff Conrad]

in message:

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

Correct.

 

[Jan Il]

Hey Jeff

in message:



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

 

[John Vinson]

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...

...here:

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]

 

[John Vinson]

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]

 

[Jeff Conrad]

in message:

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.

If you had come to breakfast one morning with us at the Summit, you
could have seen Ken demonstrating this technique in action.

Bacon, eggs, and an Access demo by Ken: The Breakfast of Champions!

 

[Jan Il]

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

Gashp! But...but.....after all this time? I was sure all Access MVPs have
those little error books that MS gives you along with the one for all the
different languages. Right? ;o)

Just kidding....sorry I forgot to add it...here is the wording:

"The Microsoft Jet database engine cannot find imput table or query
"Invoice". Make sure it exists and that its name is spelled correctly"

...here:

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?

Table name is tblSalesRecord
Control name is txtInvoiceNo
Table field name is InvoiceNo

Thank you!

Jan

 

[Jan Il]

in message:


If you had come to breakfast one morning with us at the Summit, you
could have seen Ken demonstrating this technique in action.

Bacon, eggs, and an Access demo by Ken: The Breakfast of Champions!

Heh! I was lucky that I managed to get together with you guys for dinner
the very last evening! Dirk and I had been trying to meet in person from
the first day, after all the time we have worked together via emails, and
kept missing each other. ;o)

Jan

 

[Ken Snell \(MVP\)]

If you had come to breakfast one morning with us at the Summit, you
could have seen Ken demonstrating this technique in action.

Bacon, eggs, and an Access demo by Ken: The Breakfast of Champions!

< BG >

 

[Ken Snell \(MVP\)]

Sounds about right....

--

Ken Snell
<MS ACCESS MVP>

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>

Hi John

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

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 here:
"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]

Sounds about right....

Thanks, Ken. And it's got lots of other goodies too! )

Jan

--

Ken Snell
<MS ACCESS MVP>

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>




Hi John

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

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 here:
"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]

 

[Brian Bastl]

Hi Jan,

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

Brian

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

Gashp! But...but.....after all this time? I was sure all Access MVPs have
those little error books that MS gives you along with the one for all the
different languages. Right? ;o)

Just kidding....sorry I forgot to add it...here is the wording:

"The Microsoft Jet database engine cannot find imput table or query
"Invoice". Make sure it exists and that its name is spelled correctly"

...here:

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?

Table name is tblSalesRecord
Control name is txtInvoiceNo
Table field name is InvoiceNo

Thank you!

Jan

John W. Vinson[MVP]

 

[Jan Il]

Hi Brian!

Hi Jan,

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

Brian

Thank you very much for the additional information. I really appreciate it.
You can never have too much of that. <g>

Jan

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

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...

Gashp! But...but.....after all this time? I was sure all Access MVPs have
those little error books that MS gives you along with the one for all the
different languages. Right? ;o)

Just kidding....sorry I forgot to add it...here is the wording:

"The Microsoft Jet database engine cannot find imput table or query
"Invoice". Make sure it exists and that its name is spelled correctly"

...here:

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?

Table name is tblSalesRecord
Control name is txtInvoiceNo
Table field name is InvoiceNo

Thank you!

Jan

John W. Vinson[MVP]

 

[Ken Snell \(MVP\)]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thanks, Ken. And it's got lots of other goodies too! )

Jan