Adding controls to a form and pointing to a field in TblDefs.Fields

[frothytoad]

Hello all,

I have been running into two problems that have me completely stumped, and I am hoping someone can offer an answer for one or both.

The first has to do with creating controls programmatically. For some reason, when I try to add controls to my form (in design view), it comes back and tells me that the control cannot be added (but gives no reason). The code that is doing this is (in the parent form's Load event):


Dim ctl As TextBox
Dim s() As String
Dim newName As String

...

DoCmd.OpenForm "sfrmEquipmentDatasheet", acDesign

For Each f In fieldColls(0) 'fieldColls is an array of Collections
s = Split(f.desc, ":") 'f is a class with a property 'desc'
If Left(s(0), 1) = "_" Then
newName = s(2)
ElseIf Len(s(0)) = 0 Then
newName = s(4)
Else
newName = s(0)
End If
If Not hasElement(Form_sfrmEquipmentDatasheet.Controls, newName) Then
Set ctl = CreateControl("sfrmEquipmentDatasheet", acTextBox) '!!!This is where the error occurs!!!
ctl.ControlSource = f.name
ctl.name = newName
ctl.ColumnHidden = True
End If
Next f


The second questions has to do with the fields in a TableDef. My original plan was to create collections of fields by using the existing field objects in the fields of table definitions. But when I tried this:

dim fld as Field
set fld = CurrentDB.TableDefs("myTable").Fields("myField")

...it came back with a Type Mismatch error. When I made fld more generic, it responded that an object no longer existed (referring, I believe, to the fields collection).

Any suggestions?

Thanks!

-- Jeff

 

[Golom]

On the second one

dim fld as [red]DAO.[/red]Field

There are several "Field" objects among them, "Scripting", "ADODB", "DAO" (depending on the references that you have set.) You must use a DAO.Field for this.

 

[PHV]

Type Mismatch error
What about this ?
Dim db As DAO.DataBase
Dim td As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set td = db.TableDefs("myTable")
Set fld = td.Fields("myField")

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

 

[frothytoad]

Golom,

This gets me one step further, but the variable still seems to immediately go out of scope. For example:

Dim f As DAO.field
Set f = CurrentDb.TableDefs("tblEquipment").Fields(0)
Debug.Print f.name

...gives an "Object invalid or no longer set" error (runtime, 3420).

Thoughts?

-- Jeff

 

[frothytoad]

PH,

That seems to be the catch. Retaining a reference to the specific TableDef keeps the Field point from going out of scope.

Thanks!

-- Jeff

 

[Golom]

Strange. This runs fine

Private Sub Command16_Click()
    Stop
    Dim f                           As DAO.Field
    Dim db                          As DAO.Database
    Set db = DAO.DBEngine(0).OpenDatabase(".\myDB.mdb")
    Set f = db.TableDefs("aaa").Fields(0)
    Debug.Print f.Name
End Sub

What does the rest of the code look like?

 

[PHV]

Golom, what happens if you replace this:
Set f = db.TableDefs("aaa").Fields(0)
with this ?
Set f = CurrentDb.TableDefs("aaa").Fields(0)

 

[Golom]

PHV

Gives me the same error that frothytoad is getting.

But ... and I have no idea why ... this runs

    Dim db                          As DAO.Database
    Dim f                           As DAO.Field
    Set db = CurrentDb
    Set f = db.TableDefs("aaa").Fields(0)
    Debug.Print f.Name

You're much better at Access than I am PHV. How come?

 

[frothytoad]

Golom,

My guess is that it is the local reference to the database (via db) that makes the difference. Does this modification to your code still work?

Private Sub Command16_Click()
    Stop
    Dim f                           As DAO.Field
    Dim db                          As DAO.Database
    Set db = DAO.DBEngine(0).OpenDatabase(".\myDB.mdb")
    Set f = db.TableDefs("aaa").Fields(0)
[blue]    set db = Nothing[/blue]
    Debug.Print f.Name
End Sub

 

[Golom]

No ... but I know why that doesn't work. I've destroyed the reference on which "f" depends.

CurrentDb however, is (I thought), a global object that exists for the life of the application unless you deliberately do something to destroy it. The first set of code is acting like (as you said) CurrentDb has gone out of scope somewhere between "Set f =" and "Debug.Print ...".

Weird!

 

[Golom]

Just to add to the confusion, this also works

Debug.Print CurrentDb.TableDefs("aaa").Fields(0).Name

 

[frothytoad]

It seems like CurrentDB (which is a method on Application), rather than being a global object itself, is newly regenerated as an object reference every time it is invoked. I guess that makes some sense in the case that the application might utilize multiple databases, so the "current" one cannot be a static reference.

-- J

 

[Golom]

And here's the answer!

CurrentDb is not in fact, a database object. It is rather a method that returns a reference to a database object and that goes out of scope as soon as the command is processed.