Open form from clicking field in query (when query is in a subform)

I have a subform on my form which displays a query as the source object. I did this to accomodate the dynamic nature of the fields within the query (it's a crosstab query which has different numbers of columns based upon the results the user is after).

I want to have the user double click a field on the subform query (an order number) and have another form open (trigger an event).
Is this possible?

Thanks in advance.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A subform based on a query does not really expose an Event model, so you cannot really fire off code based on user action in the subform.
Jeffrey CoachmanMIS LiasonCommented:
When the crosstab is generated (or filtered, or whatever...), also generate a unique list of Order Numbers.

Fill a listbox with these values and you can double click them from there...

Since you did not post and details on the form or it's design or purpose, ...or state your skill level with VBA and/or SQL, is difficult to give specifics on a solution...
(refreshing the listbox,...etc)

For ex., this will give you a crosstab of Employees, Shippers and the sum of the Freight, Where the shipper is 2:

TRANSFORM Sum(tblOrders.Freight) AS SumOfFreight
SELECT tblOrders.EmployeeID
FROM tblOrders
WHERE (((tblOrders.ShipperID)=2))
GROUP BY tblOrders.EmployeeID
PIVOT tblOrders.ShipperID;

Then use this same SQL (because I did not know how to remove the sums column) for the rowsource for a listbox. (set the Column count to 2, and set the column widths to 1,0)
Then set code like this on the doubleclick event of the listbox to open another form filtered for the selected Employee(ID)
    DoCmd.OpenForm "frmEmployees", , , "EmployeeID=" & Me.YourListBox


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimothyHughesAuthor Commented:
Thanks Jeff for the feedback. I am technically proficient with VBA coding and SQL, so any level of specifics of a solution will be helpful.

The top part of the form is a query builder which allows the user to refine the financial information provided in the subform. I have a crosstab query which shows the financial information by month (which can be decreased by the query filters) with fields at the beginning stating customer/salesrep/etc.  

I want the user to be able to click on a field and have a new form initiate which shows details for that row (a list of invoices, etc.). As LSMConsulting pointed out, I do not have an event model exposed with the query subform.

I was thinking of 3 possible solutions:
1) Dynamically create a data sheet form and find a way to dynamically add the event into it. I understand that this can cause problems with compiling and exposes you to possible corruption (from what i have read).
2) Use a 'Hyperlink' type of solution which points to a form. I do not know if something exists like this though.
3) Have the user highlight the order number and then press a function key. I would then write some code to post the order number to the clipboard, then open a form with the information I have in the clipboard.

Your idea of a listbox is a great one. Thanks!
I am researching it right now to see if it can work for what I am after. Let me see if I can get that many columns to view nicely as a list.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
<so any level of specifics of a solution will be helpful. >
As I stated, you have not provided any details on the design or purpose of the form.
We are also lacking the details of the crosstab subform (Linking fields, ...etc) or how (or if) it filters.
So a more "specific solution may be hard to come by here.

But my post outlines the basics of one possible solution.

Perhaps if you posted a sample of your database, this would all be clearer.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


TimothyHughesAuthor Commented:
I had thought I was asking a generic question which I now believe has no solution.

JeffCoachman did not like my question which I thought was detailed enough and succinct.

I have since completed a work-around and would like to thank the experts for their efforts.
Jeffrey CoachmanMIS LiasonCommented:

I never said I did not like your question...

I only said that I could only post a general solution, because I knew so little about your existing design.

If you post a sample, and provide the needed explanations, ...I would be more than willing to assist you further.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What was the workaround?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.