How to identify records with empty fields that are required?

[MsAxes]

Hi all - I have a table named FieldReq that has the same names found in the Data table. The purpose of the FieldReq table is to know which field is required to be populated in the Data table.



Needs:

I would like to identify records that require data but have no value.
I would like to identify records that do not require data but have values.

I have no control over the Data table. This is an internal source that is downloaded and I only have to verify that everything is filled in as required. Note that this table has up to 25k records.

I have looked into creating a Index Query but I found no examples that I could use to tie it to the FieldReq table. All I found online were examples of Forms but nothing just tied to a query.

If someone could point me to the right direction (examples) so I can build this, I'd appreciate it.

[ranman256]

I have a table : tRules
Fld, Enabled
----------------
Name, true
Phone, true
etc.

(the enabled field lets you turn on/off validation of that field)

before the person can leave (or print ) ,
i load this recordset (of enabled fields) into a collection,
then i scan the recordset of the current record and compare the two.
If it hits a field with no data, msgbox: "XXX field is required"

it it scan thru w/o errors, then they can leave screen or print document.

[madpiet]

Why not just set the fields to be required at table level? Then those are validated before the insert is even attempted? If you have a complex rule that depends on more than on column, you either have to use a Form's BeforeInsert event or you need maybe a data macro. (Not sure, I pretty much stopped using Access for anything serious before they were added).

[orange]

Further to madpiet's suggestion:

-you can iterate over all of your tables, and
-for each field you can test the Required Property

Here's an example,note it checks all non-system tables

' ----------------------------------------------------------------
' Procedure Name: IdentifyRequiredFields
' Purpose: To Identify which fields in tables are required.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 16-Feb-23
' ----------------------------------------------------------------
Sub IdentifyRequiredFields()
    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        If Not tdf.name Like "MSys*" Then RequiredOutput tdf

'Note
'You could add other table names to be excluded OR
' Only include the table names you want to interrogate

    Next
End Sub

The called subprocedure

' ----------------------------------------------------------------
' Procedure Name: RequiredOutput
' Purpose: Procedure to show if the table field is Required or not
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter tdfTemp (TableDef):
' Author: Jack
' Date: 12-Mar-20
' ----------------------------------------------------------------
Sub RequiredOutput(tdfTemp As TableDef)
       
    On Error GoTo RequiredOutput_Error
          Dim fld As Field
       
          ' Enumerate Fields collection of the specified TableDef
          ' and show the Required property.
10        Debug.Print "Fields in " & tdfTemp.name & ":"
20        For Each fld In tdfTemp.Fields
30          '  If fld.Required Then
40                Debug.Print , fld.name & ", Required = " & _
                      fld.Required
50          '  Else
60          ' End If
70        Next fld
       
    
    On Error GoTo 0
RequiredOutput_Exit:
    Exit Sub

RequiredOutput_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RequiredOutput, line " & Erl & "."
    GoTo RequiredOutput_Exit
End Sub

[Gicu]

Can you post a screenshot of the FieldReq table? Does it contain just the required fields of the Data table? Do the data types match or the FieldReq fields are just Yes/No?
Cheers,

[madpiet]

It's going to be tedious, but you can create a query, and add all the columns from the table you're testing. Then to test for "Required"/"Not Null" violations, you'd OR together a bunch of [ColumnName] IS NULL... so something like

SELECT *
FROM MyTable
WHERE RequiredColumnName1 IS NULL
OR RequiredColumnName2 IS NULL
etc.

Otherwise, you could do this in VBA and loop over the fields collection, and do similar and get a row count. If the rowcount > 0 then you'd print out the column name that's violating the rule.
<Insert aircode siren here>

dim tdf as dao.tabledef
dim fld as dao.field
set tdf = currentdb.TableDefs(strTable)
for each fld in tdf.fields
if fld.Required = True Then
'--- do something: like write to output, or to table
CurrentDb.Execute "INSERT INTO LogTable (TableName, FieldName, Required) VALUES ('" & tdf.name & "', '" & fld.Name & "'," & fld.required & ");", dbFailOnError
next fld

set tdf = nothing

[MsAxes]

Thank you all for responding. A little background on the Data table. This data comes from an old system (1990s - no joke) that no longer gets updated with field requirements. So attached is an example that I made. As you can see table FieldReq has the name of fields and if they are required by product.

I would just like an alert of some kind that would tell me to look at that specific record.

[Gicu]

Is the real Data table's structure identical to your sample, meaning it does not have a primary key?

Cheers,

[MsAxes]

Yes but I can add a primary key because I upload the file to access since the data table is an excel file I download from the system.

[Gicu]

OK, that's is what I did, I'm working on a VBA function now.
Cheers,

[Gicu]

Hi,

Please have a look, I have simply added a calculated boolean field to flag the records that are missing required data. So this would give you the first part of your outcome; to get the other you can write a similar function that does the opposite (look for the N in FieldReq and Not IsNull in Data).
If you need to create a list of what is missing you could leave the rsReq to loop to the end and build a string of field names then change the function from boolean to string to return that).
EDIT:
Forgot to mention that you have to make sure the entries in Field (bad name by the way) in FieldReq match the field names in Data; you had two that had a dot after (Product serial no. and Store No.).
Cheers,

[MsAxes]

This is perfect, I don't need a list. With the flag you created I can go into the system and populate the required data.

I appreciate your time and help on this.

Thank you

[Gicu]

[madpiet]

Seems that you should be able to have a table with a (tableName, ColumnName, Required) or just the list of (tableName, ColumnName) where the column in the table is required. Then You can just use that to look for NULLS. Just some stupid dynamic SQL. (you just set the .SQL property of your dummy query. Then you open it or append those violations to another table. Then all of your violations can be summarized in a query or report, and you know what to fix.
Or if you were really creative, you could fix it. (Well, except if you haven't cleaned up the data, then you won't be able to save the changes because the existing records will violate the new validation rules)

Not super hard, but you'll have to be at least reasonably comfortable writing VBA and some SQL.

Or maybe I'm paying more attention to the hockey game than I am to this post... hmmm...

[madpiet]

Oh, there's a HUGE difference between columns being required on a record by record basis, and a column that is required. If a column/field requires a value, you'd just implement that in the table definition. Otherwise, you have to loop through the table a record at a time and check, which is going to be slow... but at least if you do it in code, it's faster than doing it by hand.

Going back to watching hockey. =)