Looping Through RecordSet

hi

I'm just wondering if this is even possible. I am using ADODB to connect to a database and I am selecting 2 items from the database filename and illustration id.These are linked to two text boxes. I wish to loop through the recordset in orderto update the filename in another table based on the illustration ID. I want to do this without having to press the update button continously until the end of file is reached. Is there any other way that I can loop through the records and update the database with the filenames in one go??I have tried various statements and keep getting eof or bof exceptions which are understandble. Anyone have any ideas??

Thanks for your help
a ;)

You can iterate through a Recordset using a method like:

Do While Not objRecordset.EOF
' Do something with the current record

' Move to the next record in the recordset
objRecordset.MoveNext
Loop

Thanks but its still doing the same thing - just updating according to the first value held in the text box and ignoring the rest. It will work if i keep pressing the update button - but I want to do it automatically all at once?

Thanks again 4 ur help
any ideas??
a :D

Can we see your current code. This will help us spot the issue.

Sure,thanks!

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field

Private Sub cmdUpdate_Click()

Dim UPDATE As String

UPDATE = "UPDATE Illustration SET Supplemental = '" & txtFilename & "' WHERE IllustrationID = " & txtIllustrationID

Do While Not rs.EOF
conn.Execute UPDATE


rs.MoveNext
Loop

End Sub


Private Sub Form_Load()
Dim ConnStr As String

ConnStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= H:\a.mdb; JET OLEDB:"
conn.ConnectionString = ConnStr
conn.Open
Debug.Print "Connection Object Created"

rs.Open "SELECT DISTINCT m.IllustrationID, md.Filename FROM ModelDescription md , Model m WHERE md.MDescription = m.ModelDescription AND md.SubModelDescription2 = m.SubModelDescription2 AND md.SubModelDescription3 = m.SubModelDescription3", conn, adOpenDynamic, adLockOptimistic

LoadData
End Sub

Public Sub LoadData()

txtIllustrationID = rs!IllustrationID
txtFilename = rs!FileName

End Sub

OK, a better solution:



Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strString as string

Set conn = New ADODB.connection
Set rs = New ADODB.recordset

strString = "SELECT * FROM Illustration WHERE IllustrationID" = & txtIllustationID

conn.connectionstring = "YOUR ADO CONNECTION"
conn.open

rs.Open strString, conn, adOpenKeyset, adLockOptimistic, adCmdText

While rs.EOF = False
rs.Fields("Supplemental") = txtFileName
rs.update
rs.moveNext
Wend

rs.close
conn.close

Set rs = Nothing
Set conn = Nothing

Thanks for your help but I still have a problem :o

Its my fault for not explaining this but i am only using a certain number of illustration id's and they corresspond to a particular filename.I do not want the user to enter these values into the text box,they are hardcoded in2 them already.
I just want to go through these existing filenames in the textboxes and update the database based on their coressponding illustrtion ID's. it works only for the first value in the text box unless i keep pressing update,which i'm trying to avoid.Is there a better way 2 do this all at once?Im very confused.Am i trying to attempt the impossible?

Thank you so much for your help again

What, isn't that the point of the loop..this updates each record with the specified text variable ( you use a variable name in your own code, I just copied it in, txtIllustrationID). And in your code you are trying to loop until EOF, updating the field with this text variable. This code does exactly the same thing

Im a beginner so I'm sorry for all the stupid questions :)
but the way i c it i have 2 execute 2 strings


strString = "SELECT DISTINCT m.IllustrationID, md.Filename FROM ModelDescription md , Models m WHERE md.ModelDescription = m.ModelDescription AND md.SubModelDescription2 = m.SubModelDescription2 AND md.SubModelDescription3 = m.SubModelDescription3"

To enable the data to be shown in the text box

strString1 = "SELECT * FROM IllustrationInformation WHERE IllustrationID = " & txtIllustationID

Can I do this?

I used your code and kept getting this error with regard to the "Supplemental"
field.

rs.Fields("Supplemental") = txtFilename

"Item cannot be found in the collection coressponding to requested name or ordinal"

Thank you again for your help!!

Well, is supplemental a field in your recordset, if not, this will fail. Again, I was going by what you provided. If you want to updated the Supplemental field, make sure within your rs.Open statment that you include this field. You should just do a select * WHERE statement. With the Where clause you pass in the conditions you need (such as "IllustrationID" = 1), etc.