472,374 Members | 1,551 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

Auto increment field in subform

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 7892
1,923 Expert 1GB
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
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.RunCommand acCmdRecordsGoToLast
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 Expert 1GB
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
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.RunCommand acCmdRecordsGoToLast
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 Expert 1GB
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...
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)...
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...
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...
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...
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...
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...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

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.