Update a form field by double clicking a query field

[Xarkath]

Hello,

I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.

Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.



Also, I am pretty new to Access so feel free to let me know if I going about this the wrong way.

Summary:
My form contains fields for group number and Plan.
The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query).
Would like to double click a plan within the plan query and have that description populated into the form's plan field.

Thanks

[June7]

What do you mean by 'the first query displays' and 'runs the second query'? Are you opening query objects? Can't run code from clicking on a query.

[Xarkath]

What do you mean by 'the first query displays' and 'runs the second query'? Are you opening query objects? Can't run code from clicking on a query.

I drag and dropped the queries into the form so that the results could be seen without opening anything additional. Once the first query as updated I then select what I want from it and populate the plan field on the form which is then used as the criteria for the second query. I have been able to setup on event commands to refresh the queries so now I am just looking for a better way to populate the plan field.


Are those queries then concidered "objects"? Sorry, I am that new with Access.



Thanks

[June7]

It is my understanding that subform/subreport container control can use table or query as SourceObject but then limited on available events because there are no data controls (textbox, combobox, listbox) so events associated with them are not available, i.e., a field of a table or query cannot have code but a textbox can.

Why would data from subform need to be copied to main form? Sounds like duplication of data.

[Xarkath]

Why would data from subform need to be copied to main form? Sounds like duplication of data.

Because the results of the first query give me list like such:

Plan 1
Plan 2
Plan 3

there could be upwards of 20 or 30 plans for any given group but ultimately I only want results based on a single plan.

I then need to select the appropriate plan (only one will be appropriate). Once I choose the plan I then get a list of elections that correspond to the plan as such:

Med/Den/Vis/Rx
Med/Vis/Rx

Or a different plan might have:

Med/Den/Vis/Rx/FSA
Med/Vis/Rx/FSA

The idea being that some of our clients can have 100s of options (I think about 300 has been the top end so far. It is a little more complicated than just benefit elections) and it would be nice to only see the options that are appropriate to the chosen plan. So, plans are listed based on the group and elections are based on the plan. The main form controls the the queries and to get the the second query to refresh I have to know what the options are for the plan. I am not being clear....

The Main form field "group" controls the plan query delivering the data needed to populate the plan field.
The Main form field "plan" controls the query delivering the election options.

I think I just need a new approach but thanks for the response. It at least tells me that this setup is not going to work so I can stop wasting time on it and move to another approach.

Thanks

[June7]

Sounds like you really need dependent (cascading) comboboxes on the main form. http://datapigtechnologies.com/flash...combobox2.html

[Xarkath]

Thanks for the help. I just replaced the first query with a list box and set the on event macro and it is now working just as I wanted it to.