VBA error - please help with explanation

[MikeB]

I get the following error message at run-time:

Only public user defined types defined in public object modules can be
used as parameters or return types for public procedures of class
modules or as fields of public user defined types

This error has the following cause and solution:

You attempted to use a public user defined type as a parameter or
return type for a public procedure of a class module, or as a field of
a public user defined type. Only public user defined types that are
defined in a public object module can be used in this manner.

My code:

Option Compare Database
Option Explicit

Public Type Player
Fname As String
Lname As String
Rating As Integer
Games As Integer
End Type

Sub x1()
Dim dbs As DAO.Database

Dim rsGames As DAO.Recordset
Dim rsGameCount As DAO.Recordset

Const strSQLGames As String = "SELECT * from [Games] " & _
"WHERE (([White] Not In (50,51)) AND ([Black] Not In
(50,51)))"

Dim White As Player
Dim Black As Player

Dim strGameNumber As String
Dim strmatchNumber As String
Dim strWhiteId As String
Dim strBlackId As String

Dim dblWhiteScore As Double
Dim dblBlackScore As Double

Dim intWhiteRating As Integer
Dim intBlackRating As Integer
Dim intWhiteGameCount As Integer
Dim intBlackGameCount As Integer

Set dbs = CurrentDb

' Get the unrated games in the database
Set rsGames = dbs.OpenRecordset(strSQLGames, dbOpenDynaset)

' For each unrated game:

Do While Not rsGames.EOF
strGameNumber = rsGames!GameNumber
strmatchNumber = rsGames!MatchNumber
strWhiteId = rsGames!White
strBlackId = rsGames!Black
dblWhiteScore = rsGames!WhiteScore
dblBlackScore = rsGames!BlackScore

' Retrieve White player info
White = getPlayer(strWhiteId)
White.Games = getPlayerGameCount(strWhiteId)

'Retrieve Black Player info
Black = getPlayer(strBlackId)
Black.Games = getPlayerGameCount(strBlack)

Debug.Print "Game(" & strGameNumber & ")"

'
' Tab(12); "Match(" & strmatchNumber & ")" _
' Tab(25); "White:" & "(" & strWhiteId & ")" & _
' White.Fname & " " & White.Lname & _
' "(" & White.Rating & ")" & " Rated Games(" &
White.Games & ")" & _
' Tab(60); "Black:" & "(" & strBlackId & ")" & _
' Black.Fname & " " & Black.Lname & _
' "(" & Black.Rating & ")" & " Rated Games(" &
Black.Games & ")" , _
' dblWhiteScore, dblBlackScore

rsGames.MoveNext
Loop

rsGames.Close
Set rsGames = Nothing


End Sub

Function getPlayer(ByVal id As String)
' look up a player in the database by his player number as a String
type.
' Player number is really an Integer, but String seems to work just
fine

Dim getPlayer As Player
Dim rsPlayer As DAO.Recordset
Const strSQL As String = "Select * from [Players] where
[PlayerNumber] ="
Set rsPlayer = dbs.OpenRecordset(strSQLPlayer & id, dbOpenDynaset)
getPlayer.Fname = rsPlayer!FirstName
getPlayer.Lname = rsPlayer!LastName
getPlayer.Rating = rsPlayer!Ranking
rsPlayer.Close
Set rsPlayer = Nothing

End Function

Function getPlayerGameCount(ByVal id As String)
Dim getPlayerGameCount As Integer
Dim rsPlayerGameCount As DAO.Recordset
Dim qdfPlayerGameCount As DAO.QueryDef

Set qdfPlayerGameCount = dbs.QueryDefs("PlayerGameCount")
qdfPlayerGameCount.Parameters("Player?") = id
Set rsPlayerGameCount = qdfPlayerGameCount.OpenRecordset
getPlayerGameCount = rsGameCount!PlayerGameCount
rsPlayerGameCount.Close
Set rsPlayerGameCount = Nothing
qdfPlayerGameCount.Close
Set qdfGameCount = Nothing

 

[Clif McIrvin]

read the help on user defined functions a bit more carefully ...
getPlayer is defined by the Function statement. You left the type
delcaration off, so VBA types it as a variant. then when VBA hits your
Dim getPlayer it chokes. Try:

Function getPlayer(ByVal id As String) As Player

(etc), and

Function getPlayerGameCount(ByVal id As String) getPlayerGameCount As
Integer

--
Clif

Function getPlayer(ByVal id As String)
' look up a player in the database by his player number as a String
type.
' Player number is really an Integer, but String seems to work just
fine

Dim getPlayer As Player

Function getPlayerGameCount(ByVal id As String)
Dim getPlayerGameCount As Integer

I get the following error message at run-time:

Only public user defined types defined in public object modules can be
used as parameters or return types for public procedures of class
modules or as fields of public user defined types


This error has the following cause and solution:

You attempted to use a public user defined type as a parameter or
return type for a public procedure of a class module, or as a field of
a public user defined type. Only public user defined types that are
defined in a public object module can be used in this manner.

My code:

Option Compare Database
Option Explicit

Public Type Player
Fname As String
Lname As String
Rating As Integer
Games As Integer
End Type

Sub x1()
Dim dbs As DAO.Database

Dim rsGames As DAO.Recordset
Dim rsGameCount As DAO.Recordset

Const strSQLGames As String = "SELECT * from [Games] " & _
"WHERE (([White] Not In (50,51)) AND ([Black] Not In
(50,51)))"

Dim White As Player
Dim Black As Player

Dim strGameNumber As String
Dim strmatchNumber As String
Dim strWhiteId As String
Dim strBlackId As String

Dim dblWhiteScore As Double
Dim dblBlackScore As Double

Dim intWhiteRating As Integer
Dim intBlackRating As Integer
Dim intWhiteGameCount As Integer
Dim intBlackGameCount As Integer

Set dbs = CurrentDb

' Get the unrated games in the database
Set rsGames = dbs.OpenRecordset(strSQLGames, dbOpenDynaset)

' For each unrated game:

Do While Not rsGames.EOF
strGameNumber = rsGames!GameNumber
strmatchNumber = rsGames!MatchNumber
strWhiteId = rsGames!White
strBlackId = rsGames!Black
dblWhiteScore = rsGames!WhiteScore
dblBlackScore = rsGames!BlackScore

' Retrieve White player info
White = getPlayer(strWhiteId)
White.Games = getPlayerGameCount(strWhiteId)

'Retrieve Black Player info
Black = getPlayer(strBlackId)
Black.Games = getPlayerGameCount(strBlack)

Debug.Print "Game(" & strGameNumber & ")"

'
' Tab(12); "Match(" & strmatchNumber & ")" _
' Tab(25); "White:" & "(" & strWhiteId & ")" & _
' White.Fname & " " & White.Lname & _
' "(" & White.Rating & ")" & " Rated Games(" &
White.Games & ")" & _
' Tab(60); "Black:" & "(" & strBlackId & ")" & _
' Black.Fname & " " & Black.Lname & _
' "(" & Black.Rating & ")" & " Rated Games(" &
Black.Games & ")" , _
' dblWhiteScore, dblBlackScore

rsGames.MoveNext
Loop

rsGames.Close
Set rsGames = Nothing


End Sub

Function getPlayer(ByVal id As String)
' look up a player in the database by his player number as a String
type.
' Player number is really an Integer, but String seems to work just
fine

Dim getPlayer As Player
Dim rsPlayer As DAO.Recordset
Const strSQL As String = "Select * from [Players] where
[PlayerNumber] ="
Set rsPlayer = dbs.OpenRecordset(strSQLPlayer & id, dbOpenDynaset)
getPlayer.Fname = rsPlayer!FirstName
getPlayer.Lname = rsPlayer!LastName
getPlayer.Rating = rsPlayer!Ranking
rsPlayer.Close
Set rsPlayer = Nothing

End Function

Function getPlayerGameCount(ByVal id As String)
Dim getPlayerGameCount As Integer
Dim rsPlayerGameCount As DAO.Recordset
Dim qdfPlayerGameCount As DAO.QueryDef

Set qdfPlayerGameCount = dbs.QueryDefs("PlayerGameCount")
qdfPlayerGameCount.Parameters("Player?") = id
Set rsPlayerGameCount = qdfPlayerGameCount.OpenRecordset
getPlayerGameCount = rsGameCount!PlayerGameCount
rsPlayerGameCount.Close
Set rsPlayerGameCount = Nothing
qdfPlayerGameCount.Close
Set qdfGameCount = Nothing

 

[MikeB]

read the help on user defined functions a bit more carefully ...
getPlayer is defined by the Function statement. You left the type
delcaration off, so VBA types it as a variant. then when VBA hits your
Dim getPlayer it chokes. Try:

Function getPlayer(ByVal id As String) As Player

(etc), and

That was it! Thanks Cliff. I had a bunch of other errors after that,
but at least I'm now a whole lot further.

 

[Clif McIrvin]

That was it! Thanks Cliff. I had a bunch of other errors after that,
but at least I'm now a whole lot further.

Great!

I came to VBA from GWBasic after (a few years off .... there is most
assuredly a learning curve!

Good luck on your project.