Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1597
  • Last Modified:

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
0
katerina-p
Asked:
katerina-p
  • 5
  • 5
  • 4
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now