CODEWITHSUNDEEP
ms-accessvba
Anton
Anton

Reputation: 79

Select Record with blank field

I have an access database with several headers

for this question lets just say i have header1, header2 and header3

what i want to do is get the last item with a blank field for header3 since both header1 and header2 already have data in them.

what i have for now:

 Dim db As DAO.Database
 Dim rst As DAO.Recordset
 Dim sqlstr As String
 Dim header1, header2, header3 as field

 Set db = CurrentDb()
 Set rst = db.OpenRecordset("TblToMonitor")

 sqlstr = "Select * from [Mytable] Where [header3] IS NULL OR len(Nz([header3])) =0;"

 docmd.runsql(sqlstr)

 with rst
      .edit
      .fields("header3") = "Done"
      .update
 end with

when i run the code, it selects the first row then updates the header3 field with done but does not move to the next next record.

any assistance would be appreciated.

Upvotes: 0

Views: 129

Answers (1)

parakmiakos
parakmiakos

Reputation: 3020

In order to update all rows you need to enclose the update part in a loop : 

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sqlstr As String
Dim header1, header2, header3 as field

Set db = CurrentDb()    

sqlstr = "Select * from [Mytable] Where [header3] IS NULL OR len(Nz([header3])) =0;"

Set rst = db.OpenRecordset(sqlstr) 
with rst
    While not .EOF
        .edit
        .fields("header3") = "Done"
        .update
        .MoveNext
    Wend
end with

Upvotes: 2

Related Questions