Hit a snag looping through tables and records

[bigukfan]

I am producing a statistic for one of the managers at work concerning hospital patients. I have a database with about 120 tables, some of which relate to a set of patients. All the table names I am interested in start with the four letters 'READ'. I loop through each of these tables, then loop through each record, and finally loop through each field. I am counting the total number of fields, and the total number of null fields. This all works fine.

Problem is, there are certain records I need to ignore because the patient has not been discharged. I first identify the unique patient hospital number and save this to an array, and was then planning to use this to identify any such record in any of the tables.

The problem I have is that the field in each separate table holding the hospital number does not have the same field name in every case, so I can't refer to a name. I remember reading somewhere it is possible to refer to a field by its number, such as 1 being the first field, 2 the second and so on. In every case, the first field holds the hospital number, in data type Number. Does anyone know is this true, and how can I include it in the code below, which excludes any attempt at ignoring records?

Private Sub sFindNullFields()
'This subroutine finds all READ records and counts both the number
'of fields in total, along with the number of null fields
Dim tdf As DAO.TableDef
Dim Dba As Database
Dim Rst As Recordset
Dim RstIgnore As Recordset
Dim i As Integer
Dim lIgnoreHospitalNumber(25) As Long
Dim lRecordCount As Long
Dim lTotalFields As Long
Dim lNullFields As Long
i = 0
 
'First need to identify which records to ignore as patient not yet discharged
'To do this, identify recs and populate array for use later
Set Dba = CurrentDb
Set RstIgnore = Dba.OpenRecordset("SELECT DateLeftHospital, HospitalNumber FROM READ_SummaryData WHERE DateLeftHospital Is Null", dbReadOnly)
With RstIgnore
    Do Until .EOF = True
        lIgnoreHospitalNumber(i) = !HospitalNumber
        i = i + 1
        .MoveNext
    Loop
End With
 
'Now need to loop through tables starting with 'READ' and
'count ALL fields along with all NULL fields
'but excluding all records containing 'Ignore' the hospital number
For Each tdf In Dba.TableDefs
    If Left(tdf.Name, 4) = "READ" Then
        Set Rst = Dba.OpenRecordset(tdf.Name, dbReadOnly)
        With Rst
            Do Until .EOF = True
                lTotalFields = lTotalFields + Rst.Fields.Count 'Want total fields for all records in each table
                'Now want to loop through all fields in each record to count any null values
                For i = 0 To .Fields.Count - 1
                    If .Fields(i).Value = False Then
                        lNullFields = lNullFields + 1
                    End If
                Next i
                .MoveNext
            Loop
        End With
    End If
Next
MsgBox "Percent Null: " & Format(lNullFields / lTotalFields, "Percent"), 64, "Finished..."
End Sub
 
 

[Rabbit]

I'm not sure what your question is because you say that you want to refer to the field by it's position in the recordset but you're already doing that in your code.

[bigukfan]

What I mean is that in all recordsets the first field is the hospital number, but how can i refer to the first field if not by name? Thanks for looking at this for me.

[orangeCat]

Wouldn't that be

With RecSetx
.... .fields(0) ' would be the field you want

[Rabbit]

What I'm trying to tell you is that you are already referencing fields by their position in the very code you posted.

[bigukfan]

Thanks RecSetx, that was what I wanted. I think its called the ordinal reference. Thank you.