function in a query

[mohobrien]

I want to use a user defined function in my query. This is the function.

Public Function GetOwners(Dispos As String) As String
 'MAKE THE OWNER string
 Dim qdfGetOwners As QueryDef
 Dim rstGetOwners As Recordset
 Dim strSQL As String
 Dim strOWNERS As String
 Dim db As DAO.Database
  Set db = CurrentDb()
  Set qdfGetOwners = db.CreateQueryDef("")

 strSQL = "SELECT tblHolders.DispositionNumber, tblHolders.Holder, tblHolders.Percentage " & _
"FROM tblHolders " & _
"WHERE (((tblHolders.DispositionNumber)= """ & Dispos & """ ))" & _
"ORDER BY tblHolders.DispositionNumber ;"
    With qdfGetOwners
            .SQL = strSQL
            Set rstGetOwners = .OpenRecordset() ' error trap needed for no records?
    End With
    With rstGetOwners
            Do While Not .EOF
                If .OpenRecordset.Fields(0).Value = Dispos Then
                strOWNERS = strOWNERS & .Fields(1).Value & " " & .Fields(2).Value & "  "
                End If
            .MoveNext
            Loop
            strOWNERS = RTrim(strOWNERS)
    End With
    rstGetOwners.Close
    GetOwners = strOWNERS
End Function

The function works if placed inside a form module with the resultant string written to an unbound text box. If the function is placed in its own module so that the query has access to it as well, it doesn't. So when I try to use the function in the query, it can't find it.
This is my query:
SELECT ALLDISP.[Disposition Number], ALLDISP.[Current Status], ALLDISP.[NTS Map Reference], GetOwners(ALLDISP.[Disposition Number]) AS OWNERS, ALLDISP.Location, ALLDISP.[Staking Date], ALLDISP.[Effective Date], ALLDISP.[Date Protected to], ALLDISP.[Area (Hectares)], ALLDISP.[Grouping Certificate]
FROM ALLDISP
ORDER BY ALLDISP.[Disposition Number];

Any Ideas?

 

[dcx693]

So when I try to use the function in the query, it can't find it

What exactly do you mean? What error message does it give you?

 

[mohobrien]

Undefined function 'GetOwners' in expression.

 

[dcx693]

The module that contains the GetOwners function, does it compile correctly? Does the rest of your code compile correctly? Meaning in the VB editor, if you go to the Debug menu and choose Compile, does it give you an error message?

 

[mohobrien]

That's something I'll have to read up on. Is that just sort of testing how it would run?
Anyroad, I tried it and no errors.

 

[dcx693]

Sometimes a function or sub won't work because there's another function or sub in the database that doesn't compile correctly. It doesn't sound like the case here, though.

 

[mohobrien]

Thanks for the effort. The main problem is that the function just isn't recognized by access when it is in a code module. It is recognized when in a form module and it does do its thing. It is declared as public so I can't see what's wrong.

 

[dcx693]

Have you tried calling the function from the Immediate window to see if it's still working?

Press Control+G to get the Immediate window, then type something like:
? GetOwners(some value)

Just replace "some value" with a valid number in your data and see what it does.

 

[mohobrien]

Compile Error
Expected variable or procedure not module.

I had called the module by the same name as the function, so I renamed the module. At least the error message is different.

 

[mohobrien]

Solved!!

Thank you ^ 10.

It was because I named the module the same name as function!!
When I changed the name of the module, closed the db and started up again, all worked. That last clue you gave was what twigged it. Thank you again!!!

 

[dcx693]

You're welcome. I have heard of this problem before. Sorry I didn't remember, but glad it worked out.