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?
Jeffrey CoachmanConnect With a Mentor MIS 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

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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.
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.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.