unique invoice number

[Guest]

I am new to access and have a small buisness that I want to put a simple
invoice program together.

What I need to know is how to a generate a sequential number from the
starting point of 2000 for a unique invoice number field.

Thanks in advance for any /all help.

 

[UpRider]

Eric, do this:
1. Add a long integer field to your Orders table and call it perhaps
InvoiceNo.
2. Display the table in datasheet mode and add a dummy invoice with number
1999.
3. On your Orders form (I hope you are using a form to enter and edit your
data),
make the data source for InvoiceNo textbox
=nz(dmax("InvoiceNo","tblOrders"))+1
4. When all is well, display the table in datasheet mode and delete the
dummy invoice 1999.
Hope this helps,
UpRider

 

[Guest]

Set a dummy record in your invoice table. The column InvoiceNumber = 2000

Add this code to your invoice form so that when a new invoice is opened it
places the invoice number in the appropriate field.


Dim Inv As String
Inv = DMax("InvoiceNumber","tblInvoice")
Me.InvNofield.Value = Inv +1

Don't use a autonumber for an invoice number as any uncompleted invoices
will start to cause problems as the autonumber generates as soon as a record
is initiated. Unless you use a Insert statement.

I hope this makes sense for you.

Regards,

Nick.

 

[Guest]

UpRider,
Thanks man I do appreciate the help. However being a newbie (sorry in
advance) I kinda lost you on the step 3 part.

A bit more enlightenment and I should have it.

Thanks again in advance.

 

[UpRider]

Eric, do you have a form with your orders table as the underlying
recordsource?
Does the form display existing orders and allow you to input new orders?
We have to get to that point before you can proceed with the automatic
invoice no.

UpRider

 

[tina]

3. On your Orders form (I hope you are using a form to enter and edit your

data),
make the data source for InvoiceNo textbox
=nz(dmax("InvoiceNo","tblOrders"))+1

if UpRider means the ControlSource property of the textbox control, when he
refers to the "data source", i'm afraid that won't work. a calculated
control (which is a control with the ControlSource property set to an
expression) is, by definition, unbound. whatever value is displayed in it
will *not* save to a field in the table; you would have to write the value
to the table programmatically.

suggest you skip steps 2, 3, and 4 from the previous post. instead, add the
InvoiceNo field to your Orders table, as previously suggested. if you
haven't already done so, create a form bound to the table; you'll do the
programming and the data entry in the form - not directly in the table.

next, add the following code to the form's BeforeUpdate event procedure, as

If Me.NewRecord Then
Me!InvoiceNo = Nz(DMax("InvoiceNo", _
"tblOrders"), 1999)+1
End If

if you don't know how to create a VBA procedure in a form, see
http://home.att.net/~california.db/instructions.html, and click on the
"Create a VBA event procedure" link, for illustrated step-by-step
instructions. the code above assumes that the name of the table is
tblOrders, and the name of the field is InvoiceNo. if those values are
different in your database, you must adjust the code accordingly.

when the user adds or edits a record, the code fires; it checks to see if
it's a new record, and if so, then a new invoice number is generated, just
before the record is saved to the table. note that, generally speaking, in a
multi-user (or potential multi-user) environment, i set incrementing code to
the BeforeUpdate event to lessen (while not eliminating) the possibility of
duplicate numbers being generated.

hth

 

[Guest]

tina,
Did what you said. I do get the starting invoice number of 1999. But when
going to a new form I get a debug error. I pasted your code into the area
you said.

Thanks in advance.

 

[tina]

comments inline.

tina,
Did what you said. I do get the starting invoice number of 1999.

you shouldn't get a starting number of 1999; it should start at 2000.

But when
going to a new form I get a debug error.

that doesn't tell me anything useful. first, please explain what you mean by
"going to a "*new form*". next, copy the entire procedure, including the
Private Sub and End Sub lines, and paste into your post. also, do whatever
triggers the error, and click the Debug button in the message box. Access
will take you to the line of code that errs out, and highlight it; tell me
which line is erring.

 

[tina]

btw, i'm through for the night, Eric. i'll check this thread Friday evening
(it's Thursday evening here); and, of course, somebody else may step in and
give you a hand before then.

 

[Guest]

sorry, meant new record not new form.

Here are the two lines of code that are yellowed:

Me!InvoiceNo = Nz(DMax("InvoiceNo", _
"tblOrders"), 1999) + 1

Here is the entire expression:

Private Sub InvoiceNo_Enter()
If Me.NewRecord Then
Me!InvoiceNo = Nz(DMax("InvoiceNo", _
"tblOrders"), 1999) + 1
End If


End Sub


Thanks

 

[Guest]

Thanks for your help Tina. Sorry I'm being stupid. I have a generic email
address if that helps (e-mail address removed)

Thanks again
Eric

 

[Guest]

Try adding some error trapping code this should give a better understanding

Private Sub InvoiceNo_Enter()
On Error GoTo Errtrp
If Me.NewRecord Then
Me!InvoiceNo = Nz(DMax("InvoiceNo", "tblOrders"), 1999) + 1
End If

Exit1:
Exit Sub

Errtrp:
Msgbox Err.Description & " (" & Err.Number & ")"
GoTo Exit1

End Sub

Hope it helps,

Regards,
Nick

 

[JK]

Hi Eric,

Unless I missed something
No need for "dummy" record



in OnCurrent event:

dim NextNum as long

NextNum=Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1
Me.InvoiceNo.DefaultValue="'" & nextNum & "'"

Or combine the 2 lines into one withoui the Dim statement:

Me.InvoiceNo.DefaultValue="'" &
Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1 & "'"


++++++
1) To aviode confuision "'" is " ' " without the spaces)


Regards/JK

 

[Guest]

I am getting debug errors on this one as well. Gawd I feel so stupid.

Eric

Hi Eric,

Unless I missed something
No need for "dummy" record



in OnCurrent event:

dim NextNum as long

NextNum=Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1
Me.InvoiceNo.DefaultValue="'" & nextNum & "'"

Or combine the 2 lines into one withoui the Dim statement:

Me.InvoiceNo.DefaultValue="'" &
Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1 & "'"


++++++
1) To aviode confuision "'" is " ' " without the spaces)


Regards/JK

I am new to access and have a small buisness that I want to put a simple
invoice program together.

What I need to know is how to a generate a sequential number from the
starting point of 2000 for a unique invoice number field.

Thanks in advance for any /all help.

 

[tina]

re-read my first post to this thread, hon. i said to put the code in the
*form's BeforeUpdate event procedure* (NOT in the control's Enter event
procedure), and explained why. recommend that you disable and lock the
InvoiceNo control in the form; it's not a good idea to allow the user to
enter or edit that field when you're assigning the invoice number
programmatically.

also, you need to review the table and field that the form is bound to: is
the table named tblOrders? if not, correct the table name in the code. is
the field named InvoiceNo? if not, correct the field name in the code.

hth

 

[JK]

Eric,

Sorry for late response

Did you solve your problem?

If Not:
Please advise The following:
1. The name Of The table where in the invoice field is
2. The name of the invoice number field on the table
3. The name of invoice number control name on the form (in the form's
properties)
4. At what stage you are getting the "debug errors" as you called it, (what
*exactly* were you doing before you see the error message"
5. "What is the *exact* message you are getting

Awaits yours

Regards/JK

I am getting debug errors on this one as well. Gawd I feel so stupid.

Eric

Hi Eric,

Unless I missed something
No need for "dummy" record



in OnCurrent event:

dim NextNum as long

NextNum=Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1
Me.InvoiceNo.DefaultValue="'" & nextNum & "'"

Or combine the 2 lines into one withoui the Dim statement:

Me.InvoiceNo.DefaultValue="'" &
Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1 & "'"


++++++
1) To aviode confuision "'" is " ' " without the spaces)


Regards/JK

 

[Guest]

Yes, It was a typo on my part (stupid me). I really appreciate all your help.

Thanks again JK

Eric,

Sorry for late response

Did you solve your problem?

If Not:
Please advise The following:
1. The name Of The table where in the invoice field is
2. The name of the invoice number field on the table
3. The name of invoice number control name on the form (in the form's
properties)
4. At what stage you are getting the "debug errors" as you called it, (what
*exactly* were you doing before you see the error message"
5. "What is the *exact* message you are getting

Awaits yours

Regards/JK

I am getting debug errors on this one as well. Gawd I feel so stupid.

Eric

Hi Eric,

Unless I missed something
No need for "dummy" record



in OnCurrent event:

dim NextNum as long

NextNum=Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1
Me.InvoiceNo.DefaultValue="'" & nextNum & "'"

Or combine the 2 lines into one withoui the Dim statement:

Me.InvoiceNo.DefaultValue="'" &
Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1 & "'"


++++++
1) To aviode confuision "'" is " ' " without the spaces)


Regards/JK

 

[JK]

My pleasure

Regards/JK

Yes, It was a typo on my part (stupid me). I really appreciate all your
help.

Thanks again JK

Eric,

Sorry for late response

Did you solve your problem?

If Not:
Please advise The following:
1. The name Of The table where in the invoice field is
2. The name of the invoice number field on the table
3. The name of invoice number control name on the form (in the form's
properties)
4. At what stage you are getting the "debug errors" as you called it,
(what
*exactly* were you doing before you see the error message"
5. "What is the *exact* message you are getting

Awaits yours

Regards/JK

I am getting debug errors on this one as well. Gawd I feel so stupid.

Eric


:

Hi Eric,

Unless I missed something
No need for "dummy" record



in OnCurrent event:

dim NextNum as long

NextNum=Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1
Me.InvoiceNo.DefaultValue="'" & nextNum & "'"

Or combine the 2 lines into one withoui the Dim statement:

Me.InvoiceNo.DefaultValue="'" &
Nz(Dmax("[InvoiceNo]","tblOrders"),1999)+1 & "'"


++++++
1) To aviode confuision "'" is " ' " without the spaces)


Regards/JK