T
thebrat26
I am using Access 2007 and I have Membership Database in which I have created
a query which asks for input from the user – Financial Year and Membership
Type [qrySubscription].
This data is also in Tables, [FinYears] – just list years and [MembSubs] –
EffectiveYear, MemberType, MembAmount respectively, and I wanted to be able
to create a menu where the user could select the options from combo boxes.
I thought I’d found a solution on Martin Green’s wonderful Access Tips site
under “Customizing Access Parameter Queriesâ€
http://www.fontstuff.com/access/acctut08.htm and followed the instructions.
Now the Query, when run on its own asking the user for direct input, works –
I get the financial year, the correct Membership and the Value of the
corresponding membership.
I edited it to remove the prompts, set up the menu as described and ran the
Query via the menu. The drop down menus work fine but:
1. The menu box, though the correct size in Design View, in Form and Layout
View is screen-sized
2. The OK runs the query but the menu does not then disappear
3. The Cancel button does nothing
The other thing: I have been trying to figure out how to get the information
from the query to update my subscriptions form - Financial Year and
Membership Type and also to automatically fill the Membership (Value) Amount.
I tried another tack after doing some research and tried my hand at using
the Macro builder. There is a Yes/No selection that I created an event
which, On Click, would run the macro – would be nice if it ran the macro only
when clicked ON and not also when clicked OFF! Tried: in the Conditions
column but that didn’t work ïŒ! [Forms]![CopySubscriptions]![PayReceived]=True
I had some limited success with the macro builder – for a newbie! [don’t
laugh] - the query runs and the cursor is sitting in the query results on
the EffectiveYear field and I get an error message saying it can’t find
DataParQuery EffectiveYear! Huh?
I had made copies of my tables and query to experiment with and the macro
reads as follows:
Action Arguments Comments
OpenQuery DataParQuery, Datasheet, Read Only Open DataParQuery, Datasheet,
Read Only - Run Query
SetTempVar tmpFinYear, [DataParQuery]![EffectiveYear] Create a temporary
variable [tmpFinYear] and fill it with the result from Query
-[DataParQuery]![EffectiveYear]
SetTempVar tmpMembType, [DataParQuery]![Type] Create a temporary variable
[tmpMembType] and fill it with the result from Query -[DataParQuery]![Type]
SetTempVar tmpMembAmount, [DataParQuery]![MembAmount] Create a temporary
variable [tmpMembAmount] and fill it with the result from Query
-[DataParQuery]![MembAmount]
GoToControl SubsDueAmount SubsDueAmount
SetProperty SubDueAmount, Enabled, tmpMembAmount transfer amount in temp
variable Amount into SubDueAmount
RemoveAllTempVars
StopAllMacros
Hope you can understand the above as copy and paste didn't include table
layout!
Hope this doesn’t all sound too confusing, after 3 days I know I’m pretty
confused.
Any help greatly appreciated.
Thebrat26
PS why does 'Notify me of replies' not work when ticked on this site?
a query which asks for input from the user – Financial Year and Membership
Type [qrySubscription].
This data is also in Tables, [FinYears] – just list years and [MembSubs] –
EffectiveYear, MemberType, MembAmount respectively, and I wanted to be able
to create a menu where the user could select the options from combo boxes.
I thought I’d found a solution on Martin Green’s wonderful Access Tips site
under “Customizing Access Parameter Queriesâ€
http://www.fontstuff.com/access/acctut08.htm and followed the instructions.
Now the Query, when run on its own asking the user for direct input, works –
I get the financial year, the correct Membership and the Value of the
corresponding membership.
I edited it to remove the prompts, set up the menu as described and ran the
Query via the menu. The drop down menus work fine but:
1. The menu box, though the correct size in Design View, in Form and Layout
View is screen-sized
2. The OK runs the query but the menu does not then disappear
3. The Cancel button does nothing
The other thing: I have been trying to figure out how to get the information
from the query to update my subscriptions form - Financial Year and
Membership Type and also to automatically fill the Membership (Value) Amount.
I tried another tack after doing some research and tried my hand at using
the Macro builder. There is a Yes/No selection that I created an event
which, On Click, would run the macro – would be nice if it ran the macro only
when clicked ON and not also when clicked OFF! Tried: in the Conditions
column but that didn’t work ïŒ! [Forms]![CopySubscriptions]![PayReceived]=True
I had some limited success with the macro builder – for a newbie! [don’t
laugh] - the query runs and the cursor is sitting in the query results on
the EffectiveYear field and I get an error message saying it can’t find
DataParQuery EffectiveYear! Huh?
I had made copies of my tables and query to experiment with and the macro
reads as follows:
Action Arguments Comments
OpenQuery DataParQuery, Datasheet, Read Only Open DataParQuery, Datasheet,
Read Only - Run Query
SetTempVar tmpFinYear, [DataParQuery]![EffectiveYear] Create a temporary
variable [tmpFinYear] and fill it with the result from Query
-[DataParQuery]![EffectiveYear]
SetTempVar tmpMembType, [DataParQuery]![Type] Create a temporary variable
[tmpMembType] and fill it with the result from Query -[DataParQuery]![Type]
SetTempVar tmpMembAmount, [DataParQuery]![MembAmount] Create a temporary
variable [tmpMembAmount] and fill it with the result from Query
-[DataParQuery]![MembAmount]
GoToControl SubsDueAmount SubsDueAmount
SetProperty SubDueAmount, Enabled, tmpMembAmount transfer amount in temp
variable Amount into SubDueAmount
RemoveAllTempVars
StopAllMacros
Hope you can understand the above as copy and paste didn't include table
layout!
Hope this doesn’t all sound too confusing, after 3 days I know I’m pretty
confused.
Any help greatly appreciated.
Thebrat26
PS why does 'Notify me of replies' not work when ticked on this site?