Modular Level Array Issue

[AHJ1]

This code works, but I want to populate an array at a modular level so that other routines can take advantage of it.

Sub FindBDFieldNames(strTableName As String)
'By convention the last two digits of tables that have balances due are "BD".
'We want to loop through a table and discover each of these fields.
On Error GoTo err_FFN
    Dim rst As DAO.Recordset
    Dim f As Field
    Dim i As Integer
    Dim iLoop As Integer
    Set rst = CurrentDb.OpenRecordset(strTableName)
    
    For Each f In rst.Fields
      
        If Right(f.name, 2) = "BD" Then
            i = i + 1
            ReDim arrFNames(i) As BDFieldNames
            MsgBox f.name
             arrFNames(i).BDFieldName = f.name
             arrFNames(i).Entered = False
            
            'ReDim Preserve marrFieldNames(0 To i) As BDFieldNames
            Debug.Print (i & " - " & arrFNames(i).BDFieldName & " - " & arrFNames(i).Entered)
            rst.MoveNext
        End If
       
    Next f
    rst.Close
Exit Sub
err_FFN:
ErrBox "finding field names and populating an array with them in FindBDFieldNames"
End Sub

I have tried something like this at the top of the module

Dim marrBDFNames As BDFieldNames

This declaration is at the top of the same module in which I writing the code. I have changed the routine as follows.

Sub ProcessBDFieldNames(strTableName As String)
'By convention the last two digits of tables that have balances due are "BD".
'We want to loop through a table and discover each of these fields.
On Error GoTo err_FFN
    Dim rst As DAO.Recordset
    Dim f As Field
    Dim i As Integer
    Dim iLoop As Integer
    Set rst = CurrentDb.OpenRecordset(strTableName)
    
    For Each f In rst.Fields
      
        If Right(f.name, 2) = "BD" Then
            i = i + 1
            ReDim marrFNames(i)
            MsgBox f.name
             marrFNames(i).BDFieldName = f.name
             marrFNames(i).Entered = False
            
            'ReDim Preserve marrFieldNames(0 To i) As BDFieldNames
            Debug.Print (i & " - " & marrFNames(i).BDFieldName & " - " & marrFNames(i).Entered)
            rst.MoveNext
        End If
       
    Next f
    rst.Close
Exit Sub
err_FFN:
ErrBox "finding field names and populating an array with them in FindBDFieldNames"
End Sub

When I reach this line "marrFNames(i).BDFieldName = f.name", I get an object required message.

Any insights will be appreciated.

 

[PHV]

What is BDFieldNames ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Have a look at STATIC in the declaration...

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[AHJ1]

Whoops,

Sorry. Here's the definition of BDFieldNames

Type BDFieldNames
    BDFieldName As String * 9
    Entered As Boolean
End Type

 

[AHJ1]

Regarding the use of Static. When I use this declaration

Option Compare Database
Option Explicit
Global glngNextID As Long
Static marrBDFNames As BDFieldNames

I receive an "Invalid outside of Procedure" error message.
Using Public as a declaration does not change the results.

Hmmm. I changed the declaration to

Option Compare Database
Option Explicit
Global glngNextID As Long
Type BDFieldNames
    BDFieldName As String * 9
    Entered As Boolean
End Type
Dim marrFNames As BDFieldNames

When I run this sub . . .

Sub ProcessBDFieldNames(strTableName As String)
'By convention the last two digits of tables that have balances due are "BD".
'We want to loop through a table and discover each of these fields.
On Error GoTo err_FFN
    Dim rst As DAO.Recordset
    Dim f As Field
    Dim i As Integer
    Dim iLoop As Integer
    Set rst = CurrentDb.OpenRecordset(strTableName)
    
    For Each f In rst.Fields
      
        If Right(f.name, 2) = "BD" Then
            i = i + 1
            ReDim marrFNames(i)
            MsgBox f.name
             marrFNames(i).BDFieldName = f.name
             marrFNames(i).Entered = False
            Debug.Print (i & " - " & marrFNames(i).BDFieldName & " - " & marrFNames(i).Entered)
            rst.MoveNext
        End If
       
    Next f
    rst.Close
Exit Sub
err_FFN:
ErrBox "finding field names and populating an array with them in ProcessBDFieldNames"
End Sub

. . . I now get "Compile error. Expected array."

Thanks, in advance.
Alan

 

[MajP]

I would use a collection instead of an array. I think it would be easier. You do not have to worry about dimming and redimming. At the top of the module

public mFNames as collection

 

[AHJ1]

MajP,

That makes sense. I had decided to create a temporary table instead, but a collection would have less overhead. Can you point me to an explanation of the best way to populate it? I get an "object variable or with block variable not set" error. Here's what I tried:

Sub BuildFieldNameTable(strTableName As String)
'By convention the last two digits of tables that have balances due are "BD".
'We want to loop through a table and discover each of these fields.
On Error GoTo err_FFN
    Dim rst As DAO.Recordset
    Dim f As Field
    Dim i As Integer
    Dim iLoop As Integer
    Dim strSQL As String
    Set rst = CurrentDb.OpenRecordset(strTableName)
    DeleteRecsInTable "TempTableProcessing"
    For Each f In rst.Fields
      
        If Right(f.name, 2) = "BD" Then
            i = i + 1
            'MsgBox f.name
            mcolFNames.Add f.name '--> error here
            strSQL = "INSERT INTO TempTableProcessing (TableName, FieldName) VALUES (""" & strTableName & """,""" & f.name & """);"
            DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            rst.MoveNext
        End If
       
    Next f
    rst.Close
Exit Sub
err_FFN:
ErrBox "finding field names and populating an array with them in FindBDFieldNames"
End Sub

 

[MajP]

you can either declare a variable and then instantiate it later or do it all at once

public mcolFNames as collection
then in you code
set mcolFNames = new collection


or all at once

public mcolFnames as new collection
Then in your code you can use it right away.

You are trying to use a collection, but you have only declared it (reserved memory). You never instantiate it as a new collection. Normally in VBA you are working with things that already exist (Forms, Controls, etc) so you are not creating a new object, but in this case you are creating a new collection

 

[AHJ1]

Hi MajP,

I tried to iterate through the collection using this code, but it comes up with an object not set error. If it's not too much trouble, what am I doing wrong?

Sub IterateCollection()
Dim i As Integer
Dim MyCollection As Collection
Set MyCollection = mcolFNames
For i = 0 To MyCollection.Count - 1
    Debug.Print mcolFNames.Item(i)
    
Next
End Sub

 

[MajP]

Where do you instantiate mcolFNames? Did you read my last post? If mcolFNames is not instantiated then you can not set myCollection to mcolFNames because "the mcolFnames object is not Set".

 

[AHJ1]

I instantiated it in this sub, and it creates a collection and successfully populates it. Perhaps it goes out of scope after the sub closes? I was hoping that it would stay active because it was originally declared on the modular level.

Sub BuildFieldNameTable(strTableName As String)
'By convention the last two digits of tables that have balances due are "BD".
'We want to loop through a table and discover each of these fields.
On Error GoTo err_FFN
    Dim rst As DAO.Recordset
    Dim f As Field
    Dim i As Integer
    Dim iLoop As Integer
    Dim strSQL As String
    Set rst = CurrentDb.OpenRecordset(strTableName)
    DeleteRecsInTable "TempTableProcessing"
    Set mcolFNames = New Collection
    For Each f In rst.Fields
         
        If Right(f.name, 2) = "BD" Then
            i = i + 1
            'MsgBox f.name
            mcolFNames.Add f.name
            strSQL = "INSERT INTO TempTableProcessing (TableName, FieldName) VALUES (""" & strTableName & """,""" & f.name & """);"
            DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            rst.MoveNext
        End If
       
    Next f
    rst.Close
Exit Sub
err_FFN:
ErrBox "finding field names and populating an array with them in FindBDFieldNames"
End Sub

 

[MajP]

If the module is a "standard module" and you dimension it as
"public" then it will be available. If it in a form's or report's module (or a class module) it will go out of scope if the form or report is closed.

 

[AHJ1]

Hi,

I'm not comfortable asking this, as I think I should know, but how do I declare a public module? The only way that I know to create a module is to use the Database Window interface, and that seems to create only standard modules.

Alan

 

[MajP]

You are correct. Yes, you want to do this in a standard module. Just ensure you declare the collection as public at the top of the module. If you declare it as private it would only be available to the routines in the module but not by other subroutines in other modules
public mcolFNames as collection

What I was saying you do not want to do this in a form's module or report's module. These are actually class modules and the variable goes out of scope when the form or report closes.

 

[AHJ1]

Actually, I did declare it in a standard module, not a class module, and I did declare it as follows.

Option Compare Database
Option Explicit
Public mcolFNames As Collection

It goes out of scope. Could that be because it is set in a subroutine that goes out of scope?

Alan

 

[MajP]

How about just this to iterate your collection

Sub IterateCollection()
Dim i As Integer
For i = 0 To mcolFNames.Count - 1
    Debug.Print mcolFNames.Item(i)
Next 
End Sub

The fact that the subroutine completes should have no effect on the public variable going out of scope

 

[AHJ1]

It's definitely a scoping issue. When I take the code and put it in the same procedure, it runs. The trouble is that I don't need it there.

I'll play with it a little while. If I run out of time, I'll write it to a temporary table. There's not that much data, and though it will be slower, I know the data will persist.

If you have any other thoughts, let me know. Meanwhile, thanks very much for all of your effort to date.