Incrementing serial number in a form

  • Thread starter Thread starter Hulo
  • Start date Start date
H

Hulo

There is a field named "serial_no" in a table called "tbl_register" which
will get incremental values like 1, 2, 3 etc. for each record. The numbering
will start from January each year and run serially till December. On January
next year, the number will re-start from 1. There is a "serial_date" field
corresponding to the serial_no which enters the current date on the date of
entry.

Now, I want the form through which I enter data, to display the serial
number automatically in the "serial_no" box of the form. Every time I add a
new record, the form should show a serial number one more than the
previously entered number. But when I enter the first data of the new year
(not necessarily on January 1 ... it may be anytime in January or even in
February etc.), the number should reset to 1 again.

Any idea how to do it?

Thanks in advance.
 
Hulo said:
There is a field named "serial_no" in a table called "tbl_register" which
will get incremental values like 1, 2, 3 etc. for each record. The numbering
will start from January each year and run serially till December. On January
next year, the number will re-start from 1. There is a "serial_date" field
corresponding to the serial_no which enters the current date on the date of
entry.

Now, I want the form through which I enter data, to display the serial
number automatically in the "serial_no" box of the form. Every time I add a
new record, the form should show a serial number one more than the
previously entered number. But when I enter the first data of the new year
(not necessarily on January 1 ... it may be anytime in January or even in
February etc.), the number should reset to 1 again.
Click to expand...

In the BeforeUpdate event of the form...

If IsNull(Me.serial_no) = True Then
Me.serial_no = Nz(DMax("serial_no", "tbl_register", "Year(serial_date) =
Year(Date())"),0) + 1
End If
 
Back
Top