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"name="form">
<table border=1>

<%

x = 1
while RS.EOF=false
%>
<tr><td><input type="hidden" name="SprogNo<%= x %>"
value="<%Response.Write RS.Fields("SprogNo")%>"><% response.write
(RS.Fields("SprogNo"))%></td>
<td><input type="text" name="Report<%= x %>" value="<%Response.Write
RS.Fields("Report")%>"></td>
<td><input type="text" name="rep2<%= x %>" value="<%Response.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.CreateObject ("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
SOURCE=C:\Inetpub\wwwroot\test\test.mdb")
'Dim rs
Set rs = Server.CreateObject ("ADODB.Recordset")
rs.Open "SELECT * FROM SprogTbl", DB, 2, 2
rs.movefirst
for x = 1 to 3
rs.Fields("SprogNo") = Request.Form("SprogNo" & x)
rs.Fields("Report") = Request.Form("Report" & x)
rs.Fields("rep2") = Request.Form("rep2" & 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("SprogNo")=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.SprogNo)='" & SprogNo & "');"
DB.execute(strSQL)

..... 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("SprogNo") =
Request.Form("SprogNo" & x) statement. So far all attempts have fallen
foul of syntax errors. e.g. rs.update WHERE ((SprogTbl.SprogNo)='" &
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......