how to add ID number in new record automatically.

[Guest]

When I add a new record.
1) I want to increase +1 in my ID code number when I add a new record. Ex.
the first record code number is 49001 . then if i add a new record the
ID_code_number textbox will show 49002 automatically.
2) In the newly year January 1st, 2007 . The ID_code_number in a new record
can detect that newly year so the ID_code_number will show as 50001 then
50002 in the new record

pls. help me to code for that work.

 

[Guest]

Sumeth,

Try this:

Private Sub Form_Current()

Dim myLast As Long
Dim myBase As Long

'Only do this test for a new record.
If Me.NewRecord Then

'Get last record number.
myLast = DMax("[ID_code_number]", "yourTable")
'Compute base value for current year.
myBase = Year(Date) + 47993

'If base value is greater than last value, a new year
' has started.
If myBase > myLast Then
'Replace last value with base value for new year.
myLast = Year(Date) + 47993
End If

'Add 1 to the value determined.
Me.ID_code_number = myLast + 1
End If

End Sub

Best,
Bruce

 

[John Vinson]

When I add a new record.
1) I want to increase +1 in my ID code number when I add a new record. Ex.
the first record code number is 49001 . then if i add a new record the
ID_code_number textbox will show 49002 automatically.
2) In the newly year January 1st, 2007 . The ID_code_number in a new record
can detect that newly year so the ID_code_number will show as 50001 then
50002 in the new record

pls. help me to code for that work.

Unless this is *really* needed (say for compatibility with a
longstanding manual system), I'd discourage using such a composite,
non-atomic key. Several problems! What if some year you finally have
1000 records? Can't happen? Never say never!!!

Assuming that you can live with that... you might be able to use code
like this in a Form's BeforeInsert event (yes, you must use a Form,
tables don't have any usable events). Air code, untested; assuming
that the field is a Number/Long Integer field named ID, in table named
yourtable, displayed in a Form in a textbox named txtID:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngID As Long
Dim lngStart As Long
Dim strSQL As String

' get the starting number for the current year
lngStart = 1000 * (Year(Date) - 1957)
strSQL = "SELECT Max([ID]) FROM yourtable WHERE [ID] > " & lngStart
Set rs = db.OpenRecordset strSQL, dbOpenSnapshot
If rs.RecordCount = 0 Then ' first record of new year
Me!txtID = lngStart + 1
Else
' add one to the existing maximum
Me!txtID = rs!ID + 1
End If
rs.Close
Set rs = Nothing
Me.Dirty = False ' save the record to disk with the new ID
End Sub


John W. Vinson[MVP]

 

[Guest]

Thanks BruceS and John Vinson,
I try with your code and it can work.
thanks for your help.

Sumeth

When I add a new record.
1) I want to increase +1 in my ID code number when I add a new record. Ex.
the first record code number is 49001 . then if i add a new record the
ID_code_number textbox will show 49002 automatically.
2) In the newly year January 1st, 2007 . The ID_code_number in a new record
can detect that newly year so the ID_code_number will show as 50001 then
50002 in the new record

pls. help me to code for that work.

Unless this is *really* needed (say for compatibility with a
longstanding manual system), I'd discourage using such a composite,
non-atomic key. Several problems! What if some year you finally have
1000 records? Can't happen? Never say never!!!

Assuming that you can live with that... you might be able to use code
like this in a Form's BeforeInsert event (yes, you must use a Form,
tables don't have any usable events). Air code, untested; assuming
that the field is a Number/Long Integer field named ID, in table named
yourtable, displayed in a Form in a textbox named txtID:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngID As Long
Dim lngStart As Long
Dim strSQL As String

' get the starting number for the current year
lngStart = 1000 * (Year(Date) - 1957)
strSQL = "SELECT Max([ID]) FROM yourtable WHERE [ID] > " & lngStart
Set rs = db.OpenRecordset strSQL, dbOpenSnapshot
If rs.RecordCount = 0 Then ' first record of new year
Me!txtID = lngStart + 1
Else
' add one to the existing maximum
Me!txtID = rs!ID + 1
End If
rs.Close
Set rs = Nothing
Me.Dirty = False ' save the record to disk with the new ID
End Sub


John W. Vinson[MVP]