We help IT Professionals succeed at work.
Get Started

Form for editing - delete previously stored number

243 Views
Last Modified: 2012-05-11
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I have a form to provide users access to update data. There are 120 fields. The text fields in the form work correctly. The problem I am having is with the CURRENCY fields. If a user has a number previously entered in the field and attempts to delete the data, an error occurs. If they enter a '0' in the form, the form works correctly and stores the '0' and form generates no error messages.

The database is in MSAccess. There are 10 fields with exactly the same properties. I need to have the fields to allow number input with 1 or 2 decimal points (examples:  1.0, 1.5, .25). For reports I calculate totals and averages and need to use the fractional numbers accurately. The field properties for the problem field (LibFTE) are
     DataType:  CURRENCY
     Format:  FIXED
     Decimal Places:  2
     Required:  NO
     Indexed:  NO
     Text Align:  GENERAL  

There are 3 pages to the update process:
     default.asp - this page requires a check for a password (works correctly)
     Page1.asp - the form; displays stored data and allows editing
     End.asp - updates the database

All tables are connected by a field call ChartID.

Page 1 code:

sql = "SELECT * FROM tblStaff WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn
If not objrec.eof then
	LibFTE = objrec("LibFTE")
         [ALL FIELDS ARE IDENTIFIED.....]
End If
[Standard HTML code. Then display update form]

	<p><b>What is the approved FTE?</b>&nbsp;&nbsp;
	<input type="text" name="LibFTE" size="9" value="<%=objRec("LibFTE")%>"><br>
	
Code in End.asp

dim sql, objRec, ChartID
ChartID = Request.form("ChartID")


on error resume next
sql = "SELECT * FROM tblStaff WHERE ChartID = " & ChartID
	Set objRec = Server.CreateObject("ADODB.Recordset")
	objRec.Open sql, objConn, adLockOptimistic, adCmdTable
	objRec("LibFTE") = Request.form("LibFTE")
         [all fields identified]

	objRec.Update


if err.Number <> 0 then 
   Response.Write "<font face='Arial'>Data is not updated."
   Response.Write "<p>Error updating record, please press the <b>Alt</b> + back arrow to correct."
   Response.Write "The most common error is text in the FTE fields."
   Response.End
end if

Open in new window

Comment
Watch Question
Independent IT Consultant
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE