Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

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.
0
devdept
Asked:
devdept
  • 7
  • 5
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now