Need Invoice sequential number series

"Jan Il" wrote 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!
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

"John Vinson" wrote in message
...
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...

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"

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

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

Thank you!

Jan


John W. Vinson[MVP]

"Jeff Conrad" wrote in message
...
"Jan Il" wrote 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!

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

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

"Ken Snell (MVP)" wrote in message
...
Sounds about right....

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

Jan


--

Ken Snell
MS ACCESS MVP

"Jan Il" wrote in message
...
"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]

Hi Jan,

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

Brian


"Jan Il" wrote in message
...
"John Vinson" wrote in message
...
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...

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"

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

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

Thank you!

Jan


John W. Vinson[MVP]

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


"Jan Il" wrote in message
...
"John Vinson" wrote in message
...
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...

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"

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

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

Thank you!

Jan


John W. Vinson[MVP]