04-14-2004, 06:37 AM
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
04-14-2004, 06:47 AM
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
04-14-2004, 07:00 AM
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
04-14-2004, 07:04 AM
Can we see your current code. This will help us spot the issue.
04-14-2004, 07:14 AM
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
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
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
Public Sub LoadData()
txtIllustrationID = rs!IllustrationID
txtFilename = rs!FileName
04-14-2004, 07:36 AM
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"
rs.Open strString, conn, adOpenKeyset, adLockOptimistic, adCmdText
While rs.EOF = False
rs.Fields("Supplemental") = txtFileName
Set rs = Nothing
Set conn = Nothing
04-14-2004, 07:57 AM
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
04-14-2004, 08:12 AM
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
04-14-2004, 08:47 AM
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"
rs.Fields("Supplemental") = txtFilename
"Item cannot be found in the collection coressponding to requested name or ordinal"
Thank you again for your help!!
04-14-2004, 08:51 AM
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.