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):-

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 Function

Open in new window


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:-

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 Function

Open in new window


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
katerina-pAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<When compiling this (all in module):->>

  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.
0
katerina-pAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Just when you think you've seen all the errors.  I get a slightly different error ... just trying to compile that code:

1
mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Seems to me that if you do this in the Declarations section of the module:

Public Type ShipSet
  cPrice As Currency
  sService As String
End Type

Public Shipping(1 To 2) As ShipSet

Then your Function only needs to set the values - don't try to return anything per se:

Public Function fSomething()

' 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 Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
katerina-pAuthor Commented:
Hi,

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!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry, no clue.  I messed with several things, same result.  For whatever reason, Access just does not like that.  

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, the way I posted is more or less the intended use of a Public Type ...

mx
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, I played around with this for a while and defining the function like this:

Public Function fSomething() As ShipSet

Gets me all the way through the function, but I can't get anything to accept the passed back value.

Something in VBA simply doesn't handle the type conversion between a variant and a UDT like it should (and according to the docs, this should work).

Closest to what you were trying to do is this:

Public Function fSomething(strMethod As String) As Variant

Dim Shipping(10, 2) As Variant

Shipping(1, 1) = 5
Shipping(1, 2) = "test"
Shipping(2, 1) = 6
Shipping(2, 2) = "test2"

fSomething = Shipping()

End Function

Public Sub TestThis()

Dim ShipData As Variant

ShipData = fSomething2("UPS")

Debug.Print ShipData(1, 1)
Debug.Print ShipData(1, 2)
Debug.Print ShipData(2, 1)
Debug.Print ShipData(2, 2)

End Sub

  Which is just forgetting about the UDT.  You could just easily go the route MX posted, which is what I do as well, but some look at passing a global variable around from procedure to procedure like that as sloppy programming (In my book it does get the job done, and if it works it works).

 But outside of working with the UDT, I think what's above does come a bit closer to the logic you had in mind.

Jim.
0
katerina-pAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
katerina-pAuthor Commented:
Jim,

Would be interested? Is there somewhere documented the performance of various functions and methods and such?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
katerina-pAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.