How to check table data from 2nd table requirements?

[MsAxes]

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.

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

[June7]

Two options:

1. inside rsData loop, open rsFieldReq filtered to just record associated with CustomerType of rsData record - open and close rsFieldReq within loop

2. use FindFirst method on rsFieldReq

If you want to provide db for analysis, follow instructions at bottom of my post.

[Welshgasman]

The Field_Req table has 10 rows with over a hundred columns with a "Y" or "N" for that particular field.

That does not sound right at all?

[MsAxes]

I added this:

Set rsFieldReq = CurrentDb.OpenRecordset("SELECT * FROM rsFieldReq WHERE Data_Element_Name = '" & rsrsData ("Product_Name").Value & "'")

right after the 1st loop and it works. Took a lot of trial and error and searching various forums for an example I can use.