help on assigning a field to the next numeric value

[help123]

I would like to create a function "nextvalue" and assign the value returned to a field (Case_no). Based on the current year, this function will query a table(Scap_Case_Logs)to find the last number assigned in case_no and add 1. For example, the function would return 4 based on the folowing:

1999 1
1999 2
1999 3
2000 1
2000 2
2000 3

The function returned the number 4 because the current year is 2000 and the next value 4.

I am not an advanced programmer so please help with this function. The following is what i have so far.

Function nextval() As Integer
' Returns True if the specified form is open in Form view or Datasheet view.

Dim dbsScap As Database
Dim sql_stmt As String
Dim rec_set As Recordset
Dim tmp_num As Integer
tmp_num = 0


Set dbsScap = CurrentDb
Set rec_set = dbsScap.OpenRecordset("SCAP_CASE_LOGS&quot
' Set rec_set = dbsScap.OpenRecordset("SELECT max(case_no) max_case_no FROM SCAP_CASE_LOGS", dbOpenDynaset, dbReadOnly)

With rec_set
' Enumerate records.
If tmp_num < .CASE_NO Then
tmp_num = .CASE_NO
End If
.Close
End With

dbsScap.Close


nextval = tmp_num + 1
' nextval = 10

End Function

 

[MichaelRed]

See the FAQ. Ms. Access Tables and relationships. RE Autonumber. It is what you want to do, just some minor differences.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[help123]

oh, one more thing when the year 2000 rolls over to 2001, the counter has to start to one again.

any suggestions...please help

 

[MichaelRed]

Read the FAQ. Review the code presented. The sample code resets the counter on a monthly basis. Simple change to do it on annual basis, may nees to change other stuff if the record count / interval gets very large.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over