• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 497
  • Last Modified:

Display MSAccess default value for new record

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>

Open in new window

0
JLohman
Asked:
JLohman
  • 6
  • 6
  • 3
  • +1
2 Solutions
 
aikimarkCommented:
Instead of displaying default values, why not set the default values at the table level and then only append the fields for which the user has supplied values?
0
 
JLohmanAuthor Commented:
Sorry, that is what I want to do. I have the DEFAULT value set in the MSAccess table. The first time a user creates a record, I want the default value to display in the form. Currently it is not doing that.
0
 
aikimarkCommented:
As you loop through the controls, only include them in the Insert statement if the value isn't null/empty.  That is the approach I recommend that you take.

========
If you must implement this as described in your question, you will need to populate a table with the default value expressions.  Since this is your database, you should have access to all of this default data.  However, these default value expressions can be complicated to implement in a data-driven scheme.

You might consider creating a utility that creates code to push default values.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
JLohmanAuthor Commented:
I'm not sure how to write the code to loop if the value isn't empty. Could you give me an example?
0
 
aikimarkCommented:
looking at your code, I think you are doing this.  Your code is looking at the length of the control in order to include it in the Insert statement -- both the field name and the field value.  
* Do you have default values defined for your table?  
* What SQL is being generated/executed?
* What does your table look like after the Insert statement executes? (i.e. are the default values applied as expected)
0
 
JLohmanAuthor Commented:
The default values are set in the database table.
When a user signs in and creates a new record in tblROIReturn, the form displays but does NOT display the preset default in the database.

The next the user goes in, the default does show up.

I want the default to show-up when a new record is created.
0
 
aikimarkCommented:
>>The next the user goes in, the default does show up.

So, the default values are getting into the table?
0
 
peter57rCommented:
I know nothing about asp so I might be getting this wrong, but it seems to me that the poster is asking how to emulate an Access BOUND form - where any dfault values defined in the table appear in the form on the screen when a new record is begun.
But in this asp form, values are being explicitly fed from a recordset - the 'boxes' know nothing about where the data comes from or goes to - which in my head is the equivalent of an Access UNbound form.  So it is impossible for the default values held in the table definition to appear on the screen, although they will be added to the saved record if necessary.

I assume that asp must have commands that allow for a default value to be included in the asp code that defines the 'box', but I have no idea what they might be.
0
 
aikimarkCommented:
@peter57r

Your assessment of the problem is correct.
0
 
Anthony PerkinsCommented:
>>I assume that asp must have commands that allow for a default value to be included in the asp code that defines the 'box', but I have no idea what they might be. <<
Actually nothing to do with ASP, but more to do with ADOX and JET  The author will have to separately query the database to get those meta-data values.
0
 
JLohmanAuthor Commented:
Does anyone know code to accomplish this?

0
 
Anthony PerkinsCommented:
And it looks like I totally misread the question:  I see now that your backend is actually SQL Server 2000 and not MS Access.

In order to retrieve the Default value for each column use the following query:
SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableNameGoesHere'
0
 
JLohmanAuthor Commented:
My data is in an MSAccess database.
The server the database resides on is Microsoft SQL Server  2000 - 8.00.760 (Intel X86)

My table has fields with default values.
When a user creates a new record, I want the default value to display.

None of the suggestions above work. Any other ideas?

0
 
Anthony PerkinsCommented:
>>My data is in an MSAccess database.
The server the database resides on is Microsoft SQL Server  2000 - 8.00.760 (Intel X86)<<
Now you are confusing me.  In any case:
If you are using MS Access then use ADOX.
If you are using SQL Server then see my solution above.

>>None of the suggestions above work. <<
Unfortuantely, that does not work for me either.  Tell us what you did, what happened and why it is wrong.  We are not mind-readers.
0
 
JLohmanAuthor Commented:
I don't understand your recommeded solution:

   In order to retrieve the Default value for each column use the following query:
   SELECT COLUMN_NAME, COLUMN_DEFAULT
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'YourTableNameGoesHere'

Based on my original code example, how should my code be modified to display a default set in MSAccess. Could you please modify code so I can see what needs to be done? I do not know what INFORMATION_SCHEMA.COLUMNS refers to.


0
 
aikimarkCommented:
@JLohman

The SQL is going to return two columns and a number of rows equal to the number of columns in your tblGeneral table.  We don't know the controls on your web page to which you are mapping your fields.

since you reposted the acperkins SQL, I'll repost it with the implied substitution.  Maybe this will help you.

SELECT COLUMN_NAME, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblGeneral'

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 6
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now