Solved

Display MSAccess default value for new record

Posted on 2011-09-09
16
468 Views
Last Modified: 2012-05-12
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
Comment
Question by:JLohman
  • 6
  • 6
  • 3
  • +1
16 Comments
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 

Author Comment

by:JLohman
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 

Author Comment

by:JLohman
Comment Utility
I'm not sure how to write the code to loop if the value isn't empty. Could you give me an example?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
 

Author Comment

by:JLohman
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
>>The next the user goes in, the default does show up.

So, the default values are getting into the table?
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@peter57r

Your assessment of the problem is correct.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 

Author Comment

by:JLohman
Comment Utility
Does anyone know code to accomplish this?

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 

Author Comment

by:JLohman
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
>>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
 

Author Comment

by:JLohman
Comment Utility
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
Comment Utility
@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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now