Hi again - this question is related to the post found here. I am not looking for the answer, I would prefer an example of another block of code or maybe an article to read (I'm really trying to do this on my own as much as possible). If I manage to figure it out I will post the code so people can refer to it in the future if needed, if I can't, then I will ask for more help.
The purpose of the code below is:
- 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 it works exactly as expected.
My database has 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.
The Field_Req table has 10 rows with over a hundred columns with a "Y" or "N" for that particular field. One of those columns is Customer_Type (new) which has Customer_1, Customer_2 all the way up-to Customer_10. All have different requirements.
The Data table has those 10k records mentioned above with a field Customer_Type. I would now like for the code below to check the Field_Req table for that 'Customer_Type' to see which fields are required. I can't seem to find good examples, that's all I would like.
Code:
Public Sub CheckFields() Dim rsFieldReq As DAO.Recordset 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 record in Data table
Do While Not rsData.EOF
' 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
rsData.MoveNext ' Move to the next record in Data table
Loop
' Close recordsets
rsFieldReq.Close
rsData.Close
rsResults.Close
Set rsFieldReq = Nothing
Set rsData = Nothing
Set rsResults = Nothing End Sub