Hi all
I have a database with 3 tables.
- Field_Req - This table identifies which fields in the Data table require/don't require data.
- Data - This table has about 10k records that gets updated daily (100 columns).
- Results - This table holds the results of which fields have data but shouldn't and vice-versa.
I also have the VBA below, its goal is to:
- Go through the Data and Field_Req table to identify records that are missing required data or have data but shouldn't. I run the code and for some reason it only loops through one record instead of the whole data set (it identifies which records are missing data or vice-versa correctly). I don't see why it stops on one record when I know fields that don't require data have a value.
Code:
Public Sub CheckFields() Dim rsFieldReq As DAO.Recordset
Dim rsData As DAO.Recordset
Dim rsResults As DAO.Recordset
Dim fld As DAO.Field
Dim strFieldName As String
Dim strAct As String
' Open recordsets for Field_Req, Data, and Results tables
Set rsFieldReq = CurrentDb.OpenRecordset("Field_Req")
Set rsData = CurrentDb.OpenRecordset("Data")
Set rsResults = CurrentDb.OpenRecordset("Results")
' Loop through each field in Field_Req table
For Each fld In rsFieldReq.Fields
' Check if field is required (Y) or not (N)
If fld.Value = "Y" Then
' Field is required, check if it is null in Data table
If IsNull(rsData(fld.Name)) Then
' Field is null, add error to Results table
strFieldName = fld.Name
strAct = rsData("act num").Value
rsResults.AddNew
rsResults("act num").Value = strAct
rsResults("FieldName").Value = strFieldName
rsResults("ErrorType").Value = "Y-field is null"
rsResults.Update
End If
ElseIf fld.Value = "N" Then
' Field is not required, check if it is not null in Data table
If Not IsNull(rsData(fld.Name)) Then
' Field is not null, add error to Results table
strFieldName = fld.Name
strAct = rsData("act num").Value
rsResults.AddNew
rsResults("act num").Value = strAct
rsResults("FieldName").Value = strFieldName
rsResults("ErrorType").Value = "N-field is not null"
rsResults.Update
End If
End If
Next fld
' Close recordsets
rsFieldReq.Close
rsData.Close
rsResults.Close
Set rsFieldReq = Nothing
Set rsData = Nothing
Set rsResults = Nothing
End Sub
I'll appreciate any feed-back.