Help with Recordset using a Query (1 Viewer)

[cstickman]

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.

 

[Ranman256]

DONT use dlookup in a query. The query IS the dlookup. Instead use table joins to get values from other tables.
FYI:
And note, there is very little use for this code. You dont need to scan a recordset 1 record at a time to do things. (now on rare occasions, Ive had to do this) but all this should be accomplished via update queries. Update queries are almost instantaneous where this code ,step by step, is extremely slower.

 

[jdraw]

For details and examples on use of Recordsets see Steve Bishop's youtube tutorials
starting here.
Videos 53, 54, and 55.

Good luck.

 

[cstickman]

Ranman - Thank you very much, I am not even sure why I did not think of using a join in the update query. I already wrote it and it worked like a charm!! Thank you for the suggestion!!

jdraw - Thank you for the links. I will be sure to watch them for help with other projects that require a recordset,

Thanks and happy holidays to the both of you!!