Help:creating a new invoice?

Hi there i have a little problem with my invoice.

Here it is:

i have a main menu with buttons, one of my button is "Create new
invoice", when click on it a form pop up so i can enter my information
and one of the field (the user cannot change the info in it.) is
invoice #. Right now, everythime i click on "Create new invoice", the
invoice # add 1. But my problem is sometime the employee dont fill it
(for x reason) and shut it down, the nest time they will click on
"Create new invoice" the invoice # will be 1 more then the last one.
There will be a blank invoice between them.

Is there a way to get rid of this??

thx a lot.

re: Help:creating a new invoice?

"NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> wrote in message
news:1130963097.982891.141760@z14g2000cwz.googlegr oups.com...[color=blue]
> Hi there i have a little problem with my invoice.
>
> Here it is:
>
> i have a main menu with buttons, one of my button is "Create new
> invoice", when click on it a form pop up so i can enter my information
> and one of the field (the user cannot change the info in it.) is
> invoice #. Right now, everythime i click on "Create new invoice", the
> invoice # add 1. But my problem is sometime the employee dont fill it
> (for x reason) and shut it down, the nest time they will click on
> "Create new invoice" the invoice # will be 1 more then the last one.
> There will be a blank invoice between them.
>
> Is there a way to get rid of this??
>
> thx a lot.
>[/color]
Have the employee supply required information before creating a new invoice
number.

re: Help:creating a new invoice?

how do i do that? any hint?

re: Help:creating a new invoice?

have the button that creates the invoice check that all required fields
are filled in first.

re: Help:creating a new invoice?

have the button that creates the invoice check that all required fields
are filled in first - before allowing an invoice to be created at all.

re: Help:creating a new invoice?

In message <1130963097.982891.141760@z14g2000cwz.googlegroups .com>,
"NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> writes[color=blue]
>Hi there i have a little problem with my invoice.
>
>Here it is:
>
>i have a main menu with buttons, one of my button is "Create new
>invoice", when click on it a form pop up so i can enter my information
>and one of the field (the user cannot change the info in it.) is
>invoice #. Right now, everythime i click on "Create new invoice", the
>invoice # add 1. But my problem is sometime the employee dont fill it
>(for x reason) and shut it down, the nest time they will click on
>"Create new invoice" the invoice # will be 1 more then the last one.
>There will be a blank invoice between them.
>
>Is there a way to get rid of this??[/color]

It depends on how the new invoice numbers are created. What you need is
a system that creates a new invoice number in the before_insert trigger
of the form.

Are you using autonumber?



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

re: Help:creating a new invoice?

NomoreSpam4Me@hotmail.com wrote:[color=blue]
> Hi there i have a little problem with my invoice.
>
> Here it is:
>
> i have a main menu with buttons, one of my button is "Create new
> invoice", when click on it a form pop up so i can enter my information
> and one of the field (the user cannot change the info in it.) is
> invoice #. Right now, everythime i click on "Create new invoice", the
> invoice # add 1. But my problem is sometime the employee dont fill it
> (for x reason) and shut it down, the nest time they will click on
> "Create new invoice" the invoice # will be 1 more then the last one.
> There will be a blank invoice between them.
>
> Is there a way to get rid of this??
>
> thx a lot.
>[/color]
Is the Invoice number an autonumber field? If so, this is to be
expected. An autonumber is not a good method to use for sequential
numbers. An autonumber is GREAT as a key field to link to other tables.

If you enter anything into the form, the autonumber increments. Even if
you undo your changes, the next time the invoice form is opened, a new
number will be one more than the last time an autonumber was
incremented, whether or not the record was saved. So you can expect
holes in the sequence.

The better thing to do is make the invoice number field in your table a
LongInt number. Simply remove the Autonumber type from the table and
change it to Number, type Long. And make it the primary key.

Now you need to determine when you want the record to create new invoice
number. If ONLY ONE person is updating/creating invoices, you can do
something like
If Me.NewRecord then
Me.InvoiceNumber = Dmax("InvoiceNumber","InvoiceTable")
Endif
anytime you want. If you are going to have multiple people entering
invoice numbers at the same time, you might want to do that in the
BeforeUpdate event of the form...IOW, just prior to saving the record.

re: Help:creating a new invoice?

"NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> wrote in message
news:1130966714.978625.264380@g49g2000cwa.googlegr oups.com...[color=blue]
> how do i do that? any hint?
>[/color]

Have your Create Invoice button display an unbound dialog box showing only
the required fields with an OK and CANCEL button. If the user clicks OK,
verify the required fields, append the data and next invoice number to the
table and open the Invoice form to the new record. You could also add a user
ID field to your invoice table to identify who created each invoice.

re: Help:creating a new invoice?

Yes i'm using the auto number....and not +1..

re: Help:creating a new invoice?

In message <1131028215.660172.105730@g44g2000cwa.googlegroups .com>,
"NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> writes[color=blue]
>Yes i'm using the auto number....and not +1..[/color]

That's risky in this situation. Auditors will ask questions if they find
gaps in the invoice number sequence. It's far better to create the
invoice numbers yourself so that you have complete control over them.



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

re: Help:creating a new invoice?

ok, but i dont want them to enter it manually, to risquy i think, and
they wont remember what was the last invoice number?

re: Help:creating a new invoice?

and on my form i have a Save button and a Back tu main menu button.


even if i dont save it the autonumber goes up by 1 each time.

re: Help:creating a new invoice?

If it's a numeric column in the table for invoice number you
can use DMax() to get the highest existing value then add 1
to it.

--
Nick Coe (UK)
Available - Will work for money :-)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store

In
news:1131029792.984473.23260@g14g2000cwa.googlegro ups.com,
NomoreSpam4Me@hotmail.com typed:[color=blue]
> ok, but i dont want them to enter it manually, to risquy i
> think, and they wont remember what was the last invoice
> number?[/color]

re: Help:creating a new invoice?

In message <1131030203.356943.202700@f14g2000cwb.googlegroups .com>,
"NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> writes[color=blue]
>and on my form i have a Save button and a Back tu main menu button.
>
>
>even if i dont save it the autonumber goes up by 1 each time.[/color]

One way to handle this is to search the invoice table for the largest
invoice number, add one to it and then use this as the next invoice
number. If it is a large table this could be slow.

Another approach is to use a table that has one row and one field. When
you create an invoice you:

1. read the number stored there
2. add one to it
3. store the new number, overwriting the old one
4. create the invoice record using the new number

The order that you do things can be important. If the process is
interrupted or fails part way it is better to have a gap in the sequence
than to have two invoices with the same number.




--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

re: Help:creating a new invoice?

"Bernard Peek" <bap@shrdlu.com> wrote in message
news:YWHnuYWOpiaDFwk9@shrdlu.com...[color=blue]
> In message <1131030203.356943.202700@f14g2000cwb.googlegroups .com>,
> "NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> writes[color=green]
> >and on my form i have a Save button and a Back tu main menu button.
> >
> >
> >even if i dont save it the autonumber goes up by 1 each time.[/color]
>
> One way to handle this is to search the invoice table for the largest
> invoice number, add one to it and then use this as the next invoice
> number. If it is a large table this could be slow.
>
> Another approach is to use a table that has one row and one field. When
> you create an invoice you:
>
> 1. read the number stored there
> 2. add one to it
> 3. store the new number, overwriting the old one
> 4. create the invoice record using the new number
>
> The order that you do things can be important. If the process is
> interrupted or fails part way it is better to have a gap in the sequence
> than to have two invoices with the same number.
>
>
>
>
> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author.
>[/color]
dMAX will not take long if the Invoice number is indexed.
You can't have a duplicate invoice if the invoice number is the primary key.
You will need to check that your append query succeeded before opening the
invoice form, because a 2nd user may grab the next invoice number a split
second before the 1st

re: Help:creating a new invoice?

"Bernard Peek" <bap@shrdlu.com> wrote in message
news:YWHnuYWOpiaDFwk9@shrdlu.com...[color=blue]
> In message <1131030203.356943.202700@f14g2000cwb.googlegroups .com>,
> "NomoreSpam4Me@hotmail.com" <ugo_lavoie@hotmail.com> writes[color=green]
> >and on my form i have a Save button and a Back tu main menu button.
> >
> >
> >even if i dont save it the autonumber goes up by 1 each time.[/color]
>
> One way to handle this is to search the invoice table for the largest
> invoice number, add one to it and then use this as the next invoice
> number. If it is a large table this could be slow.
>
> Another approach is to use a table that has one row and one field. When
> you create an invoice you:
>
> 1. read the number stored there
> 2. add one to it
> 3. store the new number, overwriting the old one
> 4. create the invoice record using the new number
>
> The order that you do things can be important. If the process is
> interrupted or fails part way it is better to have a gap in the sequence
> than to have two invoices with the same number.
>
>
>
>
> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author.
>[/color]

Dmax would search a large table quickly if Invoice number is indexed.
Invoice number could not be duplicated if Invoice number is the primary key.
You would need to check that the append query succeeded before opening to
the new invoice. A 2nd user may grab the next number just before the 1st,
causing a key violation in the append query.