asked on

Problem with updating recordset

Hi,

I‘m trying to get a function to update each row with in a recordset. So far I have this:

Dim myRST As Recordset
Set myRST = GetADORecordset("SELECT running_requirement FROM T6 WHERE identifier = '" & NewIssueAss & "' ")
MsgBox ("myRST.RecordCount - " & myRST.RecordCount)
myRST.MoveFirst
Do While Not myRST.EOF
myRST.Fields("running_requirement") = myDLookUp("original_requirement", "T6", "identifier = '" & NewIssueAss & "' ")
myRST.Update
myRST.MoveNext
Loop

The function GetADORecordset returns a recordset with 3 rows in it which is correct. However the update portion is not updating any of the records.

The locktype of the recordset is rst.LockType = adLockOptimistic if that makes any difference.

I’ve also tried myRST.UpdateBatch but it yields the same results.

Any pointers appreciated.

Thanks.

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

andyw27

ASKER

Here is the myDLookup function:

Public Function myDLookUp(ByVal strField As String, ByVal strDomain As String, _
Optional strCriteria As Variant)

Dim rsLk As Recordset
Dim strSql As String

myDLookUp = Null
If Not IsMissing(strCriteria) Then
strSql = "SELECT " & strField & " FROM " & strDomain & " WHERE " & strCriteria
Else
strSql = "SELECT " & strField & " FROM " & strDomain
End If
Set rsLk = GetADORecordset(strSql)
If Not rsLk.EOF Then myDLookUp = rsLk(0) Else myDLookUp = "N/A"
rsLk.Close

End Function

NewIssueAss is a string which is set before I call this function. The field runing_requirement is number field.