Solved

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]"

Posted on 2012-03-12
14
1,198 Views
Last Modified: 2012-03-16
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
Comment
Question by:katerina-p
  • 5
  • 5
  • 4
14 Comments
 
LVL 57
ID: 37709628
<<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
 

Author Comment

by:katerina-p
ID: 37709915
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
 
LVL 75
ID: 37710471
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 37710621
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
 

Author Comment

by:katerina-p
ID: 37710712
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
 
LVL 75
ID: 37710737
Sorry, no clue.  I messed with several things, same result.  For whatever reason, Access just does not like that.  

mx
0
 
LVL 57
ID: 37711287
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
 
LVL 75
ID: 37711376
Well, the way I posted is more or less the intended use of a Public Type ...

mx
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 37712016
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
 

Author Comment

by:katerina-p
ID: 37713940
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
 
LVL 57
ID: 37714029
<<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
 

Author Comment

by:katerina-p
ID: 37714077
Jim,

Would be interested? Is there somewhere documented the performance of various functions and methods and such?
0
 
LVL 57
ID: 37714425
<<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
 

Author Comment

by:katerina-p
ID: 37728862
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question