troubleshooting Question

Form for editing - delete previously stored number

Avatar of JLohman
JLohman asked on
Microsoft AccessASPSQL
13 Comments1 Solution247 ViewsLast Modified:
- 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

ASKER CERTIFIED SOLUTION
Ian Pattison
Independent IT Consultant

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros