473,586 Members | 2,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Auto increment field in subform

11 New Member
Hello Everyone

I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the incremented number would help to reduce errors and make data entry simpler. The next record in the Test_SrNo field should not have any entry in it until the text insertion point enters the Test_SrNo field. Example: If the last test serial number recorded for the record number 3000 was 2 then, when the text insertion point enters the Test_SrNo field, the next number required, 3, needs to be automatically entered.

Appliance No
3000 (ONE of the one to many relationship)

Test Serial Number (MANY of the one to many relationship)
1 Auto Entry
2 Auto Entry
3 Auto Entry Auto Entry when receiving focus

The relationship is between App_No field.

Derived from a table tblLog
Primary Key of tblLog App_No (Number - long integer)

Details of the Subform
Subform Caption tblTests subform2
Subform is derived from a table tblTests
P.K. Field in tblTests is called App_No (Number - Long integer)
P.K. Field in tblTests is called Test_SrNo (Number - Long integer)
I would like this field to automatically increment by one each time a new record is entered into the subform.

The primary key of tblTests is a combination of the
App_no field and the
Test_SrNo field

Thank you to anyone who is kind enough to help. DAVID
Jan 21 '09 #1
5 8081
1,923 Recognized Expert Top Contributor
Place the following code behind your form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Test_SrNo_Enter
  2. If Not IsNull(Me!Test_SrNo) Then
  3.      Me.Test_SrNo.Locked = True
  4. Else
  5.      Me.Test_SrNo.Locked = False  
  6.      Me.Test_SrNo = Nz( DMax( "[Test_SrNo]", "tblTests" ), 0 ) + 1
  7. End If
  8. End Sub
Jan 22 '09 #2
David Wright
11 New Member
Hello Puppy Dog Pal

Thank you for responding to my enquiry. I had the following code in my ON CURRENT event property before I added your code:

Private Sub Form_Current()
DoCmd.RunComman d acCmdRecordsGoT oLast
End Sub

I added your code after this code. It may be that my code could have affected the results of your code. Your code did work but instead of providing me with a list e.g
Record Test SrNo Pretend Record Info
1 1 Black Car
2 Red Car
3 Yellow Car
It changed Test SrNo 1 to 2 when I tried to add another record. Then it changed record No 2 to number 3 when I tried again. When I looked at the tests table I had e.g.

Record Test SrNo Pretend Record Info
1 3 Black Car

Having recorded Record No 1, Test SrNo1, I went to the next record, record No 2. The test SrNo was already set to SrNo 2. (The highest Number that has not yet been used in the Tests table, SrNo field, is recorded as the start number for the next record.)

I applied ‘Auto Number’ to the Test SrNo field which did provide a unique number and it did not overwrite the previous number. Auto number does work but when I enter numbers in manually, (i.e. before applying auto number) I can record Record Number 1 Test SrNo 1, 2, 3 and Record Number 2 – Test SrNo 1,2, and Record Number 3 – Test SrNo1,2,3,4,5. This is the best format but it is not automated. Using Auto Number will ever increase the size of the last number used which will eventually make the SrNo Field wider. It is not quite such a pleasant visual method as using sequential numbers like 1,2,3 although it will work as the numbers are unique.

If you have any further suggestions I would welcome them. Thank you for the time you spend helping perfect strangers. It is very, very kind of you and greatly appreciated. David.
Jan 22 '09 #3
1,923 Recognized Expert Top Contributor
Hi David,
1. Try commenting out the form_curent code and see what happens.
2. I set the code to generate a number for you if you entered the field and it is null. It was designed for adding a new record, not editing existing records. If the field in existing records are blank, that needs to be addressed as a system startup problem handled directly thru the table via a one-time bulk update (query or manual entry). Envision that you are entering your first and subsequent records with my code in place.
Jan 22 '09 #4
David Wright
11 New Member
Hello Puppy Dog Buddy

I was not sure what you ment by 'comment out the form_current code'.

I deleted my:

Private Sub Form_Current()
DoCmd.RunComman d acCmdRecordsGoT oLast
End Sub

and inserted your code without any event. I hardly know anything about sql.
I still encountered problems.

I am content with auto number; it will suffice.

Thank you, kind regards David
Jan 23 '09 #5
1,923 Recognized Expert Top Contributor
Hi Dave,
Commenting out the code means making the code an unexecutable comment by placing a comma as the first typed character as you do for all comment lines (see below). This way, if you need to activate the code again, you don't have to retype.

what kind of problems are you having?

Expand|Select|Wrap|Line Numbers
  1. 'Private Sub Form_Current()
  2. 'DoCmd.RunCommand acCmdRecordsGoToLast
  3. 'End Sub
Jan 23 '09 #6

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

Similar topics

by: Kajol | last post by:
Hi All, I am using server version: 4.1.11-nt on windows 2000. I want to create a table with ID as auto increment and initialise it with a starting value of 1000. I am trying to do following DROP TABLE IF EXISTS `demo`;
by: Santosh | last post by:
Hi, I have a requirement in which I need to create an auto increment column in a file which will be unique. The following is what I am trying to do. 1) I need to use DDS to define the file 2) The auto-increment field can go upto a 5 digit numeric value. 3) As the records from this file will be deleted at regular intervals.I need the ability...
by: Tom | last post by:
I am trying to store information into a table that has an auto increment field. There is currently no data in the table. Using the code below I cannot insert data into the table. I get an error telling me that "Number of query values and destination fields are not the same." If I add a value for the auto increment field to the SQL String...
by: Eitan M | last post by:
Hello, How can I find the last indentity of auto-increment field ? (If I inserted a record, then the auto-increment field is build automatically, and I want to find its value). Thanks :)
by: gomzi | last post by:
hi, I have an auto-increment field. I would like to know as to how I could reset it back to zero. thanks, gomzi.
by: Michele 'xjp' | last post by:
Hi, I need a data structure (better if in STL) what give me: - an auto generated auto-increment integer field ('id') - various string fields This structure data must be searchable: I must be able to search any of the fields using a string (or a int if I search the 'id'). This is equal to a record in a database, with the searchable...
by: chumlyumly | last post by:
Hello scripters - OS: Mac OSX Language: PHP w/ MySQL database I've created an insert page where a user inputs his info, which then goes to four different tables in a MySQL database. The tables are all linked with the field 'member_id', which is an auto-increment field in the parent table ('members'). I've been able to input multiple...
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...
by: Formula | last post by:
Hi, i'm working on my first project on asp.net using c#. I'm adding a couble of fields to database but i need the primary key of the master table to add it to sub table. The database contains 2 tables : Question Table :questionID(primary key and identity) , questionInText
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...
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...
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...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
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...
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...
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...
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
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.