• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CF form to Access: auto fill date and numbering

New Here ,
Nov 18, 2011 Nov 18, 2011

Copy link to clipboard

Copied

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?

Views

2.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Dec 18, 2011 Dec 18, 2011

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation