help with updating form text boxes

[MillsRJ]

I have a form that is 15 text boxes wide by several rows deep (depends on criteria). The text boxes are populated using a vbscript function, this works great (code to follow). The problem is in changing the data. I thought I could do an onChange event to vbscript that puts together a SQL update or Insert (as the case may be).

How do I get the onChange event to reference a vbScript function or sub?

How do I refer to the new entry in the textbox?

How do I build the SQL statement to do a bunch of these at one time? I could do it one at a time, but I want the reset button to put the form back to it's original state.

note: I colored the code in question

thanks

Rob

-------------code------------

<%
dim xday1,xday2,xday3,xday4,xday5,xday6,xday7,xday8,xday9,xday10,xday11,xday12,xday13,xday14
dim wkday

wkday = request.Form(&quot;datebox&quot
xday14=WeekDayName(weekday(dateadd(&quot;d&quot;,0,wkday)),true)
xday13=WeekDayName(weekday(dateadd(&quot;d&quot;,-1,wkday)),true)
xday12=WeekDayName(weekday(dateadd(&quot;d&quot;,-2,wkday)),true)
xday11=weekdayname(weekday(dateadd(&quot;d&quot;,-3,wkday)),true)
xday10=weekdayname(weekday(dateadd(&quot;d&quot;,-4,wkday)),true)
xday9=weekdayname(weekday(dateadd(&quot;d&quot;,-5,wkday)),true)
xday8=weekdayname(weekday(dateadd(&quot;d&quot;,-6,wkday)),true)
xday7=weekdayname(weekday(dateadd(&quot;d&quot;,-7,wkday)),true)
xday6=weekdayname(weekday(dateadd(&quot;d&quot;,-8,wkday)),true)
xday5=weekdayname(weekday(dateadd(&quot;d&quot;,-9,wkday)),true)
xday4=weekdayname(weekday(dateadd(&quot;d&quot;,-10,wkday)),true)
xday3=weekdayname(weekday(dateadd(&quot;d&quot;,-11,wkday)),true)
xday2=weekdayname(weekday(dateadd(&quot;d&quot;,-12,wkday)),true)
xday1=weekdayname(weekday(dateadd(&quot;d&quot;,-13,wkday)),true)
%>
<%
function finddate(dayspan,subcat)
dim lookdate,foundit,qrydata2
' on error resume next
lookdate = dateadd(&quot;d&quot;,dayspan,request.form(&quot;datebox&quot)
foundit=0
set qryData2=Server.CreateObject(&quot;ADODB.Recordset&quot
qryData2.ActiveConnection = MM_OTReporting_STRING
qryData2.Source = &quot;Select * From dbo.tblData WHERE dtDate = '&quot; & lookdate & &quot;' AND numsubcatagoryid = &quot; & subcat & &quot;&quot;
qryData2.CursorType = 0
qryData2.CursorLocation = 2
qryData2.LockType = 1
qryData2.Open()
if qrydata2.eof and qrydata2.bof then
finddate=0
exit function
end if
qrydata2.movefirst
finddate =qrydata2(&quot;numEntry&quot
qryData2.Close()
Set qryData2 = Nothing
end function


sub ChangeBox(newnum,dayspan,subcat)
dim addSQL,modSQL,curnum,subsite,lookdate
response.Write(&quot;newnum = &quot;&newnum&&quot;, dayspan = &quot;&dayspan&&quot;, subcat=&quot;&subcat)
if newnum = 0 or newnum = &quot;&quot; then
exit sub
end if
subsite = request.Form(&quot;subcategoryselect&quot
curnum = finddate(dayspan,subcat)
lookdate = dateadd(&quot;d&quot;,dayspan,request.form(&quot;datebox&quot)
if curnum = 0 then
'add new record
addSQL = &quot;Insert into dbo.tblData (numSubCatagoryID,dtDate,flgLocked,numEntry,numSubSiteID) &quot;
addSQL = addSQL & &quot;VALUES (&quot;&subcat&&quot;,'&quot;&lookdate&&quot;',0,&quot;&newnum&&quot;,&quot;&subsite&&quot &quot;
else
'update SQL statement here
end if
end sub
%>
<form name=&quot;form1&quot; method=&quot;post&quot; action=&quot;dataentry2.asp?mode=doit&quot;>
<table width=&quot;98%&quot; border=&quot;1&quot;>
<tr bgcolor=&quot;#99CCFF&quot;>
<td> </td>
<td>
<div align=&quot;center&quot;><strong><%= xday1 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday2 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday3 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday4 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday5 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday6 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday7 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday8 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday9 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday10 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday11 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday12 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday13 %></strong></div></td>
<td>
<div align=&quot;center&quot;><strong><%= xday14 %></strong></div></td>
</tr>
<tr bgcolor=&quot;#99CCFF&quot;>
<td width=&quot;28%&quot;><strong>Element</strong></td>
<td width=&quot;6%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;date1&quot; type=&quot;text&quot; id=&quot;date1&quot; value=&quot;<%=dateadd(&quot;d&quot;,-13,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day16&quot; type=&quot;text&quot; id=&quot;date2&quot; value=&quot;<%=dateadd(&quot;d&quot;,-12,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day17&quot; type=&quot;text&quot; id=&quot;date3&quot; value=&quot;<%=dateadd(&quot;d&quot;,-11,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day18&quot; type=&quot;text&quot; id=&quot;date4&quot; value=&quot;<%=dateadd(&quot;d&quot;,-10,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day19&quot; type=&quot;text&quot; id=&quot;date5&quot; value=&quot;<%=dateadd(&quot;d&quot;,-9,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day110&quot; type=&quot;text&quot; id=&quot;date6&quot; value=&quot;<%=dateadd(&quot;d&quot;,-8,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day111&quot; type=&quot;text&quot; id=&quot;date7&quot; value=&quot;<%=dateadd(&quot;d&quot;,-7,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day112&quot; type=&quot;text&quot; id=&quot;date8&quot; value=&quot;<%=dateadd(&quot;d&quot;,-6,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day113&quot; type=&quot;text&quot; id=&quot;date9&quot; value=&quot;<%=dateadd(&quot;d&quot;,-5,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day114&quot; type=&quot;text&quot; id=&quot;date10&quot; value=&quot;<%=dateadd(&quot;d&quot;,-4,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day115&quot; type=&quot;text&quot; id=&quot;date11&quot; value=&quot;<%=dateadd(&quot;d&quot;,-3,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day116&quot; type=&quot;text&quot; id=&quot;date12&quot; value=&quot;<%=dateadd(&quot;d&quot;,-2,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;4%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day117&quot; type=&quot;text&quot; id=&quot;date13&quot; value=&quot;<%=dateadd(&quot;d&quot;,-1,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
<td width=&quot;18%&quot;>
<div align=&quot;center&quot;> <strong>
<input name=&quot;day118&quot; type=&quot;text&quot; id=&quot;date14&quot; value=&quot;<%=dateadd(&quot;d&quot;,0,request.form(&quot;datebox&quot)%>&quot; size=&quot;5&quot; readonly=&quot;true&quot;>
</strong></div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT qryElements.EOF))
%>

<tr>
<td height=&quot;26&quot;><%=(qryElements.Fields.Item(&quot;strSubCatagoryName&quot.Value)%></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day1&quot; type=&quot;text&quot; id=&quot;day1&quot; onChange=&quot;<%call Changebox(value,-13,qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)%>&quot; value=&quot;<%=finddate(-13,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day2&quot; type=&quot;text&quot; id=&quot;day2&quot; value=&quot;<%=finddate(-12,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day3&quot; type=&quot;text&quot; id=&quot;day3&quot; value=&quot;<%=finddate(-11,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day4&quot; type=&quot;text&quot; id=&quot;day4&quot; value=&quot;<%=finddate(-10,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day5&quot; type=&quot;text&quot; id=&quot;day5&quot; value=&quot;<%=finddate(-9,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day6&quot; type=&quot;text&quot; id=&quot;day6&quot; value=&quot;<%=finddate(-8,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day7&quot; type=&quot;text&quot; id=&quot;day7&quot; value=&quot;<%=finddate(-7,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day8&quot; type=&quot;text&quot; id=&quot;day8&quot; value=&quot;<%=finddate(-6,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day9&quot; type=&quot;text&quot; id=&quot;day9&quot; value=&quot;<%=finddate(-5,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day10&quot; type=&quot;text&quot; id=&quot;day10&quot; value=&quot;<%=finddate(-4,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day11&quot; type=&quot;text&quot; id=&quot;day11&quot; value=&quot;<%=finddate(-3,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day12&quot; type=&quot;text&quot; id=&quot;day12&quot; value=&quot;<%=finddate(-2,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day13&quot; type=&quot;text&quot; id=&quot;day13&quot; value=&quot;<%=finddate(-1,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
<td>
<div align=&quot;center&quot;>
<input name=&quot;day14&quot; type=&quot;text&quot; id=&quot;day14&quot; value=&quot;<%=finddate(0,(qryElements.Fields.Item(&quot;numsubcatagoryid&quot.Value)) %>&quot; size=&quot;5&quot;>
</div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
qryElements.MoveNext()
Wend
%>
</table>
<input name=&quot;datebox&quot; type=&quot;hidden&quot; value=&quot;<%=request.form(&quot;datebox&quot%>&quot;>
<input type=&quot;hidden&quot; name=&quot;subcategoryselect&quot; value=&quot;<%=request.form(&quot;subcategoryselect&quot%>&quot;>
<input type=&quot;hidden&quot; name=&quot;categoryselect&quot; value=&quot;<%=request.form(&quot;categoryselect&quot%>&quot;>
<br>
<input type=&quot;submit&quot; name=&quot;Submit&quot; value=&quot;Save&quot;>
<input type=&quot;reset&quot; name=&quot;Submit2&quot; value=&quot;Reset&quot;>
</form>
<p> </p>
<p><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><a href=&quot;home.asp&quot;><img src=&quot;images/home.gif&quot; width=&quot;70&quot; height=&quot;35&quot; border=&quot;0&quot; align=&quot;left&quot;></a></font></p>
</html>

Rob Mills
System Analyst
Correctional Services Canada
(all statements are my own ...., ah hell, its the government, sue'em anyway)