I know you got an answer for this on experts exch, but I thought I'd post an answer for you here in case anyone else is having this issue.
I had the same exact issue as you did. I have code in prior versions of MS-Access that worked fine. OOP 101, a form is an object, I have properties for that object that I have defined in code. Prior to 2007, queries can reference everything properly, as an object, so you can reference your properties from within queries just like any other form property. Not so in 2007.
As was detailed by others, you have to wrap any custom properties for a form in a function in order to be referenced by a query. It's more pervasive than that apparently.
You can't reference any custom properties of a form from a query.
You can't reference a forms public functions from a query.
You can't reference most anything "VBA" other then global functions from a query. (i.e. Global Variables, etc.)
I'm sure this is an old issue for many, but I just hit this recently, as did you. I played around a bit with this, and was kind of amazed at what can't be used within queries now. I'm not one of the nay sayer's claiming that Microsoft is out to kill MS-Access, but I AM sort of amazed that this apparently is also in MS-Access 2010.
I downloaded your sample DB, and this is what a simple wrapper function would look like:
'Put the following into a module. It will NOT work from within the form itself.
'Well, it will from VBA, just not for the query, even if you reference it properly as Forms!fMgr.getPrpDeptID()
Function getPrpDeptID() As Long
On Error Resume Next
getPrpDeptID = 0
getPrpDeptID = Forms!fMgr.prpDeptID
End Function
Your Query then becomes:
SELECT tDeptEmp.*
FROM tDeptEmp
WHERE ((tDeptEmp.DEPT_ID)=getprpdeptid());
I'm sure you've long resolved this, but this is for reference for others.
ascnd wrote:
Custom Form Property, Parameter Query Access 2007 Problem
23-Feb-10
In most of my Access Forms I create a custom form property like the following
Form called "fMgr
Public Property Let prpAssignEventGroup(ByVal v As Long
iAssignEventGroup =
End Propert
Public Property Get prpAssignEventGroup() As Lon
prpAssignEventGroup = iAssignEventGrou
End Propert
Then in my query I create a parameter like the following
[Forms]![fMgr].[prpAssignEventGroup
here is the SQL
PARAMETERS [Forms]![fMgr].[prpAssignEventGroup] Long
SELECT tEventGrpsAssgn.
FROM tEventGrpsAssg
WHERE tEventGrpsAssgn.EGRP_ID=[Forms]![fMgr].[prpAssignEventGroup]
In the form I then assign the custom property when a selection is made in
drop down box then I fire off the query. The problem is in Access 200
everything works seamlessly, but in Access 2007 it is like it cannot find th
custom property and the query prompts the user for that parameter
Please help.
Previous Posts In This Thread:
Custom Form Property, Parameter Query Access 2007 Problem
In most of my Access Forms I create a custom form property like the following
Form called "fMgr
Public Property Let prpAssignEventGroup(ByVal v As Long
iAssignEventGroup =
End Propert
Public Property Get prpAssignEventGroup() As Lon
prpAssignEventGroup = iAssignEventGrou
End Propert
Then in my query I create a parameter like the following
[Forms]![fMgr].[prpAssignEventGroup
here is the SQL
PARAMETERS [Forms]![fMgr].[prpAssignEventGroup] Long
SELECT tEventGrpsAssgn.
FROM tEventGrpsAssg
WHERE tEventGrpsAssgn.EGRP_ID=[Forms]![fMgr].[prpAssignEventGroup]
In the form I then assign the custom property when a selection is made in
drop down box then I fire off the query. The problem is in Access 200
everything works seamlessly, but in Access 2007 it is like it cannot find th
custom property and the query prompts the user for that parameter
Please help.
Submitted via EggHeadCafe - Software Developer Portal of Choice
How to display a Gravatar Image with 100 Percent Client Script Code
http://www.eggheadcafe.com/tutorial...c-b0877c10ecb4/how-to-display-a-gravatar.aspx