Autonumber?

[Guest]

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

 

[Guest]

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

I wasn't ragging you for asking a duplicate question. Sometimes questions go
under so many names, it is hard to find what you want.

I would consider the On Current event of the form.

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

Oh, no problem, I didn't take it as such...

I'm not getting it to work when I program in the "If/Then" part doesn't seem
to be compiling or going in correctly, am I missing quotes or something
somewhere?

I wasn't ragging you for asking a duplicate question. Sometimes questions go
under so many names, it is hard to find what you want.

I would consider the On Current event of the form.

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

:

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

Oops, untested air code in a hurry. Sorry, here is what I think the problem
is:
Is:
If IsNull strRMA Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Should Be:
If IsNull(strRMA) Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Oh, no problem, I didn't take it as such...

I'm not getting it to work when I program in the "If/Then" part doesn't seem
to be compiling or going in correctly, am I missing quotes or something
somewhere?

I wasn't ragging you for asking a duplicate question. Sometimes questions go
under so many names, it is hard to find what you want.

I would consider the On Current event of the form.

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

:

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

:

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

yeah, I played with it and got that far just as you answered...unfortunately
I'm a bit "fresh" to the programming side of all this (it's been awhile!).

Now it gives me (after putting it all in correctly) an "Invalid use of Null"
error.

Sorry to be a pain about this...I'm reading/trying to understand the code,
but just not sure where it's messing up. Do I need to <somehow> let it know
the month/date in the code? Or, from what I'm reading, it should get that
via the Month(date) & Year(date) functions? Right?

Oops, untested air code in a hurry. Sorry, here is what I think the problem
is:
Is:
If IsNull strRMA Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Should Be:
If IsNull(strRMA) Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Oh, no problem, I didn't take it as such...

I'm not getting it to work when I program in the "If/Then" part doesn't seem
to be compiling or going in correctly, am I missing quotes or something
somewhere?

I wasn't ragging you for asking a duplicate question. Sometimes questions go
under so many names, it is hard to find what you want.

I would consider the On Current event of the form.

:

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

:

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

:

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks

 

[Guest]

jeffrey,
You are no pain at all.
Can you tell me which line is getting the invalid use of null? My guess it
that it is the line with the DMAX in it. Try typing it in in the immediate
window and see what the result it. You can use a ? to see the value returned:

? DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4)

As to your other questions, see below:

yeah, I played with it and got that far just as you answered...unfortunately
I'm a bit "fresh" to the programming side of all this (it's been awhile!).

Now it gives me (after putting it all in correctly) an "Invalid use of Null"
error.

Sorry to be a pain about this...I'm reading/trying to understand the code,
but just not sure where it's messing up. Do I need to <somehow> let it know
the month/date in the code? Or, from what I'm reading, it should get that
via the Month(date) & Year(date) functions? Right?

Correct. the Month() and Year() return an integer for today's date,
Month(date) will return 6 and Year(Date) will return either 2005 or 05
depending on how you have Access configured to handle datesl . (Date) returns
the current system date.

Oops, untested air code in a hurry. Sorry, here is what I think the problem
is:
Is:
If IsNull strRMA Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Should Be:
If IsNull(strRMA) Then 'This is the first entry for the Month/Year strRMA =
strRMA & "01"

Oh, no problem, I didn't take it as such...

I'm not getting it to work when I program in the "If/Then" part doesn't seem
to be compiling or going in correctly, am I missing quotes or something
somewhere?

:

I wasn't ragging you for asking a duplicate question. Sometimes questions go
under so many names, it is hard to find what you want.

I would consider the On Current event of the form.

:

Thanks for the quick reply...I (thought) I looked through for similiar...but
will do a search.

Anyhow...this sounds like it will work for what I need. At this point, yes,
it is a multi-user environment, but the RMA part will be very limited, so I
don't think I have to worry about "using up" or duplicates being grabbed.

And they seldom (at least in the past 6 months of checking) gone past
needing more than 40 numbers per month (guess that speaks towards repairs on
their products...if it gets THAT high...uh oh!).

Anyhow...I'll look at your code, research some on where to put it!!

Thanks

:

jeffrey,

How to do sequential numbering is a common question here, but here is how it
works.

First we need to find the highest current number for the month and year.
Dim intMo, intYr as Integer
Dim strRMA as String
Dim strNxtNbr as String

intMo = Month(date)
intYr = Year(date) - 2000 'If you are using 4 digit years, we need to remove
the 2000 part

'Format the Month and Year for the Lookup
strRMA = format(intM0, "00") & format(intYr, "00")

'Do a Lookup to find the current highest number
strNxtNbr = DMAX("[RMA_Nbr]","MyTableName","Left([RMA_Nbr], 4) = '" _
& strRMA & "'")
If IsNull strRMA Then 'This is the first entry for the Month/Year
strRMA = strRMA & "01"
Else
'Add 1 to the current high number and format it
strRMA = strRMA & Format(Right(strNxtNbr,2) + 1,"00")
End If

Now you have your next number. There are a couple of issues worth
consideration:
It would be better if instead of Month, Year you formatted your number as
Year, Month. This would make sorting a whole lot better. The way it is now,
you will get all the Januarys for 2005 followed by all the Januarys for 2006
followed by all the Februarys for 2005. See the problem?

The next issue is only a problem if you are in a multi user environment. If
you are, you will have to deal with the situation where two users could be
adding RMAs and because the first user has not yet updated the table, the
second user would get the same number. The second user to try to update will
get an error or will overwrite what the first user entered.

The easiest solution is to put some code in the before update event of the
form to be sure the number is still available before updating. If the number
now exists, you will have to increment the number and check again.

And, the last issue is whether 99 is a safe upper limit for the RMA numbers.
If there is any possibility of going beyond that, you might need to make
that number one character longer.

:

Hello

I'm working on creating an RMA table and I want a form to be able to assign
the "next" available number.

The catch is, the numbers need to somehow be controlled...something like
this...
The number format should be in this format (as it currently is in a manual
format now).
060501
Where 06 is the month, 05 is the year and the first number for each month
will start at 01

Will I just need to make a table with all those numbers in it? Autonumber
starts at 1 and goes from there...I couldn't find any way to control what the
number it hands out is.

Thanks