Query with VBA Public function as parameter

[randognb]

Hi,

I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.

Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String

Public Function returnName() As String
If IsNull(gbl_loginName) Then
returnName = "test" ' dummy account created for development only
Else
returnName = gbl_loginName
End If
End Function

and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name], [Entry of Hours].[Set Up Time], [Entry of Hours].[Run time], [Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date],
[Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS, [Entry of Hours].DMR, [Entry of Hours].[Employee Number], Traveler_to_Drawing.Drawing_Number,
Traveler_to_Drawing.Customer, Drawings.Description, Traveler_to_Drawing.Due_Date, [Entry of Hours].[Set Up Time] + [Entry of Hours].[Run time] AS Total, LEFT([Entry of Hours].[Traveler Number],
6) AS WO
FROM [Entry of Hours] INNER JOIN
Traveler_to_Drawing ON [Entry of Hours].[Traveler Number] = Traveler_to_Drawing.Traveler_Number INNER JOIN
Drawings ON Traveler_to_Drawing.Drawing_Number = Drawings.Drawing_Number
WHERE ([Entry of Hours].[Employee Name] = returnName())

when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name

Can I get some advice on what i'm doing wrong? Is there a problem with using public functions in Access' design view?

Thanks!

 

[pbaldy]

Is the function in a standard module, rather than behind a form/report? Presuming so, is the module named the same thing (it can't be)?

 

[randognb]

The function is in a standard module. It's named "StartUp"

 

[pbaldy]

I just tested with a simplified version of that and it worked fine with:

WHERE tblBookings.OutDriver=returnName()

 

[randognb]

hmm very strange...

and you were able to add that in as criteria in access' design view mode? I've seen lots of people write and execute the SQL in VBA code... this query is in a subform... is there a way I can write a vba code that adds in the global variable to the query definition and then refresh the query?

 

[randognb]

by the way, i appreciate your time!

 

[pbaldy]

I built it in design view. Can you post your db here? Oh, has code been enabled or is the db in a trusted location?

 

[randognb]

I'm working on an ADP pulling the database tables from our SQL server. Can I still just post the ADP?

Code should be enabled since i've programmed all sorts of other stuff.

 

[pbaldy]

Don't think posting an ADP would help, but it's probably your problem. I haven't used an ADP (the advantages didn't seem worth the disadvantages), but I assume the SQL is passed back to SQL Server to process, and SS doesn't know anything about the function. It would work against linked tables, since the processing would start on the Access side.

I haven't used it but Armen's SQL tools are very good, and would let you dynamically change the criteria:

http://www.jstreettech.com/downloads.aspx

I think the ADP will also let you include a form reference, though don't quote me on that. If so, it would give you another way of solving the problem.

 

[randognb]

Thanks for the reference and the help!

I checked out the tools and while they are very useful indeed, I'm not entirely clear how they would let me dynamically change the criteria. They mostly allow editing of an SQL string so would I have to use a querydef callout to pass the edited / updated SQL string back into a query?

 

[pbaldy]

Yes, so instead of the query using the function directly you'd modify it before it ran so the actual query was:

WHERE [Entry of Hours].[Employee Name] = 'Paul'

That way SQL Server isnt' trying to figure out what the function is. I do have this feeling that you can use a form reference in an ADP, so you might play with that too. I looked briefly at ADP's when they came out, but didn't see how they would be an advantage over linked tables. They've since been deprecated, so not many people use them.