So I am trying to learn how to use Recordsets. So I made a module and then call the module from a report. I also designed an update query to run on the recordset. Below is the code:
<code>
Function DAOLooping()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "UPDATE tempauditscores15 " _
& "SET tempauditscores15.[Defect Type] = DLookUp([Defect Type],tempauditscores15,[ID] = " & [ID]-1) " _
& "WHERE (((tempauditscores15.[Defect Type]) Is Null))"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
.MoveNext
Wend
End If
.Close
End With
ExitSub:
Set rs = Nothing
Exit Function
ErrorHandler:
Resume ExitSub
End Function
</code>
I get an error by the ID in the Dlookup string. Then I call the function from the form with the following code:
Call DAOLooping
Any assistance with this would be greatly appreciated. The DLookup is looking up missing information from the record above it. Since the first line in the table has the field it is basically coping it down to all columns that are null.
When I run the query alone it works fine, but it only copies it once. So I thought a recordset would solve this problem by doing it to every line. I do not want to run the query 15 times in the form. So I tried to simplify it.
<code>
Function DAOLooping()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim rs As DAO.Recordset
strSQL = "UPDATE tempauditscores15 " _
& "SET tempauditscores15.[Defect Type] = DLookUp([Defect Type],tempauditscores15,[ID] = " & [ID]-1) " _
& "WHERE (((tempauditscores15.[Defect Type]) Is Null))"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
.MoveNext
Wend
End If
.Close
End With
ExitSub:
Set rs = Nothing
Exit Function
ErrorHandler:
Resume ExitSub
End Function
</code>
I get an error by the ID in the Dlookup string. Then I call the function from the form with the following code:
Call DAOLooping
Any assistance with this would be greatly appreciated. The DLookup is looking up missing information from the record above it. Since the first line in the table has the field it is basically coping it down to all columns that are null.
When I run the query alone it works fine, but it only copies it once. So I thought a recordset would solve this problem by doing it to every line. I do not want to run the query 15 times in the form. So I tried to simplify it.