473,554 Members | 3,281 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A table with Auto number generation field

2 New Member
Hi all,

I’m trying to create a form with 15 fields that generates an auto number in one of those 15 fields. The auto generated number which should display like this – “081-2008-00001” “081-2008-00002” “081-2008-00003” and so on.
The first 3 characters of those numbers are pulled in from one of the field, namely “Building #”. The second set of 4 digit numbers are nothing but the current year which gets pulled from another field namely “Current Date” in the mm/dd/yyyy format (I need to pull only the last 4 digits of the date which is the year).
The Last 5 digits are auto generated numbers assigned by the system.
How do get this logic working for me?
Also, there are several other records which may have different building numbers (for ex. 41, 43, 50, 55, 75, 78, and so on). The last 5 digit auto generated numbers could be consecutive but the first 3 digits should be pulled from the “building #” field and the next 4 from the “current date” field.

Please help!

Thank you,

Irving Guy
Apr 10 '08 #1
2 2512
Stewart Ross
2,545 Recognized Expert Moderator Specialist
Hi. The most useful way to generate a representation of a compound key (one that combines a number of key fields together to form a unique combined ID) is to create a query with all the fields from the table, then add a calculated field to the query. The calculated field can show the fields you mention combined together in the format you require.

The calculated field would be along the lines of:
Expand|Select|Wrap|Line Numbers
  1. CompoundKey: Format([Building#], "000") & "-" & Year([Current Date]) & "-" & Format([autonumber field], "00000")
On your form you then can add the new CompoundKey field, setting its properties to Enabled=No and Locked=Yes to prevent users from trying to change its (non-changeable) value.

I am assuming that you already have an autonumber field in your table. If you don't, you can create a numeric field of type Long and increment that, setting its default value as:
Expand|Select|Wrap|Line Numbers
  1. Nz(DMax("[long field]", "[your table name]"), 0) + 1
Autonumber fields have some properties that are considered unsuitable for use where the number is supposed to mean something to those who might see it. For example, autonumbers end up not entirely consecutive (if, for instance, a record addition is cancelled the autonumber is still incremented and the number that was generated is skipped).

-Stewart
Apr 10 '08 #2
Irving Guy
2 New Member
Stewart,

I'll try it out and let you know if that works! Thanks for your time and help!

Irving Guy
Apr 11 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
3842
by: Ram | last post by:
Hi, I am trying to set up a DB for a race series where a riders best 6 of 10 rides count towards a league position. I have a table of Riders ( say 300 ) I want to use a form to select which riders from the Riders Table are taking part and create a new Event Table ( say Round 1 ) max of 120 riders per event.
3
1865
by: John Baker | last post by:
Hi: At the outset let me admit that I screwed up! I have built a rather elaborate set of forms and sub forms starting with a client table, and going down to PO and Line item. This works very well EXCEPT that when I started the design I failed to consider that client records would be added to the client table in random order. The table is...
2
3071
by: Dragon | last post by:
When I run a make-table query, the results take over 1 minute to return. The query is making a table that has 12,000 records in it. If I run the make-table query ONLY to see the records that will be put into the table, this only takes 5 seconds. It's the making of the table that takes so long. Does anyone know what I should do to improve...
2
7967
by: Greg Strong | last post by:
Hello All, Is it possible to change table field lookup properties in code? I've been able to change other field properties in code, however so far no luck with field lookup properties. What I've done for test purposes is use a text input file for the table field lookup properties. I thought that I'd start first by just changing the...
3
2768
by: vonclausowitz | last post by:
Hi All, I was thinking of creating a table in my database to index all words in the database. That way I can quickly search for one or more words and the index table will return the words and records I need. For example the iTable would look like this:
1
2351
by: daniellee2006 | last post by:
I am creating a basic website to store people profiles and within this website i have a page that creates a table dependent on the number of records in mysql written in PHP within these tables they show images i need these images to hyperlink to other HTML pages... Note these tables are written within the <?PHParea of the full Script
5
17671
by: joshua.nicholes | last post by:
I have an access database that consists of two tables.A data collection table and a species list table. The data collection table has about 1500 records in it and the species list has about 600. The species list has 7 fields the first is a four digit unique identifier (species) it is set as the primary key. I have created a relationship to...
8
12503
by: Wayne L | last post by:
I have searched and could not find my solution to my problem. I have a table that has a field that I want to start at 0001 and go to 1999. Once it reaches 1999 I want the field to reset to 0001. I am importing unique records and the date and number separates the records but some days there may be more than one import. So if I set the field to...
11
4054
by: ZaphodBBB | last post by:
Hi I have a table that has as its Primary Key the Auto-Increment Field: Equipment_Number. In M.S. Access is there any way to define a starting number for the field? OR another way in which I could save the same problem would be: I have a form frmAddNew_Equipment, once all the fields are filled in correctly and it has been verified...
0
7519
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...
0
7798
Oralloy
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. ...
1
7560
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...
0
5152
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...
0
3556
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...
0
3545
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2015
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
1
1130
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
838
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.