Custom Form Property, Parameter Query Access 2007 Problem

I've never used properties like this, but I do use functions for a similar purpose.

Because this uses a static variable, it retains its value between calls, so I can set it in one form, and call it from other forms, reports, or queries (and I don't have to declare a parameter).  To set its value, just call it with a value.  As an example in the AfterUpdate of the EventGroup combo box.

Private Sub cboEventGroup_AfterUpdate

    fnEventGroup me.cboEventGroup

End Sub

Then, in a query,

SELECT tEventGrpsAssgn.*
FROM tEventGrpsAssign
WHERE tEventGrpsAssgn.EGRP_ID = fnEventGroup

You could even throw some code in there to set the default value using a DLOOKUP or some other code if you wanted to.  One of the nice things about this, is that I can set it to 0 if I want to select all event groups.  Then, in a query I would use:

SELECT tEventGrpsAssgn.*
FROM tEventGrpsAssign
WHERE fnEventGroup() = 0 OR tEventGrpsAssgn.EGRP_ID = fnEventGroup

Public Function fnEventGroup(Optional EventGroup as Variant = NULL) as Long

    Static myEventGroup as long

    if isnull(EventGroup) = False then myEventGroup = EventGroup
    fnEventGroup = myEventGroup

End Function

Select allOpen in new window

While I understand your suggestion I really want to know why my code works in MS Access 2003 but not MS Access 2007.  You see I have almost 200 databases that contain that code.  There has got to be something to make it work in 2007.

Since the "prpAssignEventGroup" is a property of the form, rather than a field, I would think that the syntax would be more like:

[Forms]![fMgr].prpAssignEventGroup

With your form open, what happens when you type the following in the immediate window:

?[Forms]![fMgr].[prpAssignEventGroup]

or

?[Forms]![fMgr].prpAssignEventGroup

Okay, here's the deal.  [Forms]![fMgr].[prpAssignEventGroup] won't work in the immediate window in either version since it doesn't like the brackets around the property.  The query editor automatically puts the brackets around the property and works fine in 2003 but not 2007.

I have attached a database with the very issue I have been talking about.  If you have both environments then give it a try.
QryParameter.mdb

Have not tried this yet, but you might be able to create a generic function that you pass a value (your public property) and have it returned as a value

Public Function GetIt(ByVal SomeVariable as Variant) as Variant

    GetIt = SomeVariable

End Function

vadimrapp1,

Thanks for the information.  I don't understand what Microsoft is doing MS Access.  They are ruining it more and more with each release.  It's like Microsoft doesn't want anyone to develop it into an application anymore.

>  It's like Microsoft doesn't want anyone to develop it into an application anymore.

I think this is in fact true. If you look at http://www.microsoft.com/office/2010/en/access/default.aspx , you will notice that practically none of the benefits are targeted at the developers. If you think about the target user of these "benefits", it's clearly someone totally unfamiliar with any development, but very familiar with web. So all the effort goes into empowering an illiterate dude to "quickly and easily", better "effortlessly",  create "professional designs" and access it  "from virtual anywhere".

The optimistic look at this idiocy would be in recognizing secret intention to drive everyone professional into Visual Studio; but I'm not that optimist. In fact, in Microsoft's view, Access for decades has been "productivity tool" , i.e. 20 years ago they still were seeing it as the tool for the housewife to store culinary recipes in. That in 20 years the housewife has not materialized, apparently does not matter anything - unlike me, they are obviously optimists.

"[Forms]![fMgr].[prpAssignEventGroup] won't work in the immediate window in either version since it doesn't like the brackets around the property"

But this should (in A2003):

?Forms.fMgr.prpAssignEventGroup

Several things have been 'tightened' in A2007/10 (unfortunately).  For example ... in A2003 you could put this in a Text box Control Source:

=Forms.YourFormName.RecordsetClone.Recordcount
and you would get the Count.

I'm pretty sure this does not work in A2007 >>

mx

DatabaseMX,

Thanks for the extra information.  How do you know about the 'tightening' in A2007/10?  Is there some information on the internet that explains the changes.  I tried to look myself but I couldn't really find anything.  

ascnd

Here's remarkable quote I just found at http://ycmi.med.yale.edu/nadkarni/Access_Course/String_Frame.htm

As of 2006, the Access developer documentation still hasn't recovered from the abominations inflicted on it in 2000, when the Office developer documentation - in particular, the table of contents - was almost deliberately and systematically disorganized to make it next to impossible to learn from. (To be able to refer to any part of it usefully, you already needed to be an expert in VBA.)

"Is there some information on the internet that explains the changes."
Well, I've seen it in various places ... and I also tried the RecordsetClone thing in A2007.

As far as the link above, can't say I really agree with that.  The documentation in A2003 is an improvement over A2000.

RE: ** So all the effort goes into empowering an illiterate dude to "quickly and easily", better "effortlessly",  create "professional designs" and access it  "from virtual anywhere".**
To this day, that has never happened which is why ... there is still PLENTY of work for developers.

mx

> As far as the link above, can't say I really agree with that.  The documentation in A2003 is an improvement over A2000.

actually you are in full agreement with the link: it's _recovering_ :-)

> To this day, that has never happened which is why

Problem is in Microsoft's orientation, who they cater to. The redesign of the documentation was another manifestation of it.