Access 2007 VBA - Looking For A Better Way
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:
vb Code:
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.
Re: Access 2007 VBA - Looking For A Better Way
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!
Re: Access 2007 VBA - Looking For A Better Way
A single statement can do the job for you in one hit:
Code:
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
Re: Access 2007 VBA - Looking For A Better Way
Code:
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
Re: Access 2007 VBA - Looking For A Better Way
Ecniv, your code is less readable than the OP.