CF form to Access: auto fill date and numbering

vmparelan wrote: 1. Change the current date field to auto fill the date into the db. 2. Have an invisible field that enters the current year into it's own column. 3. Have each entry follow a progressive numbering system that resets on January 1st each year.

Use cfform. Implement the requirements as follows:

1) To enable the user to automatically fill the date field, use <cfinput type="datefield"> or <cfcalendar>;

2)** Add the column currentYear to the database table, if it doesn't yet exist. Include the following field in the form:

<cfinput type="hidden" name="curYear" value="#year(now())#">. On the action page of the form, you will insert the value of form.curYear into the currentYear column;

3) Place the following code on the action page of the form

<!--- 

Assumptions:

i) The required index is stored in the id column;

ii) The maximum number of transactions saved per year runs into 3 digits. That is, up to a maximum of 999. The IDs are therefore saved as 2011001, 2011002, ..., and so on, up to a maximum of 2011999. When the year changes to 2012, the ID will begin at 2012001.

If the number of annual transactions is higher, use a scaling factor higher than 1000, say 10000. For fewer transactions, use a scaling factor of, say, 100. 

--->

<cfquery name="getId">

select max(id) as max_id

from tbl

</cfquery>

<cfset maxYearFromDB = left(getId.max_id, 4)>

<cfset currentYear   = year(now())>

<cfif maxYearFromDB EQ currentYear>

    <cfset id = getId.max_id + 1>

<cfelse>

    <cfset id = currentYear * 1000 + 1>

</cfif>

Next, your insert query should then include this id value.

** [Added remark on 2)]

Reconsider the requirement of passing the value of the current year as a hidden form field. The reason should not only be to save it. There are more efficient ways to save the current year, for example, by using a database function in the insert query.