Access 2007 VBA - Looking For A Better Way

[SeanK]

I have an Access 2007 database application that I’m taking over. In one of the tables there are 13 fields, and I need to run a query to see if any of those fields contains a zero. The typical entry in these fields are numbers like 0.8, 1.4, 0.5, 9.1, 0.0 – I just need to know the COUNT of how many fields, for each of the around 2,500 records, contains a 0.0. I have:

Dim db As DAO.Database
Set db = Access.Application.CurrentDb
Dim rsCount As DAO.Recordset
Dim sSQL As String
Dim Counter As Integer
Dim strPOCode As String
Set rsCount = CurrentDb.OpenRecordset("SELECT * FROM table")
Do While Not rsCount.EOF
 
    strPOCode = rsCount.Fields("FieldName")
    
    If rsCount.Fields("Field1").Value = 0 Then
       Counter = Counter + 1
    End If
    
    If rsCount.Fields("Field2").Value = 0 Then
       Counter = Counter + 1
    End If
 
    If rsCount.Fields("Field3").Value = 0 Then
       Counter = Counter + 1
    End If
 
'etc etc on through all 13 fields
 
    sSQL = "UPDATE table "
    sSQL = sSQL & "SET  CounterFieldInTable = " & Counter
    sSQL = sSQL & " WHERE FieldName = '" & strPOCode & "' "
    'Debug.Print sSQL
    db.Execute sSQL
    Counter = 0
    rsCount.MoveNext
Loop

This works just fine....but I'm not real well versed in Access VBA (or any other kind of VBA for that matter) so I'm wondering if there is a better way. It loops through all 2,500 and change records pretty quickly....it just looks a little "sloppy".

Thanks.

[dmaruca]

If you're looking for style suggestions, I'd say that everything looks fine. It's not sloppy at all!

The only things I'd point out is that you should probably get into the habit of not assigning variables like strPOCode until just before you need it. The assignment was done 50 something lines before it was needed and the POCode is still valid in the recordset object... whoever inherited the code would have to search. You also created the db object and didn't use it.

You did good being explicit with DAO. I'm not really a fan of hungarian notation (personal opinion) since it was created to take care of a problem in a different language than VBA, and VBA doesn't have that problem of excessive typecasting to the point of not knowing what type of variable you're looking at. I hope this was the kind of feedback you were looking for. Have a great day!

[anhn]

A single statement can do the job for you in one hit:

DoCmd.RunSQL "UPDATE Table1 SET ZeroCount = " & _
             "-(Field1=0)-(Field2=0)-(Field3=0)-(Field4=0)-(Field5=0)-(Field6=0)-(Field7=0)" & _
             "-(Field8=0)-(Field9=0)-(Field10=0)-(Field11=0)-(Field12=0)-(Field13=0)"

You can also replace DoCmd.RunSQL with CurrentDb.Execute

If Field1 = 0 then (Field1=0) = True = -1, otherwise (Field1=0) = False = 0

[Ecniv]

    dim rst as dao.recordset
    dim strSql as string
    dim lngR as long, lngMR as long
    dim lngFld as long
    dim blnHasZero as boolean

    strsql = "SELECT fld1, fld2, fld3, fld4, HasZero from tblData"

    set rst = dbengine(0)(0).openrecordset(strsql)
    if rst.eof then
        msgbox "No records",vbokonly+vbinformation
    else
        rst.movelast
        rst.movefirst
        lngmr=rst.recordcount

'---- loop through all records
        for lngr = 1 to lngmr
             blnHasZero=false

'---- change 3 for max field -1 (fields count from 0)
             for lngFld = 0 to 3
                 if rst(lngfld)=0 then 
                      blnHasZero=true
                      exit for
                 endif
             next
         

             if blnHasZero then
                  rst.edit
                  rst("HasZero")=true
                  rst.update
             end if
             rst.movenext

'---- option to add a percentage bar here if loads of records using lngr and lngmr

        next
    endif
    rst.close
    set rst = nothing

[dmaruca]

Ecniv, your code is less readable than the OP.