Refer To VBA Object from Forms & SQL?

  • Thread starter Thread starter TIm
  • Start date Start date
T

TIm

Hi,

I am just learning about VBA class modules. For starters
I was trying to replace a set of functions with a custom
class. My class is "Person." In the startup module I do
this:

Public objThisUser As New Person

And then this:

objThisUser.PersonID = fLoggedInUserID()

In the application I refer to properties like so:

objThisUser.FullName

instead of how I used to:

fLoggedInUserFullName()

I would like to retire the person function in favor of the
class. However, I use these functions queries, form
recordsources, form controls etc. Is there any way to
refer to this object in a controlsource or query, etc. Or
do I need to keep my functions for this?

Thanks,
Tim
 
AFAIK, you can't refer to the class in your query. However, this doesn't
mean that you're forced to continue using fLoggedInUserID. You can create a
simple "wrapper function" that returns the value of objThisUser.PersonID

Public Function ThisUser() As String
ThisUser = objThisUser.PersonID
End Function

and then use this function in your queries. In this particular case, it
might not save you much, but if some of your properties require a lot of
processing to compute, you'll probably find it faster.
 
Is there any way to
refer to this object in a controlsource or query, etc. Or
do I need to keep my functions for this?
Click to expand...
I haven't checked this but I think:-

Controlsource: yes. The GUI objects can all see VBA modules and objects.

Queries: only if you run them from the GUI -- that is, clicking on the
thing in the database window, or using DoCmd.OpenQuery. If you pass the
querydef or SQL direct to Jet, using db.Execute or db.OpenRecordset, then
the expression engine never gets to see it to provide the values.

SQL: you can insert the actual values into the SQL as you are creating it:

strSQL = strSQL & "WHERE User = """ & GlobalUser.FullName & """;"

and so on.

Hope that helps

Tim F
 
Thanks. I cannot get it to work in a controlsource. In
the controlsource of "FullName" I put a reference to a
global VBA object like so:

=objThisUser.FullName

This gives "#Name" error. Then I tried the following code
in the current event, which works:

Me.FullName = objThisUser.FullName

I would rather not have a bunch of initialization code for
the form. What am I doing wrong? Thanks.

Tim
 
This gives "#Name" error.
Click to expand...

I think Douglas has the right answer. The Access GUI can see global
Functions but not variables.
I would rather not have a bunch of initialization code for
the form.
Click to expand...

Although there is nothing wrong with that -- what is it that worries you?
One thing that strikes me is that this will not reflect changes in the
object value.

B Wishes


Tim F
 
Well, exactly. If I set the value with code instead of
the controlsource or defaultvalue properties, I must know
and handle all the events that might change it. I think
wrapper functions are the answer.

However, what's not clear to me is, what's the benefit of
using custom classes if I must create a matching function
for each property. Seems like duplication. Maybe as I
explore custom classes I will see the light!

Thanks!

Tim
 
However, what's not clear to me is, what's the benefit of
using custom classes if I must create a matching function
for each property. Seems like duplication. Maybe as I
explore custom classes I will see the light!
Click to expand...

Classes are a VBA opportunity -- data abstraction, reusable code, better
and cheaper debugging and maintenance, self documenting etc etc.

The fact that Access (sic) even sees some VBA structures is an unexpected
blessing -- there is no other serious db system that offers such ability.
What you get in real DBMSs is SQL triggers, which are much harder to write
and probably there are things that they simply cannot do (but I am no SQL
expert!). Given that, MS is allowed to put limits on how much magic it
exposes, and the design team decided to make Void Functions visible to the
expression engine and not variables. (I was being thick in my first
response: I did know this really...)

So, class modules are still enormously helpful. If you ever program in VB,
Excel, Word, CorelDraw, asp, vbscript, etc. etc. then you can simply drop
in your class without thinking again (almost).

Remember you have to put a function wrapper round any Public Sub you write
too, before seeing it from Access.

Hope that helps


Tim F
 
Back
Top