|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have inheritted a db that needs to be checked for nulls and reported on.
I'm trying to automate this process. The previous method was to run a query
export as Excel and search the block of data for errors manually. Using the
existing query, i want to take any null values (designated by an "X") and put
them in a sperate table. I will then Export that table to Excel and send the
null values report to the appropriate people. I have written code to search
each field in the query for an "X". Is there a way to code this so I don't
have to write out each field? There are 270 elements that need to be
searched. Here is my code thus far. It will work fine for one element then
give me a "Record Not Found" Error on the next field.
Option Explicit
Public intCathID As Integer
Public strFieldDef As String
Public intFieldCount As Integer
Private Sub ErrorCheckA()
Dim rst As DAO.Recordset
Dim rstError As DAO.Recordset
Dim db As DAO.Database
Dim strTest As String
'set counter to 0
intFieldCount = 20
Set db = CurrentDb()
Set rst = db.OpenRecordset("30DefTest")
Set rstError = db.OpenRecordset("tblError")
'Delete existing entries in error table
With rstError
Do Until .EOF
rstError.Delete
rstError.MoveNext
Loop
End With
With rst
'Do Until .EOF
Do While intFieldCount < 44
strTest = rst(intFieldCount)
If rst(intFieldCount) = "X" Then
intCathID = rst!SS_Event_Cath_ID
strFieldDef = rst(intFieldCount)
Call UpdateError
'Else
End If
rst.MoveNext
If .EOF Then
intFieldCount = intFieldCount + 1
'Call UpdateError
'Else
'Call UpdateError
End If
Loop
End With
rst.Close
rstError.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblerror",
"C:\Documents and Settings\All Users\Desktop\CathErrors"
'DoCmd.SendObject acSendTable, "tblError", acFormatXLS, "xxxx[ at ]XXX.org", , ,
"Test", "Testing this code. The cool part is this is all done with one
button push.", True
End Sub
'This routine updates the error table
Private Sub UpdateError()
Dim rst As DAO.Recordset
Dim rstError As DAO.Recordset
Dim db As DAO.Database
Dim strWhere As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("30DefTest")
Set rstError = db.OpenRecordset("tblError")
strWhere = "SS_Event_Cath_ID = " & intCathID
With rst
rst.FindFirst strWhere
rstError.AddNew
If rst(intFieldCount) = "X" Then
rstError!EventCathID = rst!SS_Event_Cath_ID
rstError!MRN = rst!Patient_ID
rstError!CathDate = rst!Date_of_cath
rstError!PatLast = rst!Last_Name
rstError!Fellow = rst!Cath_Fellow
rstError!attending = rst!Cath_Attending
rstError!Error = "Missing"
'rstError!Field = strFieldDef
rstError!Field = rst(intFieldCount).Name
rstError.Update
End If
End With
End Sub
|
|
|