How to loop throgh records in SQL 1

[rbasram]

Everytime one problem is solved the other one pops up I Got manged to solve the problem with SQL statement, but now while I do this procedure the only the first record which meets the parameters gets its Document ID changed, isn't there any other way so that all the records are changed?? Is there some kind of loop that goes through all the records and then edits all the DocumentID where the two parameters match for a given recod. Here is my Function...

Dim MyDat As Recordset
Dim Titem As Variant
Dim Ritem As Variant
Dim sql As String


sql = "Select [TNum], [Product].[ServerName], [DocumentID] from [Bulletins Query1] where [TNum]= '" & lstTNum.Column(Titem) & "' and [Product].[ServerName] = '" & lstResult.Column(Ritem) & "';"

Set MyDat = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

MyDat.Edit
MyDat("DocumentID&quot = Me.txtDocID
MyDat.Update

End Sub

Any help will be appreciated..

 

[jebry]

Hi!

Try this:

If MyDat.EOF = True And MyDat.BOF = True Then
Call MsgBox("There were no records found&quot
Else
MyDat.MoveFirst
Do Until MyDat.EOF = True
MyDat.Edit
MyDat("DocumentID&quot = Me.txtDocID
MyDat.Update
MyDat.MoveNext
Loop
End If

hth
Jeff Bridgham
bridgham@purdue.edu

 

[cmmrfrds]

you could make an update query, which would update everything that meets the where criteria. This should be more efficient than stepping through record by record.

"Update [Bulletins Query1]
set [DocumentID] = Me.txtDocID
where [TNum]= '" & lstTNum.Column(Titem) & "' and [Product].[ServerName] = '" & lstResult.Column(Ritem) & "';"

 

[rbasram]

Thanx a lot guys the procedure you gave me really helped..

thanx a tons