Skip navigation
vmparelan
Currently Being Moderated

CF form to Access: auto fill date and numbering

Nov 18, 2011 1:45 PM

I have a very simple CF form that processes to an Access 2007 database. My boss would like me to add a few things:

 

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.

 

My ColdFusion knowledge is rudimentary at best, I've combed through forums and Google searches and the solutions I've found either don't fit the specifications, or get into advanced concepts I don't know how to apply yet. Can someone explain it simply, or is it not possible without getting into masks and loops I don't know how to use?

 
Replies
  • Currently Being Moderated
    Dec 18, 2011 4:14 AM   in reply to vmparelan

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points