User defined types/public function in a query

[AlanM]

Hi All

I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.

Using the function in forms/VBA works fine.

But my DB crashes whenever I try to use it in a query.

My modules code:
= = =
Option Compare Database

Public Type EvCheks
EvType As String
EvAttCat As String
EvUnitAss As String
EvCheksAll As String
End Type
= =
Public Function getEvCheks(EV, EvUnit) as EvCheks

'Event Type: Event or DL
If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then
getEvCheks.EvType = "Y"
Else: getEvCheks.EvType = "N"
End If

'Event Attendance Category: INDB= in database or LIST
If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then
getEvCheks.EvAttCat = "Y"
Else: getEvCheks.EvAttCat = "N"
End If

'Event Assessing Organisation
Dim AOROName As String
Dim AOName As String
AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit)
AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)

Select Case AOName
Case "NA"
getEvCheks.EvUnitAss = "N"
Case "ABCD"
getEvCheks.EvUnitAss = "Y"
Case Else
getEvCheks.EvUnitAss = "X"
End Select

getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss
End Function
====
On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box
MsgBox getEvCheks(EV, EVU).EvCheksAll

This works fine.
=====
In a query I have this SQL code:
SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails
FROM tblEvUnits;

This causes the db to crash.

I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".

I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.

Any/all assistance much appreciated.

Regards
AlanM

 

[RuralGuy]

I doubt you can use a function in a query that returns more than one value.

 

[AlanM]

Thanks RuralGuy - good point.

Bit "heavy-handed" of Access to just crash! No error messages, no warnings.

Anyway, I've used another way of doing what I want but when I have time I'll change the module code to return just one value, and see what happens in the query.

Thanks
AlanM

 

[RuralGuy]

There is always a problem in slowing things down enough during runtime to be able to catch every error.