Auto Generate Custome Number While Saving (Vb 6.0 and MS Access)

[Hiren Joshi]

Hi All,

VB 6.0 and MS Access

While Saving Record, I want Number to be Generated in following Format

140208-001, 140208-002, 140208-003 . . . etc...

You must have noticed that 140208 is today's Date and After the - Last 3 digits are number.

Now what I want is, lets say If i save 87 Records today it should be 140208-087 and next day (tomorrow) numbers should be like 150208-001, 150208-002 etc.....

Please let me know if it possible then how? I m using the following code for Auto Generation of the Number but in different format.

=========My Code Goes Here =========

Dim C, E As String
Dim D As Integer
C = "ABCDEF"

If Xrs.RecordCount = 0 Then

D = 1

Do While Not Xrs.EOF
If Xrs("QNo") > D Then

End If

Xrs.MoveNext

D = D + 1

Loop

If D >= 1 And D < 10 Then
E = C & "0000" & D
Text1.Text = E
Else
If D >= 10 And D < 100 Then
E = C & "000" & D
Text1.Text = E
Else
If D >= 100 And D < 1000 Then
E = C & "00" & D
Text1.Text = E
Else
If D >= 1000 And D < 10000 Then
E = C & "0" & D
Text1.Text = E
Else
E = C & D
Text1.Text = E
End If
End If
End If
End If

Xrs.AddNew
Xrs("QNo").Value = E

Text1.Text = Xrs.Fields("QNo")

====== Code Ends Here ===========

Regards,

Hiren

[debasisdas]

For the first part use system date . You just need to format the date.

For the second part findout the number of records inserted in that day .You number should be +1.

Next you need to format it as desired.

You need to Store a date field in your table.

[QVeen72]

Hi,

Code Something like this :

Dim NextNum As String
Dim TNo As Integer
Dim RST As New ADODB.RecordSet
Dim sSQL As String
NextNum = Format(Date, "ddmmyy")
sSQL = "Select Max(MyField) From MyTable Where MyField Like '"  _
    & NextNum & "*'"
RST.Open sSQL,Conn,adOpenStatic,adOpenLockReadOnly
TNo = 0
If Not RST.EOF Then
    RST.MoveFirst
    sSQL = Trim(RST(0) & "")
    If Trim(sSQL) <> "" Then
        TNo = Val(Right(sSQL,3))
    End If
    RST.Close
End If
TNo = TNo +1
NextNum = NextNum & "-" & Format(TNo,"000")
 

Regards
Veena

[Hiren Joshi]

Yes I thought of the same thing, but having confusion of how to Increment Daily Records... I mean how to check If today it is the first record or 5th or 87th?

I am going to try it today, if you can give me any suggestion on above will be helpful.

Thanks

[Hiren Joshi]

Thanks Veena,

I will try this as well today and Let both of you and Debasis know the outcome tomorrow....

Thanks Once again

Hiren

[Hiren Joshi]

Ok I tried it, but what's happening is I am able to save first record of the day, then while saving second record it gives duplicate value error as this no. in the table is a primary key.

I managed to save 1 record yesterday 140208-001 and 1 record today 150208-001, but I am not able to save second record in the same day.

Here is my code

======== My Code (VB) ===========

Dim NextNum As String
Dim TNo As Integer
Dim RST As New ADODB.Recordset
Dim sSQL As String
NextNum = Format(Date, "ddmmyy")

sSQL = "Select Max(TestNo) From Test Where TestNo Like '" _
& NextNum & "*'"
RST.Open sSQL, Con, adOpenStatic, adLockOptimistic

If Not RST.EOF Then
RST.MoveFirst
sSQL = Trim(RST(0) & "")
If Trim(sSQL) <> "" Then
TNo = Val(Right(sSQL, 3))
End If
RST.Close
End If
TNo = TNo + 1
NextNum = NextNum & "-" & Format(TNo, "000")

======== Code Ends =============

I am getting Following Error:

"The Changes You requested to the table were not successful because they would create duplicate values in the index, Primary key or relationship....."

[QVeen72]

Hi,

Keep a BreakPoint and check at the end of the above Code what is the value of variable "NextNum "....? and "TNo" ...?
also check what is RST(0)...

What is your Backend Database...?
If SQLServer or Oracle , Replace * With % in SQL Statement..

Regards
Veena

[Hiren Joshi]

TNO = 1
NextNum = "-001"

its not increasing TNO

[QVeen72]

Hi,

Keep a Break point and check from the beginnig.. What Database?

REgards
Veena

[Hiren Joshi]

Database is MS Access 2003... Whats happening is, For Every record of the same day it doesnt pass No.2, TNO is always 1 and thats why its giving problem.

I tried through Break Point using stop command.

It doesnt increment TNO.... Always Makes TNO 0+1 = 1

Regards

Hiren

[QVeen72]

Hi,

VB6 or VBA...?
My Code is for VB6.. not sure it will work for VBA or not..
what is RST(0)..?

REgards
Veena

[Hiren Joshi]

I am using VB 6 as well...

There is something very small we are missing out.... I have tried all possibilities...

if you are on Yahoo or MSN, Can you please add me on your messanger so that it will be faster for us to communicate... My ID ismail id removedin both.

Regards

Hiren

[Hiren Joshi]

Veena,

I have mailed u the code and table please check your mail

Hiren

[debasisdas]

Veena,

I have mailed u the code and table please check your mail

Hiren

Now you check your PM.