katerina-p
asked on
MS Access VBA error: "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 [UDT]"
Hi All,
I've had a search and I realise there are some posts on this but I'm afraid I can't work out what's wrong.
I'm getting this error:- 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
When compiling this (all in module):-
I'm afraid I don't understand the error message or how to resolve. For info, what I'm trying to achieve is pass a record reference to a function, and return an array (size unknown before running function) of the UDT (currency, string and integer values). I presume I could achieve this with:-
But that's just using variants and I'm worried about memory and time (500k records to process).
Is there a way to achieve this using my UDT / why am I getting the error?
Many thanks
I've had a search and I realise there are some posts on this but I'm afraid I can't work out what's wrong.
I'm getting this error:- 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
When compiling this (all in module):-
Public Type ShipSet
cPrice As Currency
sService As String
End Type
Public Function fSomething() As Variant
Dim Shipping(1 To 2) As ShipSet
Shipping(1).cPrice = 5
Shipping(2).cPrice = 10
Shipping(1).sService = "Ser1"
Shipping(2).sService = "Ser2"
fSomething = Shipping()
End FunctionI'm afraid I don't understand the error message or how to resolve. For info, what I'm trying to achieve is pass a record reference to a function, and return an array (size unknown before running function) of the UDT (currency, string and integer values). I presume I could achieve this with:-
Public Function fSomething2() As Variant
Dim shipping(1 To 2, 1 To 2) As Variant
shipping(1, 1) = 5
shipping(2, 1) = 10
shipping(1, 2) = "Ser1"
shipping(2, 2) = "Ser2"
fSomething2 = shipping
End FunctionBut that's just using variants and I'm worried about memory and time (500k records to process).
Is there a way to achieve this using my UDT / why am I getting the error?
Many thanks
ASKER
Hi Jim,
Thanks for your response. It's in a 'normal' module, not a class module or form.
No problem:-
The function is passed the weight, value, and service level of a product and returns the array of different postage services available - price, destination, etc.
so fShipping(lGrams, cValue, sAvailability) would return an 2-dimension UDT array
(FirstClass, £1, UK, ...)
(SecondClass, £0.5, UK, ...)
(AirMail, £2, USA, ...)
etc, with the result being based on the parameters given. And then this UDT array is used when building an XML doc. I wanted to pull back all the different options at once - will using an variant array cause performance degradation? The function will be called for each record in the rst - sometimes up to 500,000 at a time.
Thanks for your response. It's in a 'normal' module, not a class module or form.
No problem:-
The function is passed the weight, value, and service level of a product and returns the array of different postage services available - price, destination, etc.
so fShipping(lGrams, cValue, sAvailability) would return an 2-dimension UDT array
(FirstClass, £1, UK, ...)
(SecondClass, £0.5, UK, ...)
(AirMail, £2, USA, ...)
etc, with the result being based on the parameters given. And then this UDT array is used when building an XML doc. I wanted to pull back all the different options at once - will using an variant array cause performance degradation? The function will be called for each record in the rst - sometimes up to 500,000 at a time.
Just when you think you've seen all the errors. I get a slightly different error ... just trying to compile that code:
mx
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Yep, seems plausible.
Any ideas about/can you explain the original error? As far as I can parse the error msg (i.e., the same way that Jim does above) I've done everyhting correctly!
Seems to me that if you do this in the Declarations section of the module:
...
Public Shipping(1 To 2) As ShipSet...
Yep, seems plausible.
Any ideas about/can you explain the original error? As far as I can parse the error msg (i.e., the same way that Jim does above) I've done everyhting correctly!
Sorry, no clue. I messed with several things, same result. For whatever reason, Access just does not like that.
mx
mx
I don't remember having ever tried something like this myself. I'll have to play around with it.
I'll also try and think of a better way to end up doing what you want.
Jim.
I'll also try and think of a better way to end up doing what you want.
Jim.
Well, the way I posted is more or less the intended use of a Public Type ...
mx
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Jim.
Just FYI:
1000 records took 210 seconds using Variant Array
1000 records took 204 seconds using UDT Array
1000 records took 64 seconds using Dlookup (i.e., reading stored values)
1000 records took 344 seconds using the previous, non-array, function as a QRY field.
Not quite as fast as I'd hoped, but still a 1/3 faster.
Thanks for your and MX's help.
K.
Just FYI:
1000 records took 210 seconds using Variant Array
1000 records took 204 seconds using UDT Array
1000 records took 64 seconds using Dlookup (i.e., reading stored values)
1000 records took 344 seconds using the previous, non-array, function as a QRY field.
Not quite as fast as I'd hoped, but still a 1/3 faster.
Thanks for your and MX's help.
K.
<<1000 records took 64 seconds using Dlookup (i.e., reading stored values)>>
<<Not quite as fast as I'd hoped, but still a 1/3 faster.>>
I'm sure we could speed that up.
Jim.
<<Not quite as fast as I'd hoped, but still a 1/3 faster.>>
I'm sure we could speed that up.
Jim.
ASKER
Jim,
Would be interested? Is there somewhere documented the performance of various functions and methods and such?
Would be interested? Is there somewhere documented the performance of various functions and methods and such?
<<Would be interested? Is there somewhere documented the performance of various functions and methods and such?>>
No documentation really.
While Dlookup() is not inherently slow, there are faster methods of accessing table information depending on where/how your doing this.
The thing to keep in mind with all the Domain functions such as Dlookup() is that they are intended to be used where expressions are allowed and SQL statements not (if you break them down, they all represent some form of an SQL Statement).
If you can use a SQL statement, then you’re better off simply to do so, such as in a query. If your working in a query, you can simply join to another table rather then doing a sub-select (which is what a Dlookup() would be). But even if you did a sub-select, it would still be faster then using Dlookup() in the query because the query parser can see what you want and act accordingly. Dlookup() in query is unoptimizable.
Beyond that, if you’re working in code then you can use DAO, which offers the fastest methods of accessing JET data. With the available methods, the fastest is using .Seek against a index against a table. This is almost instantaneous as it is a direct lookup against a B-Tree ISAM index.
If you cannot do that, then you can either:
a. Execute a SQL statement returning just the data you need.
b. Open a recordset and use a find method, which depending on indexes available can be pretty fast.
In your case, I would try and find a way of using .seek, even if it meant adding an index.
Ask another question, showing your lookup code as it stands now and that you'd like to use .seek instead. Posting a sample MDB would be very helpfully. Doesn't have to be the full db either.
No matter where you end up, I'm sure you can do better then what you got with Dlookup().
Jim.
No documentation really.
While Dlookup() is not inherently slow, there are faster methods of accessing table information depending on where/how your doing this.
The thing to keep in mind with all the Domain functions such as Dlookup() is that they are intended to be used where expressions are allowed and SQL statements not (if you break them down, they all represent some form of an SQL Statement).
If you can use a SQL statement, then you’re better off simply to do so, such as in a query. If your working in a query, you can simply join to another table rather then doing a sub-select (which is what a Dlookup() would be). But even if you did a sub-select, it would still be faster then using Dlookup() in the query because the query parser can see what you want and act accordingly. Dlookup() in query is unoptimizable.
Beyond that, if you’re working in code then you can use DAO, which offers the fastest methods of accessing JET data. With the available methods, the fastest is using .Seek against a index against a table. This is almost instantaneous as it is a direct lookup against a B-Tree ISAM index.
If you cannot do that, then you can either:
a. Execute a SQL statement returning just the data you need.
b. Open a recordset and use a find method, which depending on indexes available can be pretty fast.
In your case, I would try and find a way of using .seek, even if it meant adding an index.
Ask another question, showing your lookup code as it stands now and that you'd like to use .seek instead. Posting a sample MDB would be very helpfully. Doesn't have to be the full db either.
No matter where you end up, I'm sure you can do better then what you got with Dlookup().
Jim.
ASKER
Jim,
With apologies for the delay (systems crash to deal with!), thank you so uch for your advice. I've already implemented some of it and along with other changes, am running at 1000 records taking <20 seconds now. Once I've tidied it up I will post another question as you suggested.
Many thanks!
Katerina.
With apologies for the delay (systems crash to deal with!), thank you so uch for your advice. I've already implemented some of it and along with other changes, am running at 1000 records taking <20 seconds now. Once I've tidied it up I will post another question as you suggested.
Many thanks!
Katerina.
Well take that error message a piece at a time:
"Only public user defined types"
You've got your UDT declared public, so that's OK.
"defined in public object modules"
You said this is all in a module; what kind of module? it cannot be a class or form module (which is a actually a class module).
"can be used as parameters or return types for public procedures of class modules or as fields of public [UDT]"
OK.
<<But that's just using variants and I'm worried about memory and time (500k records to process).>>
Maybe if you outline what it is your trying to do, someone can suggest a better approach.
Jim.