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,157 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now