How to read one field from one record

Question

I know what I think about it, but I want to check one value / field in one record. For example, I want to know if the value of the "closedDate" field in a record with primary key 33 is zero or not.

I thought something like:

dim db as DAO.Database dim rs as DAO.Recordset set db = CurrentDb set rs = db.OpenRecordset("record_holdData") If not isNull(rs.Fields("closedDate")) then 'do nothing Else 'add a close date End If 

But I do not think this is right. It does not indicate a record number. In the application, the form opens by binding to the corresponding record, but I do not think that CurrentDb takes this into account and rather refers to the entire table.

So my question is: how to open a recordset in this way and only refer to this field in this particular record?

Answer 1

You found the answer you want, but I would use the DLookup function .

 Dim db As DAO.Database Dim strWhere As String Dim varClosedDate As Variant Set db = CurrentDb strWhere = "id = 33" varClosedDate = DLookup("closedDate","record_holdData",strWhere) If IsNull(varClosedDate) = True Then 'use today date as closedDate db.Execute "UPDATE record_holdData Set closedDate = Date() WHERE " & strWhere End If 

Answer 2

I got the impression that the argument to the .OpenRecordset () method should only be the name of the table. But it turns out you can also send a request:

 set rs = db.OpenRecordset("select * from record_holdData where id = 33")