Change text box to populate a new sequenced number

[Amelia]

I have a text box on a form that is called [Invoice number]. I want it to
autopopulate an invoice number based on the format I want. (which is
"G-0000")

So I want the invoice numbers to start at G-0100, and then +1 for every new
record. I currently have previous invoices entered, I don't know if this
makes a difference.

If anyone can help me that would be great!

 

[Keven Denen]

I have a text box on a form that is called [Invoice number]. I want it to
autopopulate an invoice number based on the format I want. (which is
"G-0000")

So I want the invoice numbers to start at G-0100, and then +1 for every new
record. I currently have previous invoices entered, I don't know if this
makes a difference.

If anyone can help me that would be great!

There are hundreds of questions that are exactly the same already on
here. Take a moment to search through the old posts and I'm sure
you'll find your answer. Or take a moment to use Google, I'm sure
you'll find a hundred thousand websites that answer that question.

Keven Denen

"Give a man a fish and he'll eat for a day, teach him how to use
Google and he'll eat forever."

 

[Amelia]

That is the problem. I have found lots of solutions and have tried many and I
can not get anything to work the way I want. Some people have it way more
complicated then I want, others don't elaborate enough in their posts for me
to figure out where to put things. I just think it should either be a simple
code to put in the "pop-up" form on load event or a setting with in Access. I
have looked at sample databases and they have to many underlying macros and
codes that make no sense to me.

I have a text box on a form that is called [Invoice number]. I want it to
autopopulate an invoice number based on the format I want. (which is
"G-0000")

So I want the invoice numbers to start at G-0100, and then +1 for every new
record. I currently have previous invoices entered, I don't know if this
makes a difference.

If anyone can help me that would be great!

There are hundreds of questions that are exactly the same already on
here. Take a moment to search through the old posts and I'm sure
you'll find your answer. Or take a moment to use Google, I'm sure
you'll find a hundred thousand websites that answer that question.

Keven Denen

"Give a man a fish and he'll eat for a day, teach him how to use
Google and he'll eat forever."

 

[Jeanette Cunningham]

Hi Amelia
assuming that this is not a multi-user database, you can do it something
like this untested air code-->
In the before update event of the form, find the current invoice no. and add
1.

To be able to add 1, you need the invoice number as a number and not a
string.
G-0100 is a string.
To add 1 you need just the number part then you do simple maths on it.

Use code like this-->

Private Function NextInvoiceNbr As Long
Dim lngNbr As Long
Dim strNbr As String
Dim strCurrentInvNbr As String

strCurrentInvNbr = DMax("[Invoice number]", "NameOfTable")

strNbr = Mid(strCurrentInvNbr, 3)
Debug.Print strNbr
lngNbr = CLng(strNbr)
Debug.Print lngNbr
NextInvoiceNbr = lngNbr+1
Debug.Print NextInvoiceNbr
End Function

In the before update event of the form, use code like this-->
If IsNull(Me.[Invoice number]) Then
Me.[Invoice number] = NextInvoiceNbr
End If

Note: replace NameOfTable with the name of the table with the invoice info
in it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

Good catch about adding the G-0 back into the invoice number.
Thanks for the tip about not needing to convert from a string to a number to
do the math.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette:

You are looking up a string Invoice Number and parsing it to get a number
here, but then, after adding 1 to the number, you are assigning the result
to
the Invoice Number column without tacking on the 'G-' again. It needs to
be
one or the other. Also it needs to be seeded to start at G-0100:

Private Function NextInvoiceNbr() As Long

Dim lngNbr As Long
Dim strNbr As String
Dim strCurrentInvNbr As String

' look up current highest invoice number
strCurrentInvNbr = DMax("[Invoice number]", "NameOfTable")

' seed number if necessary
If strCurrentNumber <> "G-0099" Then
strCurrentNumber = "G-0099"
End If

' strip off leading 'G-' from returned string
strNbr = Mid(strCurrentInvNbr, 3)
lngNbr = CLng(strNbr)
' increment value by 1
NextInvoiceNbr = lngNbr+1
' format as return value of function
NextInvoiceNbr = Format(NextInvoiceNbr ,"\G\-0000")

End Function

In fact you don't really need to convert the return value of the Mid
function
to a long integer data type; you can simply add 1 to the string value
returned. As long as the string is capable of being interpreted as a
number
it will be treated as such in an arithmetical operation. But on the other
hand why not simply store the value as a long integer and format it with:

Format([Invoice number],"\G\-0000")

Roger Carlson's solution for handling conflicts can then be employed:

http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


Ken Sheridan
Stafford, England

Hi Amelia
assuming that this is not a multi-user database, you can do it something
like this untested air code-->
In the before update event of the form, find the current invoice no. and
add
1.

To be able to add 1, you need the invoice number as a number and not a
string.
G-0100 is a string.
To add 1 you need just the number part then you do simple maths on it.

Use code like this-->

Private Function NextInvoiceNbr As Long
Dim lngNbr As Long
Dim strNbr As String
Dim strCurrentInvNbr As String

strCurrentInvNbr = DMax("[Invoice number]", "NameOfTable")

strNbr = Mid(strCurrentInvNbr, 3)
Debug.Print strNbr
lngNbr = CLng(strNbr)
Debug.Print lngNbr
NextInvoiceNbr = lngNbr+1
Debug.Print NextInvoiceNbr
End Function

In the before update event of the form, use code like this-->
If IsNull(Me.[Invoice number]) Then
Me.[Invoice number] = NextInvoiceNbr
End If

Note: replace NameOfTable with the name of the table with the invoice info
in it.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a text box on a form that is called [Invoice number]. I want it to
autopopulate an invoice number based on the format I want. (which is

[quoted text clipped - 6 lines]

If anyone can help me that would be great!