Auto increment field in subform

[David Wright]

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

[puppydogbuddy]

Place the following code behind your form:

Private Sub Test_SrNo_Enter
If Not IsNull(Me!Test_SrNo) Then
     Me.Test_SrNo.Locked = True
Else
     Me.Test_SrNo.Locked = False  
     Me.Test_SrNo = Nz( DMax( "[Test_SrNo]", "tblTests" ), 0 ) + 1
End If
End Sub
 

[David Wright]

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.

[puppydogbuddy]

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.

[David Wright]

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

[puppydogbuddy]

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?

'Private Sub Form_Current()
'DoCmd.RunCommand acCmdRecordsGoToLast
'End Sub