Data Check of Multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a module that uses the TransferSpreadsheet acImport to bring in 4
tables into one table and that works great. The next step I am having
trouble with is on each record I have about 8 different checks for warnings
or errors in the data.
Because several of the checks require nested if's, I would like to open the
table and code the checks for each record but I can not figure out how to do
it. I have tried the following where all of the names used in the code are
the actual data field names:

Private Sub Warn_Errors()
With CurrentDb.OpenRecordset("CPAP_Import", dbOpenDynaset)
Do Until .EOF ' To step through all records

.Edit ' For example
Dim tw_txt, te_txt As String
If IsNull([CPAP_SD]) Then
X_Cnt_Error = 1
X_E_SD = True
X_E_Text = "No Start Date Entered"
Else
'Additional test will go here!
End If
.Update
.MoveNext
Loop
End With

Please help, \DDBeards
 
A few observations:
It's not clear what X_Cnt_Error, X_E_SD, and X_E_Text are. Are they local
variables or table fields? If they are local variables, you don't need to
edit/update the records.

Most importantly, if [CPAP_SD] is a field in your recordset, you need to
refernce it like this:
If IsNull(.Fields("CPAP_SD"))
The same is true if you are updating the other fields.

Barry
 
Thats what I was looking for, they are all fields, just didn't know how to
address them. Thanks

Barry Gilbert said:
A few observations:
It's not clear what X_Cnt_Error, X_E_SD, and X_E_Text are. Are they local
variables or table fields? If they are local variables, you don't need to
edit/update the records.

Most importantly, if [CPAP_SD] is a field in your recordset, you need to
refernce it like this:
If IsNull(.Fields("CPAP_SD"))
The same is true if you are updating the other fields.

Barry

DDBeards said:
I have a module that uses the TransferSpreadsheet acImport to bring in 4
tables into one table and that works great. The next step I am having
trouble with is on each record I have about 8 different checks for warnings
or errors in the data.
Because several of the checks require nested if's, I would like to open the
table and code the checks for each record but I can not figure out how to do
it. I have tried the following where all of the names used in the code are
the actual data field names:

Private Sub Warn_Errors()
With CurrentDb.OpenRecordset("CPAP_Import", dbOpenDynaset)
Do Until .EOF ' To step through all records

.Edit ' For example
Dim tw_txt, te_txt As String
If IsNull([CPAP_SD]) Then
X_Cnt_Error = 1
X_E_SD = True
X_E_Text = "No Start Date Entered"
Else
'Additional test will go here!
End If
.Update
.MoveNext
Loop
End With

Please help, \DDBeards
Click to expand...
Click to expand...
 
Back
Top