troubleshooting Question

Display MSAccess default value for new record

Avatar of JLohman
JLohman asked on
Microsoft AccessASPMicrosoft SQL Server 2005
16 Comments2 Solutions515 ViewsLast Modified:
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

I have three tables: tblGeneral, tblROIInvestment, tblROIReturn.
The common field linking the three tables is ChartID
I need to update the data in tblROIInvestment and tblROIReturn.
Page1.asp is accessed via a password page. It displays, allows updating and when you click submit the data is updated and connects successfuly to Page2.asp

Page2.asp displays correctly. Here is the issue. There are 4 fields to update:
- ROIRefBrief
- ROIRefBriefCost
- ROIComplex
- ROIComplexCost

ROICRefBriefCost and ROIComplexCost have default values set in the MSAccess table.

The first time a user access their data, the fields are displayed, but it is NOT showing the default values. I need the default values to display when a new record is created. Is that possible?

I am attaching the code for Page1.asp and Page2.asp
Page1.asp code:
<%
dim title
title = "Return on Investment for Library Services. Annual Investment Worksheet"

ChartID = Request.querystring("ChartID")

'Get values for form below


'Now get the data for the form below
dim facility, VISNID, city, state

	sql = "SELECT * FROM tblGeneral WHERE ChartID=" & ChartID & ";"
	set objrec = objConn.Execute(sql)

		City = objrec("City")
		State = objrec("State")
		Facility = objrec("Facility")
		VISNID = objrec("VISNID")

	set objrec = nothing

'Now get the data for the form below
sql = "SELECT * FROM tblROIInvestment WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn

'Following code creates a station entry when none exists
'create variables and give them values from recordset fields
'Then use variables in form code below, rather than recordset fields

If not objrec.eof then
	ROIAMM = objrec("ROIAMM")
	ROILNO = objrec("ROILNO")
End If

%>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title><%=title%></title>


</head>

<body>

<h3><%=title%> (page 1 of 3)</h3>


<p><b>
	Facility Number:<font color="#FF0000"> <%=Facility%></font><br>
	VISN: <font color="#FF0000"> <%=VISNID%></font><br>
	Site: <font color="#FF0000"> <%=City%>, <%=State%></font>
</b></p>


<hr>

<form method="POST" action="Page2.asp" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1">

<table border='1' width='50%'>


	<tr>
		<td>
		<b>AMMS cost:&nbsp; </b>
		<input type="text" name="ROIAMM" size="10" value="<%=ROIAMM%>"></td>

	</tr>


	<tr>
		<td>
		<b>Program Support: </b>
		<input type="text" name="ROILNO" size="10" value="<%=ROILNO%>"></td>
	</tr>
		
</table>


<input type="submit" value="Continue"> 
<input type="hidden" name="ChartID" value="<%=ChartID%>">

</form>

<p>&nbsp;</p>
</body>
</html>


Page2.asp code:
<%
dim title
title = "Return on Investment for Library Services. Annual Investment Worksheet"


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

'If there was a record for this ChartID in tblROIInvestment already, delete it.
sql = "DELETE * FROM tblROIInvestment WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn

'Start building your sql statement.  We're going to insert a record into tblROIInvestment for each ChartID.

sql = "INSERT INTO tblROIInvestment ("
	For each strCol in Request.Form
	'Loop through the fields in the form
		For each item in Request.Form(strCol)
			If Len(Request(strCol)) > 0 then
				strColumns = strColumns & strCol & ","
				'Find out which columns need to be updated
				'Following line does not allow apostrophe
				'strValues = strValues & "'" & item & "',"
    			'The Replace code in the next line corrects apostrophe entries
    			strValues = strValues & "'" & Replace(item,"'","''") & "',"
				'Find out what value is in those form fields
			End If
		Next
	Next

strColumns = left(strColumns, len(strColumns)-1)
strValues = left(strValues, len(strValues)-2)
'Get rid of the commas at the end of the column and value strings

sql = sql & strColumns & ") VALUES (" & strValues & "')"
'Finish building your sql statement then execute the insert
objConn.Execute(sql)


'Now get the data for the form below

sql = "SELECT * FROM tblROIReturn WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn

'Following code creates a station entry when none exists
'create variables and give them values from recordset fields
'Then use variables in form code below, rather than recordset fields

If not objrec.eof then
	ROIRefBrief = objrec("ROIRefBrief")
	ROIRefBriefCost = objrec("ROIRefBriefCost")

	ROIComplex = objrec("ROIComplex")
	ROIComplexCost = objrec("ROIComplexCost")
End If

%>
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title><%=title%></title>

<link rel=stylesheet type="text/css"
  href="/VALNET/CodeReuse/style.css">
  
</head>

<body>

<h3><%=title%> (page 2 of 2)</h3>

<hr>

<form method="POST" action="End.asp" onsubmit="return FrontPage_Form1_Validator(this)" language="JavaScript" name="FrontPage_Form1">
  
<table>

	<tr>
		<td><b>
		Number of brief:&nbsp; </b>
		<input type="text" name="ROIRefBrief" size="10" value="<%=ROIRefBrief%>"><br>
		<b>Cost: </b>
		<input type="text" name="ROIRefBriefCost" size="10" value="<%=ROIRefBriefCost%>"></td>
	</tr>

	<tr>
		<td><b>
		Number of complex:&nbsp; </b>
		<input type="text" name="ROIComplex" size="10" value="<%=ROIComplex%>"><br>
		<b>Cost: </b>
		<input type="text" name="ROIComplexCost" size="10" value="<%=ROIComplexCost%>"></td>
	</tr>

</table>


<input type="submit" value="Submit">
<input type="hidden" name="ChartID" value="<%=ChartID%>">

</form>

</body>

</html>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 16 Comments.
Join the Community
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 2 Answers and 16 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