Problems Using UPDATE to modify multiple and non sequential records - a giant conundrum from a total novice....

[Chris Hall]

The records in my database are displayed in a form as follows:
%>
<form action="report-ammend1.42.asp" method="post"na me="form">
<table border=1>

<%

x = 1
while RS.EOF=false
%>
<tr><td><inpu t type="hidden" name="SprogNo<% = x %>"
value="<%Respon se.Write RS.Fields("Spro gNo")%>"><% response.write
(RS.Fields("Spr ogNo"))%></td>
<td><input type="text" name="Report<%= x %>" value="<%Respon se.Write
RS.Fields("Repo rt")%>"></td>
<td><input type="text" name="rep2<%= x %>" value="<%Respon se.Write
RS.Fields("rep2 ")%>"></td></tr>

<%
x = x + 1
RS.movenext
Wend
By specifying integer values for x in the form action file, the
correct data is always udated to the correct record:

Dim DB

Set DB = Server.CreateOb ject ("ADODB.Connect ion")
DB.Open ("PROVIDER=Micr osoft.Jet.OLEDB .4.0;DATA
SOURCE=C:\Inetp ub\wwwroot\test \test.mdb")
'Dim rs
Set rs = Server.CreateOb ject ("ADODB.Records et")
rs.Open "SELECT * FROM SprogTbl", DB, 2, 2
rs.movefirst
for x = 1 to 3
rs.Fields("Spro gNo") = Request.Form("S progNo" & x)
rs.Fields("Repo rt") = Request.Form("R eport" & x)
rs.Fields("rep2 ") = Request.Form("r ep2" & x)
rs.update
RS.movenext
Next
rs.movefirst
However, I need this to work for any (undefined) no. of records and
the records may have been queried to display non-sequential records
for the database.
I can't make any of the three solutions I can think of work:
1. If I could make the field SprogNo (which contains the unique ID
no.) rs.response write into x as an integer, then each record would be
automatically numbered. I would then need some kind of "for each x"
loop before the rs.update statements. Just putting:

Response.Write RS.Fields("Spro gNo")=x

in place of x = x+1 doesn't do it........

2. I've also tried tying the update to a WHERE statement. But, the
following will work for a databse of one record only:

Dim strSQL
strSQL = "UPDATE SprogTbl SET Report = '" & Report & _
"', rep2 = '" & rep2 & _
"' WHERE ((SprogTbl.Spro gNo)='" & SprogNo & "');"
DB.execute(strS QL)

..... however, I've had no success trying to iterate it's functions
starting with rs.movefirst and then a while rs.EOF=false 'rs.movenext
loop.

3. Finally, I thought it might be worth trying to get the WHERE
statement (above) into the rs.Fields("Spro gNo") =
Request.Form("S progNo" & x) statement. So far all attempts have fallen
foul of syntax errors. e.g. rs.update WHERE ((SprogTbl.Spro gNo)='" &
SprogNo & "');
The last two solutions would still require making the update repeat
for each of the fields in some kind of a loop.

Any help gratefully recieved......