Getting Auto Numbers as Period Changes

I have designed the form,i want to get numbers in the text box as 0001 for the first time then 0002,.........like this way i need to get  whenever i click Add button,one more thing as the  period changes (month changes) again the number should start with 0001,0002,............like this i want to have the auto numbers,whenever the month changes the number must start from 0001.
Please let me know,how can i do this.
devdeptAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LambersonSoftware EngineerCommented:
Hi devdept,
Assuming you are storing the number in a database, use something like this........
replace the <last record number in your database> part with the value of the last number in your db.

Private Sub Text1_Change()
Dim numb As Long
numb = Month(Date)
Text1.Text = Format(numb+ <last record number in your database>, "0000")

End Sub

hth
0
devdeptAuthor Commented:
I am using database,my table name is incoming and i have columns as Period,Refno,Subject,Groupname......
I want to store date in Period and Auto numbers in Refno column,as the period change (that is month) the number should starts from 0001.
0
Bob LambersonSoftware EngineerCommented:
Hi devdept,

the varible numb is getting the number of the current month in the line
          numb = Month(Date)

the next line formats and adds the current month number to whatever the last record number you stored and fills the text box.
       
add a button to your form, then in the click event code as below.

Private Sub Command0_Click()
Dim numb As Long
Dim newNumb as long
Dim rs as recordset

'set up your recordset to your needs.

numb = Month(Date)

'check to see if we are in the same month as the last date saved.
if val(numb) = val(month(rs.Period)) then
    Text1.Text = Format(numb+ rs.Auto, "0000")
else
    Text1.text = Format(numb, "0000")
end if

'then you can save the value in the text1.text to your database field Auto, and the date to your database field Period.
End Sub

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob LambersonSoftware EngineerCommented:
Hi devdept,
actually these lines should be.............

if val(numb) = val(month(rs.Period)) then
    Text1.Text = Format(1+ rs.Auto, "0000")
else
    Text1.text = Format(1, "0000")
end if

my mistake
0
devdeptAuthor Commented:
Private Sub Command1_Click()
Text1 = Format(Date, "YYYY-MM")


I am storing year and month in textbox 1 as above format in text4 i want to store 0001 for the first time,later it should increase the number like this 0002,when the period changes to 2006-05 the number again should start from 0001 and i want to store this in database when ever i click new button
0
Bob LambersonSoftware EngineerCommented:
Hi devdept,
> Private Sub Command1_Click()
> Text1 = Format(Date, "YYYY-MM")

if you have the above displayed in textbox named Text1, then later want to store that in your database (on button click or whatever) that is fine and a seperate situation from incrementing the series of numbers you want.

so, take the date you are working with in Text1 or in the database (this would be the result of your code above) and use following code to increment the number you want by one, each time you click the button

Private Sub Command0_Click()
Dim numb As Long
Dim newNumb as long
Dim rs as recordset

'set up your recordset to your needs.

numb = Month(Date)

'check to see if we are in the same month as the last date saved.

'the first part of the If statement checks the current month value aganst the month value of the last stored date you have saved, then increments the value by one and formats the value to the "0000" format, then puts that new value in the textbox.
'the else part of the If statement simply increments the value in Text1 by one and formats the value to the "0000" format, then puts that new value back in the textbox.

if val(numb) = val(month(rs.Period)) then
           Text1.Text = Format(1+ rs.Auto, "0000")
else
    Text1.text = Format(1, "0000")
end if

If you want to put the new number value in the database, just change the lines slightly, something like ........

rs.addnew
if val(numb) = val(month(rs.Period)) then
           rs.Auto = Format(1+ rs.Auto, "0000")
else
    rs.Auto = Format(1, "0000")     'or whatever you are calling your auto number field.
end if

HTH
0
devdeptAuthor Commented:
But during retrival time,i am getting 1 instead of 0001,and when i  check in the database it is storing as 1,2,.....instead of 0001,0002,.........,.

when i wrote on button click event
rs.MoveNext
       If rs.EOF Then
          rs.MoveLast
       Else
         Text1.Text = rs("Period") & ""
         Text5.Text = rs("Type") & ""
         Text7.Text = rs("Refno") & ""
          Combo1.Text = rs("Groupname") & ""
          Text3.Text = rs("Subject") & ""
       End If
I am getting 1 instead of 0001,
0
Bob LambersonSoftware EngineerCommented:
Hi devdept,
>       Text1.Text = rs("Period") & ""
>          Text5.Text = rs("Type") & ""
>          Text7.Text = rs("Refno") & ""     <<<<<<<<Change this code to    Text7.Text = Format(rs("Refno"),"0000")
>           Combo1.Text = rs("Groupname") & ""
>           Text3.Text = rs("Subject") & ""

that will display it in the textbox like you want it.


HTH
0
devdeptAuthor Commented:
When i am using this code,i am getting error "method or datamember not found"
if val(numb) = val(month(rs.Period)) then
           Text1.Text = Format(1+ rs.Auto, "0000")
else
    Text1.text = Format(1, "0000")
end if

I am using below code :

Text1 = Format(Date, "YYYY-MM")
    If Not rs1 Is Nothing Then Set rs1 = Nothing
    rs1.Open "select max(Refno) from incoming where Period='" & Format(Text1, "YYYY-MM") & "'", cn, adOpenDynamic, adLockOptimistic
             Dim numb As Long
             Dim newnumb As Long
            numb = Month(Date)
            If Val(numb) = Val(Month(rs1.Period) Then
                  Text7.Text = Format(1 + rs1.Refno, "0000")
            Else
                  Text7.Text = Format(1, "0000")
            End If

Still i am getting error "Method or datamember not found"
0
Bob LambersonSoftware EngineerCommented:
Hi devdept,
>     If Not rs1 Is Nothing Then Set rs1 = Nothing
I think it is because you are setting the recordset object to nothing in the above line.

Not sure what you want to accomplish there but try adding .........
    Set rs1 = New Recordset         after that line.
HTH
0
Bob LambersonSoftware EngineerCommented:
Hi devdept,
>     If Not rs1 Is Nothing Then Set rs1 = Nothing
I haven't tested it, but you should be able to just delete the line above, assuming you already have a     rs1    created in prior code.
HTH
0
devdeptAuthor Commented:
Text1 = Format(Date, "YYYY-MM")
    If Not rs1 Is Nothing Then Set rs1 = Nothing
    rs1.Open "select max(Refno) from incoming where Period='" & Format(Text1, "YYYY-MM") & "'", mod1.cn, adOpenDynamic, adLockOptimistic
        If Len(Trim(rs1(0) & "")) <> 0 Then
           Text7.Text = Val(rs1(0) & "") + 1
        Else
           Text7.Text = 1000

When i am using above code i am getting Autonumbers starting from 1000 then incrementing to 1001,1002,1003,...,when ever i click add button,again when the period changes it starts again from 1000.

But i need to start from 0001 instead of 1000
0
vkaniCommented:
Hi devdept,
just change the last 4 lines of your code as follows:

 If Len(Trim(rs1(0))) <> 0 Then
           Text7.Text = format(Val(rs1(0)) + 1,"0000")
        Else
           Text7.Text = format(1,"0000")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.