Using Access 2003, I need to create an automatic Serial Number.
Example "7235-E001"
"7" is current year
"235" is day
"-E" is static
"001" is sequencial, restarting at "001" each day
I set a table field with "=right(Format( Now(),"yy") & Format(Format(N ow(),"y"),"000" ),4)" to create the year/day number.----I should be able to add &"-E"& to add the static "-E"----I tried to add this all together with the "autonumber ", but that obviously failed since the autonumber will not reset each day. Also, I lost the placeholders on the sequencial number "001".
I have little to no knowledge of VB, and if possilbe, would like to keep all expressions regulated to queries and tables and forms. If not possible, I will see what I can understand.
Please keep in mind, I am a complete noob, so speak to me like I am an idiot. Thank you.
16 18275 ADezii 8,834
Recognized Expert Expert
Using Access 2003, I need to create an automatic Serial Number.
Example "7235-E001"
"7" is current year
"235" is day
"-E" is static
"001" is sequencial, restarting at "001" each day
I set a table field with "=right(Format( Now(),"yy") & Format(Format(N ow(),"y"),"000" ),4)" to create the year/day number.----I should be able to add &"-E"& to add the static "-E"----I tried to add this all together with the "autonumber ", but that obviously failed since the autonumber will not reset each day. Also, I lost the placeholders on the sequencial number "001".
I have little to no knowledge of VB, and if possilbe, would like to keep all expressions regulated to queries and tables and forms. If not possible, I will see what I can understand.
Please keep in mind, I am a complete noob, so speak to me like I am an idiot. Thank you.
I could only spend a very short time on this, but this Function should properly generate the next Serial Number per your specifications. There are only a couple of simple assumptions:- Your Table Name is tblTest, make the appropriate substitution if necessary.
- The [SerialNo] Field within this Table is called [SerialNo].
- The Day component you refer to in the Serial Number is a Julian Date (number of Days from the first of the year).
- Any questions, feel free to ask.
- Public Function fGenerateNextSerialNumber()
-
Dim strCurrentYear As String
-
Dim strCurrentDay As String
-
Dim strStaticValue As String
-
Dim strSequentialNo As String
-
Dim strLastSerialNo As String
-
Dim strLastSequentialNo As String
-
Dim strNextSequentialNo As String
-
-
strCurrentYear = Right(Year(Now()), 1)
-
strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1
-
strStaticValue = "-E"
-
-
'get ready to extract the Sequential Number
-
strLastSerialNo = DLast("[SerialNo]", "tblTest") 'produces 7235-E001
-
strLastSequentialNo = Right(strLastSerialNo, 3) 'produces 001
-
-
'Generate the Next Sequential Number
-
strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "000") 'produces "002"
-
-
'Generate the next, Unique, Serial #
-
fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo
-
End Function
This is kind of what I was afraid of. I have no idea where I would put the VB in the database.
ADezii 8,834
Recognized Expert Expert
This is kind of what I was afraid of. I have no idea where I would put the VB in the database.
Place =fGenerateNextS erialNumber() in the Default Value Property of the [ Serial Number] Field. Anytime you add a New Record, the results of this Function will be placed in this Field.
Roger, I will have access to the database tomorrow morning, I will try at that time, thank you. I'll reply back whether I manage or not.
ADezii 8,834
Recognized Expert Expert
Roger, I will have access to the database tomorrow morning, I will try at that time, thank you. I'll reply back whether I manage or not.
Just make sure to substitute your Table name for tblTest and your Field Name for SerialNo or else the code will not work.
To see if I could get this to work, I created a new table. I named the
table "tblTest. I entered 3 fields. Field 1 - ID (autonumber), Field 2
-
SerialNo (no format), Field 3 - data (no format).
I then went to the VB (Tools/Macro/Visual Basic Editor). I entered the
code as typed. When I opened the VB, it had autopopulated "option
compare database" on the top line, so I left that information in, and
placed the new code below it.
When I added =fGenerateNextS erialNumber into the default data for the
"SerialNo" field, the =fGenerateNextS erialNumber changed to read
="fGenerateNext SerialNumber".
I then changed the table to "datasheet view" and added data to the
"data" field, but the "SerialNo" field populated with
"fGenerateNextS erialNumber".
I went back to VB and deleted "option compare database", but the
"SerialNo" field still populates with "fGenerateNextS erialNumber" when
I add data to the "data" field.
Am I putting the code in the right place, or should I have formated the
"SerialNo" field somehow?
Hopefully I explained well enough.
Thank you.
ADezii 8,834
Recognized Expert Expert - In Form Design View.
- Right Click on the [SerialNo] Field.
- Select Properties.
- Select the Data Tab.
- In Default Value, enter =fGenerateNextS erialNumber()
My apologies for not getting back to you in a timely manner, things have been hectic. I made the corrections that you pointed out. Now the database is giving me an error on the
strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), , Now()) + 1
line. When opening the serial number table, I receive "Compile error: Arguement not optional: warning, and it highlights the above mentioned script line.
ADezii 8,834
Recognized Expert Expert
My apologies for not getting back to you in a timely manner, things have been hectic. I made the corrections that you pointed out. Now the database is giving me an error on the
strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), , Now()) + 1
line. When opening the serial number table, I receive "Compile error: Arguement not optional: warning, and it highlights the above mentioned script line.
It seems as though you inadvertantly added another comma, thus the Error. The correct syntax is: - strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tony Clarke |
last post by:
Hi,
I'm trying to write a system thats used for about 50 clients that uses html
forms and php to log details. The problem is that when a client loads the
form page it's given a value which is the last record in a table +1 (i.e. so
its the next record). The problem with that is that a client could sit on
that page for 20 mins (or whatever length of time) and a different client
could use that record number and there what be an error...
|
by: Nitin |
last post by:
Hello,
We are trying to figure out how to get a unique identifier for a machine.
Our application is a C# windows application that talks to our server via a
webservice. Every time our webservice is called, we want to verify that the
user has a valid license. For that we want to identify the user via his/her
machine. I tried using the following
searcher = new ManagementObjectSearcher("SELECT * FROM Win32_DiskDrive");
|
by: UJ |
last post by:
Is there any number I can get that is truly unique for a computer that can't
be changed? I want to have a system whereby I have a computer that accesses
a web service based on some unique value that can't be changed. Or at least
if it is changed, it's not changed frequently. (IP address won't work
because that can change frequently).
Here's my thought - the program boots up, finds some unique piece of
information, sends that off to the...
|
by: expect |
last post by:
Hello,
Trying to get this MySql create table command to work, no luck.
create sequence serial;
CREATE TABLE outbound (
source char(100) default '',
destination char(100) default '',
sport int4 default 0 NOT NULL,
|
by: Yavuz Bogazci |
last post by:
Hi, we are developing an winforms application for task management. We want
generate a serial number wich includes Username, Company Name and another
Information as String. How can i create from these values a serial number?
thanks
yavuz
| |
by: BRINER Cedric |
last post by:
Synopsis
CREATE { TEMPORARY | TEMP } ] TABLE /table_name/ (
{ /column_name/ /data_type/ ]
| /table_constraint/ }
)
) ]
...
...
|
by: JJ |
last post by:
Now I know this question has been asked many times, but I cannot seem to
find a good site which summarises the methods possible in vb .net.
I am after a way of producing a unique serial number for my app. The program
would produce a unique identifier for the computer, then I would psovide the
user with a serial number unique to their machine.
In other words I would like to check for things like:
i) Hard disk (not volume) serial number...
|
by: Arne Beruldsen |
last post by:
I have a windows app using vb.net 2005.
I would like to create a serial number based on a couple of characteristics
of the customers computer (computer name, HD number..etc). I don't need
anything complicated just a way to generate a 16 character number.
Is there an easy way to do this...?
Thanks
|
by: Paul Bromley |
last post by:
Ok - I have given up on trying to find the active IP address for a given PC.
For licensing purposes I need to retrive a unique identifier from the PC
that the program is installed on. The Hard disk serial number would do fine,
but on Googling it seems that some of the sample code will now laways work
on W2K, and not without administrator rights.
Hence my question - how can I consistently get a UNIQUE PC identifier using
vb.net 2003 - not...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |