473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 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.

Example:
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.

HELPFUL DETAILS
Main Form frmRECORD_UPDATES
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 8043
puppydogbuddy
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
  9.  
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

GOING BACK A LITTLE
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.)

Experimenting:
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
puppydogbuddy
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
puppydogbuddy
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
  4.  
Jan 23 '09 #6

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

Similar topics

2
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...
0
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)...
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...
9
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 :)
2
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.
0
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...
0
chumlyumly
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...
11
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...
1
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.