Question Auto Generate NO and Reset it Everyday

[wschian]

Hello, Please help me!

I Want to automatic Generate the Numbers (Me.RID) when everytime loading the form. EXAMPLE "14011701", "14011702", "14011703", "14011704"....

140117 mean "yymmdd" and the last 2 digit mean autorun Number.

But in the next day 140118 I want the last 2 digit auto Reset and start form 01 example "14011801", "14011802", "14011803", "14011804"....

Same also for day 140119, "14011901", "14011902", "14011903", "14011804"....

Below is my incomplete VB code, i dont know how to reset the last 2 digit every day.:banghead::banghead:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub Form_Load()
Dim RNos As String

RNo = DLookup("[RNos]", "Temp_Table")
Me.RID.SetFocus
Me.RID = Format(Date, "yymmdd") & Format(Val(Right$(RNo, 2) + 1), "00")
DoCmd.SetWarnings False
DoCmd.RunSQL "update Temp set Temp_Table.RNos = '" & Me.RID & "'"


End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

[pr2-eugin]

Check out the DMax Plus One thread by RainLover, it will be a good place to start.

 

[namliam]

Just as an added note, changing dates into numbers in a DDMMYY format is generaly a bad idea even if only IMNSHO

If you were to use YYMMDD as your format it might be a lot easier

 

[RichP]

Or for fun you could set the default value for RID on the form to

=nz(dmax("RID","Table1","Left$([RID],6)=format(Date(),""YYMMDD"")")+1,format(Date(),"YYMMDD") & "01")

where Table1 is the name of the table that contains RID. This takes the maximum value of RID each day stored in table1 and adds 1 to it. If there haven't been any that day then the dmax function returns null, the +1 is ignored and the nz function on the outside picks up the format(Date(),"YYMMDD") & "01" instead

 

[RainLover]

Have a look at this.

You will have to change things in order to set it up for your own requirements.

Thanks Paul for the kind words.

 

[namliam]

Have a look at this.

You will have to change things in order to set it up for your own requirements.

Thanks Paul for the kind words.

namliam What is

huh what? where ? when? why?

 

[RainLover]

namliam What is IMNSHO

Sorry about my Edit that I had to Edit

 

[RainLover]

Hello, Please help me!

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Private Sub Form_Load()
Dim RNos As String

RNo = DLookup("[RNos]", "Temp_Table")
Me.RID.SetFocus
Me.RID = Format(Date, "yymmdd") & Format(Val(Right$(RNo, 2) + 1), "00")
DoCmd.SetWarnings False
DoCmd.RunSQL "update Temp set Temp_Table.RNos = '" & Me.RID & "'"


End Sub
>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Could you please add some comments to explain what is happening, or what you would like to happen.

 

[RichP]

As noted by the others, my solution won't work well in a multiuser environment and you will need to use the RainLover stuff to make it work

 

[namliam]

namliam What is IMNSHO

Sorry about my Edit that I had to Edit

Google is your friend dear lover.... http://nl.urbandictionary.com/define.php?term=imnsho

The OP is trying to generate his own PK using the date in the format DDMMYY and appending a 2 didget number to the back of it generating a key from
14011701 to 14011799
for today

And now I see I need to eat my foot, he isnt using DDMMYY format, rather YYMMDD So nevermind my earlier comment

 

[nanscombe]

There was a similar thread on automatic numbering system where I came up with a function which would do the job you want.

It's based on the idea of using DMax + 1 but is configurable.

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String, Optional ByVal nisNumberOfZeros As Integer = 4) As Variant
    
    ' Function nextIdString() takes 3 / 4 parameters
    '  1 - (nisFieldName) The name of the field where the ID is found.
    '  2 - (nisTableName) The name of the table.
    '  3 - (nisPrefix) The prefix, if necessary, for the ID.
    '  4 - (nisNumberOfZeros) The number of zeroes, or numerals, to be used for the ID.

    ' nisPrefix & "0" gives you a default value if one is not found in the table
    nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")

    ' Get next numerical value by looking at the highest value number after the prefix and adding 1
    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1

    ' Create new ID string by concatenating the formated number to the prefix
    nextIdString = nisPrefix & Format(nextIdString, String(nisNumberOfZeros, "0"))
End Function

By feeding it a prefix based on the date as a yymmdd formatted string it would look for the largest matching value and give you an incremented version, ie if it found 14011713 it would return 14011714.

To generate the next number for the field "ID" in the table "tblNextIdTest" for the current day I would use a use event code thus.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strPrefix As String
    If Len(Me!ID & vbNullString) = 0 Then
        
        ' Function nextIdString() takes 3 / 4 parameters
        '  1 - (nisFieldName) ID
        '  2 - (nisTableName) tblNextIdTest
        '  3 - (nisPrefix) Format(Now, "yymmdd") - Todays date in the format yymmdd
        '  4 - (nisNumberOfZeros) 2 zeroes for the numbers 01 to 99
        '  Could be changed to 3 to give the numbers 001 to 999

        '  For 17/01/2014 IDs 140117xx (14011701 - 14011799) would be generated
        '  For 18/01/2014 IDs 140118xx (14011801 - 14011899) would be generated
        
        Me!ID = nextIdString("ID", "tblNextIdTest", Format(Now, "yymmdd"), 2)
    
    End If
End Sub

Take a look at the attached database and see what you think. I generated the following records with it.

ID		Text1
14011601	sdfas
14011602	sdf
14011603	sdg
14011604	sdf
14011605	sd
14011701	dsgasdg
14011702	sadfgasdg
14011703	asdfga
14011704	adfh
14011705	afdh
14011706	a
14011707	mbmbm

 

[wschian]

Thank for nanscombe! you solved my prolem

 

[nanscombe]

I'm glad it worked out.