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").Valu e = E
Text1.Text = Xrs.Fields("QNo ")
====== Code Ends Here ===========
Regards,
Hiren
13 12055
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 1,445
Recognized Expert Top Contributor
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
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
Thanks Veena,
I will try this as well today and Let both of you and Debasis know the outcome tomorrow....
Thanks Once again
Hiren
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, adLockOptimisti c
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 1,445
Recognized Expert Top Contributor
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
TNO = 1
NextNum = "-001"
its not increasing TNO
QVeen72 1,445
Recognized Expert Top Contributor
Hi,
Keep a Break point and check from the beginnig.. What Database?
REgards
Veena
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Laphan |
last post by:
Hi All
This is a strange request, but I just cannot fathom how to do it.
In theory the requirement is very basic, but in practise its a noodle!!
I have 10 team names like so:
Team A
Team B
|
by: Sebastien |
last post by:
Hi,
I am building a products database, linking sales and production. Each
part has a unique sales Stock Code and Production Number.
The sales stock code is a combination of letters and numbers taken
from the part name (BrandA 300 4 Bladed : B3004B). The production
number is a 4 or 5 digits number with first figure(s) indicating
category (BrandA 300 4 Bladed: BrandA is category 1000. This Product
would be 1003 if it was the third...
|
by: SalimShahzad |
last post by:
Dear Gurus,
i had written following codes to auto generate the next claim no
Private Const strC = "GCT/02/J/"
Private Sub Command1_Click()
Dim stra, stre As String
Dim intb, intd As Integer
Dim strd As String
stra = "200"
|
by: Bill Rust |
last post by:
I've created an "Add Item" wizard for VB.NET 2003 that allows a user to add a
specialized class that works with my application framework. In the wizard,
the user can select the interfaces they would like to support. During the
code generation phase, I add an "Implements Ixxx" for each interface they
select, but I've not yet figured out how to add the skeleton implementation
for those interfaces. Once the user opens the class in the VS...
|
by: gwhite1 |
last post by:
I am using the auto-generate fields option because my query can be
different based on what the user selects. But I would still like to set
the column alignment but I can;t seem to access the columns in code
after the data has loaded. Any ideas?
| |
by: G |
last post by:
Hello Firends
I want a sample code for developing an " auto genearte number " and want to
store in database.
Please if you find any code please send
Thanks in Advance
G
|
by: ganesh22 |
last post by:
hi...
I am doing a project asp.net with C#.my requirement I want to do auto generate password for my application that means when user forget his password when he clicks a link it will auto generate a new password and then it will send to a secondary mail
|
by: DanielLauJJ |
last post by:
When inserting a record into a table, I want SQL Server to generate a
number automatically for the Primary Key. (e.g. OrderID is 1, 2, 3
and so on) How to do it?
(This behavior is similar to the "Auto-number" in Microsoft Access)
http://raymondlamsk.blogspot.com/2008/03/faq-ms-sql-server-how-to-generate_24.html
|
by: Jagdish Patil |
last post by:
I have project in asp.net with sql server backend.
i want to auto generate a number for particular column.
with the auto generated number i want to insert some other data in the same row same table. on button click event.
details
railway PNR no.:- want to autogenerat
passenger details:- want to inserted simultaneously
pls help me
jagdish_contact27@yahoo.co.in
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |