"Type Mismatch" error when trying to pass DAO.Tabledef to object function

[hydroparadise]

Hey guys, first time posting. I can't figure out why I keep getting a "Type
Mismatch" Error when passing a DAO.Tabledef object into a user defined class
function. Check out the impelementation...

<class module start: FieldCategories>
Option Compare Database
Option Explicit

Public Function GetFields(td As DAO.TableDef)
Msgbox td.Fields.Count
End Function
<class module end>

Dim field1 as FieldCategories
Set field1 = New FieldCategories

Dim td1 as DAO.TableDef
'Usualy write out entire path for best practice
Set td1 = DBEngine.Workpsaces(0).Databases(0).TableDefs("Table1")

td1.GetFields(td1) '<<Gets "Type Mismatch" Error


Not sure what im doing wrong. Maybe its something stupid. I hope so!

 

[Douglas J. Steele]

GetFields is a function, not a method of a recordset.

All you should need is something like

Msgbox "There are " & GetFields(td1) & " fields in the recordset"

 

[hydroparadise]

GetFields is a function, not a method of a recordset.

All you should need is something like

Msgbox "There are " & GetFields(td1) & " fields in the recordset"

Hey guys, first time posting. I can't figure out why I keep getting a
"Type

[quoted text clipped - 21 lines]

Not sure what im doing wrong. Maybe its something stupid. I hope so!

I take it that i'm confusing a function as method where i should have used a
sub procedure? Comming from a c++ background, using functions were the
methods of the class. Set me straight here.

 

[hydroparadise]

GetFields is a function, not a method of a recordset.

[quoted text clipped - 7 lines]
I take it that i'm confusing a function as method where i should have used a
sub procedure? Comming from a c++ background, using functions were the
methods of the class. Set me straight here.

I think it would be worth it to mention that my goal is to make a class
definition with a method to accept a DAO Table Definition.

 

[Douglas J. Steele]

Actually, I missed the fact that GetFields is in a class.

GetFields is a method of the class, not of the recordset.

Dim field1 as FieldCategories
Dim td1 as DAO.TableDef

Set field1 = New FieldCategories

'Usualy write out entire path for best practice
Set td1 = DBEngine.Workpsaces(0).Databases(0).TableDefs("Table1")

field1.GetFields(td1)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

GetFields is a function, not a method of a recordset.

All you should need is something like

Msgbox "There are " & GetFields(td1) & " fields in the recordset"

Hey guys, first time posting. I can't figure out why I keep getting a
"Type

[quoted text clipped - 21 lines]

Not sure what im doing wrong. Maybe its something stupid. I hope so!

I take it that i'm confusing a function as method where i should have used
a
sub procedure? Comming from a c++ background, using functions were the
methods of the class. Set me straight here.

 

[Douglas J. Steele]

And even there, I made an error!

You can't just have

field1.GetFields(td1)

You need to assign the result of the call to a variable:

Dim lngFieldCount As Long

lngFieldCount = field1.GetFields(td1)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, I missed the fact that GetFields is in a class.

GetFields is a method of the class, not of the recordset.

Dim field1 as FieldCategories
Dim td1 as DAO.TableDef

Set field1 = New FieldCategories

'Usualy write out entire path for best practice
Set td1 = DBEngine.Workpsaces(0).Databases(0).TableDefs("Table1")

field1.GetFields(td1)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

GetFields is a function, not a method of a recordset.

All you should need is something like

Msgbox "There are " & GetFields(td1) & " fields in the recordset"

Hey guys, first time posting. I can't figure out why I keep getting a
"Type
[quoted text clipped - 21 lines]

Not sure what im doing wrong. Maybe its something stupid. I hope so!

I take it that i'm confusing a function as method where i should have
used a
sub procedure? Comming from a c++ background, using functions were the
methods of the class. Set me straight here.

 

[hydroparadise]

Im sorry, I made a mistake as well. The last line of code I submitted should
have been:
field1.GetFields(td1) '<< returns "Type mismatch"

The GetFields method of my FieldCategory class simply takes a DAO.TableDef
object and extracts the field names to a String array within the
FieldCategory class. The GetFields function is not supposed return anything.

And even there, I made an error!

You can't just have

field1.GetFields(td1)

You need to assign the result of the call to a variable:

Dim lngFieldCount As Long

lngFieldCount = field1.GetFields(td1)

Actually, I missed the fact that GetFields is in a class.

[quoted text clipped - 26 lines]

 

[Douglas J. Steele]

Actually your GetFields function doesn't do anything (other than raise a
message box).

Your original post shows it as

Public Function GetFields(td As DAO.TableDef)
Msgbox td.Fields.Count
End Function

 

[Dirk Goldgar]

Im sorry, I made a mistake as well. The last line of code I submitted
should
have been:
field1.GetFields(td1) '<< returns "Type mismatch"

Try:

field1.GetFields td1

 

[hydroparadise]

Try:

field1.GetFields td1

Today is just not my day.

Tried Returns:

Invalid Operation

 

[Dirk Goldgar]

Today is just not my day.

Tried Returns:

Invalid Operation

I suspect that you have not posted all the code in your class module, and
that you are doing something wrong in there. This works just fine for me:

' ----- start of class module FieldCategories -----
Option Compare Database
Option Explicit

Dim mstrFieldList As String

Public Property Get FieldList() As String

FieldList = mstrFieldList

End Property


Public Function GetFields(td As DAO.TableDef)

Dim i As Integer

mstrFieldList = vbNullString

For i = 0 To td.Fields.Count - 1
mstrFieldList = mstrFieldList & ", " & td.Fields(i).Name
Next i

mstrFieldList = Mid$(mstrFieldList, 3)

MsgBox td.Fields.Count

End Function
' ----- end of class module FieldCategories -----

'----- start of test code in a standard module -----
Sub TestFieldCategories()

Dim field1 As FieldCategories
Set field1 = New FieldCategories

Dim td1 As DAO.TableDef
Set td1 = DBEngine.Workspaces(0).Databases(0).TableDefs("Table1")

field1.GetFields td1

Debug.Print field1.FieldList

End Sub
'----- end of test code -----

 

[hydroparadise via AccessMonster.com]

I did omit some code before to help with brevity. Here is the entire set that
i am working with. my code is very similar to what Dirk Golgar submitted
(without the use of properties)

'-------Start Class Module FieldCategories-------
Option Compare Database
Option Explicit

Enum TreeLevel
tlUnknown = -1
tlBottom = 0
tlTop = 1
tlMiddle = 2
End Enum

Dim Fields() As String
Public Name As String
Dim FieldCount As Integer

Public Sub GetFields(td As DAO.TableDef)
Dim cnt As Integer

FieldCount = td.Fields.Count
ReDim Fields(FieldCount)

For cnt = 0 To FieldCount - 1
Fields(cnt) = td.Fields(cnt)
Next cnt

End Sub


Public Function ShowFields()
Dim cnt As Integer

For cnt = 0 To FieldCount - 1
MsgBox Fields(cnt)
Next cnt

End Function
'-------End Class Module FieldCategories-------

'----------------Start Standard Modue----------------
Private Sub Command0_Click()

Dim field1 As FieldCategories
Dim td1 As DAO.TableDef

Set field1 = New FieldCategories
Set td1 = DBEngine.Workspaces(0).Databases(0).TableDefs("MarioBrothers")

field1.GetFields (td1) '<<--"Type Mismatch" Err
field1.ShowFields

Set field1 = Nothing

End Sub
'----------------End Standard Modue----------------

[quoted text clipped - 10 lines]

Invalid Operation

I suspect that you have not posted all the code in your class module, and
that you are doing something wrong in there. This works just fine for me:

' ----- start of class module FieldCategories -----
Option Compare Database
Option Explicit

Dim mstrFieldList As String

Public Property Get FieldList() As String

FieldList = mstrFieldList

End Property

Public Function GetFields(td As DAO.TableDef)

Dim i As Integer

mstrFieldList = vbNullString

For i = 0 To td.Fields.Count - 1
mstrFieldList = mstrFieldList & ", " & td.Fields(i).Name
Next i

mstrFieldList = Mid$(mstrFieldList, 3)

MsgBox td.Fields.Count

End Function
' ----- end of class module FieldCategories -----

'----- start of test code in a standard module -----
Sub TestFieldCategories()

Dim field1 As FieldCategories
Set field1 = New FieldCategories

Dim td1 As DAO.TableDef
Set td1 = DBEngine.Workspaces(0).Databases(0).TableDefs("Table1")

field1.GetFields td1

Debug.Print field1.FieldList

End Sub
'----- end of test code -----

 

[Dirk Goldgar]

I did omit some code before to help with brevity. Here is the entire set
that
i am working with. my code is very similar to what Dirk Golgar submitted
(without the use of properties)

'-------Start Class Module FieldCategories-------
Option Compare Database
Option Explicit

Enum TreeLevel
tlUnknown = -1
tlBottom = 0
tlTop = 1
tlMiddle = 2
End Enum

Dim Fields() As String
Public Name As String
Dim FieldCount As Integer

Public Sub GetFields(td As DAO.TableDef)
Dim cnt As Integer

FieldCount = td.Fields.Count
ReDim Fields(FieldCount)

For cnt = 0 To FieldCount - 1
Fields(cnt) = td.Fields(cnt)
Next cnt

End Sub


Public Function ShowFields()
Dim cnt As Integer

For cnt = 0 To FieldCount - 1
MsgBox Fields(cnt)
Next cnt

End Function
'-------End Class Module FieldCategories-------

'----------------Start Standard Modue----------------
Private Sub Command0_Click()

Dim field1 As FieldCategories
Dim td1 As DAO.TableDef

Set field1 = New FieldCategories
Set td1 = DBEngine.Workspaces(0).Databases(0).TableDefs("MarioBrothers")

field1.GetFields (td1) '<<--"Type Mismatch" Err
field1.ShowFields

Set field1 = Nothing

End Sub
'----------------End Standard Modue----------------

There are two errors in the code you have now posted. First, as I said
before, this line:

field1.GetFields (td1) '<<--"Type Mismatch" Err

.... is incorrect. You must either remove the parentheses around td1:

field1.GetFields td1

.... or else use the Call statement:

Call field1.GetFields(td1)

The syntax you were using would cause VB to attempt to evaluate td1 as an
expression and pass the value of the expression to the function. This
fails, as you have seen.

Second, this line is in error:

Fields(cnt) = td.Fields(cnt)

That attempts to store a Field object in an array of type String. You can't
do that, so you get an Invalid Operation error. From what you've said, your
line of code should use the field's Name property:

Fields(cnt) = td.Fields(cnt).Name

That ought to work -- it does in my test.

 

[RoyVidar]

I did omit some code before to help with brevity. Here is the entire
set that i am working with. my code is very similar to what Dirk
Golgar submitted (without the use of properties)

'-------Start Class Module FieldCategories-------
Option Compare Database
Option Explicit

Enum TreeLevel
tlUnknown = -1
tlBottom = 0
tlTop = 1
tlMiddle = 2
End Enum

Dim Fields() As String
Public Name As String
Dim FieldCount As Integer

Public Sub GetFields(td As DAO.TableDef)
Dim cnt As Integer

FieldCount = td.Fields.Count
ReDim Fields(FieldCount)

For cnt = 0 To FieldCount - 1
Fields(cnt) = td.Fields(cnt)
Next cnt

End Sub


Public Function ShowFields()
Dim cnt As Integer

For cnt = 0 To FieldCount - 1
MsgBox Fields(cnt)
Next cnt

End Function
'-------End Class Module FieldCategories-------

'----------------Start Standard Modue----------------
Private Sub Command0_Click()

Dim field1 As FieldCategories
Dim td1 As DAO.TableDef

Set field1 = New FieldCategories
Set td1 =
DBEngine.Workspaces(0).Databases(0).TableDefs("MarioBrothers")

field1.GetFields (td1) '<<--"Type Mismatch" Err
field1.ShowFields

Set field1 = Nothing

End Sub
'----------------End Standard Modue----------------

Im sorry, I made a mistake as well. The last line of code I
submitted should [quoted text clipped - 10 lines]

Invalid Operation

I suspect that you have not posted all the code in your class
module, and that you are doing something wrong in there. This
works just fine for me:

' ----- start of class module FieldCategories -----
Option Compare Database
Option Explicit

Dim mstrFieldList As String

Public Property Get FieldList() As String

FieldList = mstrFieldList

End Property

Public Function GetFields(td As DAO.TableDef)

Dim i As Integer

mstrFieldList = vbNullString

For i = 0 To td.Fields.Count - 1
mstrFieldList = mstrFieldList & ", " & td.Fields(i).Name
Next i

mstrFieldList = Mid$(mstrFieldList, 3)

MsgBox td.Fields.Count

End Function
' ----- end of class module FieldCategories -----

'----- start of test code in a standard module -----
Sub TestFieldCategories()

Dim field1 As FieldCategories
Set field1 = New FieldCategories

Dim td1 As DAO.TableDef
Set td1 = DBEngine.Workspaces(0).Databases(0).TableDefs("Table1")

field1.GetFields td1

Debug.Print field1.FieldList

End Sub
'----- end of test code -----

Dirk Goldgars sample

field1.GetFields td1

passes the tabledef object by reference (which is what is needed),
while your code

field1.GetFields (td1)

passes the tabledef object by value, which is probably what is
giving the Type Mismatch error.

At least in regular code, you can also call functions that do not
return values with

Call field1.GetFields(td1)

 

[Jim Burke in Novi]

I haven't bothered to read though all of the replies here, so by now you may
not even need this info, but ...

1) To define a method, subroutine or function with a DAO Tabledef as a
parameter you would do it as exactly you have it shown here:

Public Function GetFields(td As DAO.TableDef)

That is a perfectly valid way of doing that. I always add the ByRef/ByVal
parm when doing this, so I would personally make it:

Public Function GetFields(BYRef td As DAO.TableDef)

I can never remember if ByRef or ByVal is the default, but for a TableDef
I'm pretty sure it has to be ByREf, since a tabledef is an Object

2) As I think Doug mentioned, this is not valid:

td1.GetFields(td1)

td1 is defined as a TabelDef. TabelDefs do not have a method called
GetFields - GetFields is a function that you defined that is not associated
with any Access class.
You would need to do something like this:

x = GetFields(td1)

to assign a value from GetFields, although in your example you did not
define a function that returns a value, you are only displaying a messagebox
in your function. If you wanted to have a function that returned the number
of fields in a tabledef, you would do something like this:

Public Function GetFields(ByRef td As DAO.TableDef) as long
GetFields = td.Fields.Count
End Function

 

[hydroparadise via AccessMonster.com]

Thanks for everybody's help. I finally found my problem. The byval aproach (
fields1.GetFields(td1) ) was what was causing my the "Datatype mismatch"
error as previously mentioned by you guys. The reason the byref wasnt working
was due to the code inside the function. The IDE kept pointing to the
function call when the code inside the function was causing the Invalid
Operation error. Well noted: Objects always get passed by reference!

I haven't bothered to read though all of the replies here, so by now you may
not even need this info, but ...

1) To define a method, subroutine or function with a DAO Tabledef as a
parameter you would do it as exactly you have it shown here:

Public Function GetFields(td As DAO.TableDef)

That is a perfectly valid way of doing that. I always add the ByRef/ByVal
parm when doing this, so I would personally make it:

Public Function GetFields(BYRef td As DAO.TableDef)

I can never remember if ByRef or ByVal is the default, but for a TableDef
I'm pretty sure it has to be ByREf, since a tabledef is an Object

2) As I think Doug mentioned, this is not valid:

td1.GetFields(td1)

td1 is defined as a TabelDef. TabelDefs do not have a method called
GetFields - GetFields is a function that you defined that is not associated
with any Access class.
You would need to do something like this:

x = GetFields(td1)

to assign a value from GetFields, although in your example you did not
define a function that returns a value, you are only displaying a messagebox
in your function. If you wanted to have a function that returned the number
of fields in a tabledef, you would do something like this:

Public Function GetFields(ByRef td As DAO.TableDef) as long
GetFields = td.Fields.Count
End Function

Hey guys, first time posting. I can't figure out why I keep getting a "Type
Mismatch" Error when passing a DAO.Tabledef object into a user defined class

[quoted text clipped - 19 lines]

Not sure what im doing wrong. Maybe its something stupid. I hope so!

 

[Dirk Goldgar]

The reason the byref wasnt working
was due to the code inside the function. The IDE kept pointing to the
function call when the code inside the function was causing the Invalid
Operation error.

If you had stepped into the function with the debugger, I think you'd have
found which line in the function was causing that error.