Sequential Number (1 Viewer)

[Puddy]

Sequential Number Help

I have created a command button on my form that when a user clicks it, it generates a serial# and places it into the serial# field on the form....the format for the serial# is Vyyww d ###
What I now need this to do is to reset the ### part each day...
here is the code I have created so far:
Private Sub Command11_Click()
Dim MyDate, MyYear, MyWeek, MyDay
Dim Ret As String
Dim GenerateSerial As String
Dim rs As Recordset
Dim MyNumber As String

MyDate = Date
MyYear = Format(Date, "yy")
MyWeek = Format(DatePart("ww", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "00")
MyDay = Format(DatePart("w", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "0")

MyNumber = Format(DMax("[Number]", "Table1") + 1, "000")

Ret = "V" & MyYear & MyWeek & " " & MyDay & " " & MyNumber

Forms!Form1!Serial = Ret

 

[FloBob]

Public Function getNumber() As String

Dim MyDate, MyYear, MyWeek, MyDay, MyNumber As Integer
Dim Ret As String
Dim Test As Variant

MyDate = Date
MyYear = Format(Date, "yy")
MyWeek = Format(DatePart("ww", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "00")
MyDay = Format(DatePart("w", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "0")

Ret = "V" & MyYear & MyWeek & " " & MyDay

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")


If IsNull(Test) Then
    Ret = Ret & " 001"
Else
        
    MyNumber = CInt(Right(Test, 3)) + 1
    
    Select Case MyNumber
    Case Is < 10
        Ret = Ret & " 00" & CStr(MyNumber)
    Case Is < 100
        Ret = Ret & " 0" & CStr(MyNumber)
    Case Else
        Ret = Ret & " " & CStr(MyNumber)
    End Select
       
End If

getNumber = Ret

End Function

That should do what you want it to do. I created it in a function you can call whenever you needed the new number. I suggest though that you call this function right before you add the new record to the table instead of storeing it in the form because if you have multiple users they run the risk of getting the same number. I hope this helps.

 

[Puddy]

Thanks for the help, the above code worked like a charm...I have another question regarding this database and wheter or not it can be done...is it at all possible to generate numerous serial#'s at one time...meaning, right now if the generate serial function is used, it will generate 1 serial #, but if I were to add a field on the form for the qty of the model's needing a serial #, is there any way to code it to generate that many serial #'s?

Thanks again.

 

[Rich]

Why can't you just use Format(Date(), "yywwdd") + your DMax+1

 

[FloBob]

You could just generate that serial number in the form that is creating the record and when your posting it to the table assign it to each record.

Rich,
He needed the serial number to regenerate every day... not just change the date. So it searches for the serial number with the date and makes it 001 if needed or adds 1 to it and then assigns it to the table.

Records would look like this

V 0404 5 001
V 0404 5 002
V 0404 6 001
V 0404 6 002
V 0404 6 003
etc....

If I used the dmax function only I would get

V 0404 5 001
V 0404 5 002
V 0404 6 003
V 0404 6 004
V 0404 6 005

Which isnt what hes looking for.

 

[GrahamB]

Actually you can do it using DMAX

next_number = "V " & Format(Date, "yyww dd ") & Format(IIf(IsNull(DMax("right([MyNumber],3)", "Table1", "left([Mynumber],9)=""V " & Format(Date, "yyww dd") & """")), 0, DMax("right([MyNumber],3)", "Table1", "left([Mynumber],9)=""V " & Format(Date, "yyww dd") & """")) + 1, "000")

Sorry I couldn't resist having a bash

 

[FloBob]

Did not think of that... That will work aswell. Isn't access grand!! P

 

[Puddy]

Not sure what happened, I've been working on trying to get the above to generate more then one sn#, but have now gone back to the original and for some reason it is not working anymore...I can generate the number, but it doesn't go to the next number when create a new record

 

[GrahamB]

Have you missed out the '+1' ???

I've done that before

 

[Puddy]

Here's the exact code I'm using, don't think I'm missing anyting:

Private Sub Command11_Click()
Dim MyDate, MyYear, MyWeek, MyDay, MyNumber As Integer
Dim Ret As String
Dim Test As Variant

MyDate = Date
MyYear = Format(Date, "yy")
MyWeek = Format(DatePart("ww", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "00")
MyDay = Format(DatePart("w", MyDate, vbUseSystemDayOfWeek, vbUseSystem), "0")

Ret = "V" & MyYear & MyWeek & " " & MyDay

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")


If IsNull(Test) Then
Ret = Ret & " 001"
Else

MyNumber = CInt(Right(Test, 3)) + 1

Select Case MyNumber
Case Is < 10
Ret = Ret & " 00" & CStr(MyNumber)
Case Is < 100
Ret = Ret & " 0" & CStr(MyNumber)
Case Else
Ret = Ret & " " & CStr(MyNumber)
End Select

End If

Forms!Form1!Serial = Ret

 

[Puddy]

anyone have any ideas? This was working like a charm 2 weeks ago and now it's not...
I can see where the problem lies, but cannot figure out why it is happening...I put a breakpoint at the beginning of the If statment, and check the Results of the Test= it always comes up Null?

Anyone have any ideas...coming under the gun to get this to work again...thanks

 

[GrahamB]

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")

Have you renamed any of the fields or the table its self?

Otherwise I'd put my money on "number like ' should be "[number] like ' ???

 

[Puddy]

argggg....I just recreated the entire database, rewrote the code, and still, same freakin problem...for some reason the Test is still equal to null everytime so it keeps returning the 001...

I'm attaching the database to this as like i said, it is not too large, no real data in it yet...if someone could take a look and maybe see what I'm not seeing...thanks

 

[GrahamB]

Your soo going to kick yourself over this

Test = DMax("[number]", "TABLE1", "number like '" & Ret & "*'")


should be

Test = DMax("[serial]", "TABLE1", "serial like '" & Ret & "*'")

 

[Puddy]

you're right I'm kicking myself real hard right now, can't believe I missed that..thanks alot for the extra pair of eyes and the help....