Constraint "rsLK.RecordCount > 1" does NOT work for subset of tables

[skydivetom]

Good morning:

I have a quick question about altering a table WHERE condition of "rsLK.RecordCount > 1" is met.

Specifically, in the attached example, I have 4 tables with the following record counts:
1. [LK_AGE] = 1;
2. [LK_CIV_MIL] = 2,


3. [LK_CLOSE_DATE] = 1;
4. [LK_EMERGENCY_ROOM] = 4

For any table where "rsLK.RecordCount > 1" (e.g., [LK_CIV_MIL] and [LK_EMERGENCY_ROOM]), I then want to insert a new field [XYZ].

However, once I run the VBA routine, the new field [XYZ] is inserted into three (3) tables incl. [LK_AGE].

My question: Why does [LK_AGE] -- containing a single record -- by-pass the IF constraint and thus execute the following sub-routine?

If rsLK.RecordCount > 1 Then


    'Add [TARGET_STRING] (Text)
    Set fld = New DAO.Field
    fld.Name = "XYZ"                'Field to be added!
    fld.Type = dbText
    .Append fld
                        
    'Refresh fields collection
    .Refresh
    
    n = n + 1
End If

[NoellaG]

before you can know the actual recordcount you have to go to the last record (movelast) of the recordset.

[Bob Fitz]

skydivetom

You have the following line in your code:

                    If i > 1 Then        'In this example, the IF constrating only applies to 2 tables ([LK_CIV_MIL] and [LK_EMERGENCY_ROOM]).                                                        'However, the field below is also added to [LK_AGE]... it shouldn't!

You say that the field is added to [LK_AGE] but when I run the code it is added to [LK_CLOSE_DATE]
This was after adding the line: rsLK.MoveLastimmediately before your test of the recordcount

[skydivetom]

Bob:

As suggested, I added the following line:

                    rsLK.MoveLast                    
                    If rsLK.RecordCount > 1 Then

Also, as you indicated, table CIV_MIL, EMERGENCY_ROOM, AND CLOSE_DATE receive the new field.

The question remains... how do I need to modify the code so that *only* tables with more than one (1) record (i.e., CIV_MIL and EMERGENCY_ROOM) have the table altered?

//

Alternatively, I could go for the field count... that is, tables with 1 record only have 5 fields. Please note that LK_AGE should have field DATE_UPDATED as well. So, anything where table with field count > 5 should be altered. How would that work?

[orange]

Tom,
Just looked at you material.
Here's an alternative you could try.

Public Sub AddFieldsJ()
          
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim n As Integer
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If tdf.Name Like "LK_*" Then
            If tdf.RecordCount > 1 Then
                Debug.Print tdf.Name & " has " & tdf.RecordCount & " records"
                'Add [TARGET_STRING] (Text)
                With tdf.Fields
                    Set fld = New DAO.Field
                    fld.Name = "XYZ"                'Field to be added!
                    fld.Type = dbText
                    .Append fld
                    Debug.Print "Field 'XYZ' added to " & tdf.Name
                    n = n + 1
                    GoTo get_next_tdf
                End With
            End If
        End If
get_next_tdf:
    Next tdf
    Debug.Print "Finished " & n & " tables received new specified field(s)."
    MsgBox n & " tables received new specified field(s).", vbInformation, "Status"
    Set fld = Nothing
    Set db = Nothing
End Sub

When I test your data with this I get these messages.

LK_CIV_MIL has 2 records
Field 'XYZ' added to LK_CIV_MIL
LK_EMERGENCY_ROOM has 4 records
Field 'XYZ' added to LK_EMERGENCY_ROOM
Finished 2 tables received new specified field(s).

jack


Update: It isn't rsLK that you want recordcounts from. You need to evaluate the individual tables whose names are in the rsLK recordset.

[skydivetom]

Jack:

Your solution is PERFECT! Many thanks for providing an alternative method. Only 2 tables get updated... yeah!

Final question... is there a way to insert the new field "XYZ" into a specific position between existing fields? That is, instead of [XYZ] being the last field, I'd like to have it inserted between [TARGET_VALUE] and [DEFAULT_VALUE]. Can the field position be coded? If so, how?

[orange]

Hi Tom,
Adjusted as below. In your tables, the position of the new field is the same for the tables involved in your database.

Public Sub AddFieldsJ()
          
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim n As Integer
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If tdf.Name Like "LK_*" Then
            If tdf.RecordCount > 1 Then
                Debug.Print tdf.Name & " has " & tdf.RecordCount & " records"
                'Add [TARGET_STRING] (Text)
                With tdf.Fields
                    Set fld = New DAO.Field
                    fld.Name = "XYZ"                'Field to be added!
                    fld.Type = dbText
                    fld.OrdinalPosition = 4 ' placement of the new field before the Append
                    .Append fld
                    Debug.Print "Field 'XYZ' added to " & tdf.Name & " in position 4"
                    n = n + 1
                    GoTo get_next_tdf
                End With
            End If
        End If
get_next_tdf:
    Next tdf
    Debug.Print "Finished " & n & " tables received new specified field(s)."
    MsgBox n & " tables received new specified field(s).", vbInformation, "Status"
    Set fld = Nothing
    Set db = Nothing
End Sub

Debug.print results
LK_CIV_MIL has 2 records
Field 'XYZ' added to LK_CIV_MIL in position 4
LK_EMERGENCY_ROOM has 4 records
Field 'XYZ' added to LK_EMERGENCY_ROOM in position 4
Finished 2 tables received new specified field(s).

[skydivetom]

Jack -- your solution is PERFECT!! Thank you so very much for the assistance.

Cheers,
Tom