Open another form from a clicked value in a subform in Access 2010

Posted on 2016-08-21
Medium Priority
Last Modified: 2016-08-22
I have a form (CustomerInfo) with a subform (WorkOrders) linked by CustomerID. I want to be able to click on an order on the subform to open the associated work order form. In the subform click event, I passed the value to a textbox in the main form (CustomerInfo). From there I built a macro to open the associated work order form but somehow It didn't work. It just opened a blank form. Any help is appreciated.

This is my open form macro (assigned to a command button on the main form
Form name: WorkOrder
View: Form
Where condition = [Forms]![WorkOrder]![WorkorderID]=[Text116]

Question by:thao-nhi
  • 2
  • 2
LVL 50

Expert Comment

by:Dale Fye
ID: 41764652
rather than passing the value to the main form, use the subforms DoubleClick event to open the other form with code like:
Private Sub Form_DblClick(Cancel as Integer)

    docmd.openform "WorkOrder",  , , "[WorkOrderID] = " & me.txt116, acFormEdit

End Sub

Open in new window

If you want the user to have to close that popup form before doing anything else in the main or subform, then set the WindowMode argument of the OpenForm method to acDialog.

If this does not work, check your WorkOrder form and make sure the "Data Entry" property of the form is not set to Yes.  If it is, that means that the form will only open in data entry (blank) mode.
LVL 31

Expert Comment

ID: 41764667
Assume [associated work order form] name is  [awof] with key field id
Subform has a key field id
Macro: works on a current subrecord field's click event.
Try other events and report back.

Form Name [awof]                            
View:          Form
Where Condition= id= CustomerInfo!WorkOrdersControl.Form!id
Date Mode      Edit

WorkOrdersControl refers to the subform/subreport control that contains the subform WorkOrders. It may be the same name as the subform's name.

here first id, in where condition, referes to awof.id

Author Comment

ID: 41766107
The first approach gets it going but still need some tweaking. It does not take the whole value in text116 Please see attachment.
LVL 31

Assisted Solution

hnasr earned 1000 total points
ID: 41766114
Both an event procedure or a macro should work, although I prefer procedures.

Upload a sample database to apply solutions.
LVL 50

Accepted Solution

Dale Fye earned 1000 total points
ID: 41766140
Normally, fields with an "ID" attached are numeric, at least they are in my databases.  Since this is a text value, you would need to use:

docmd.openform "WorkOrder",  , , "[WorkOrderID] = '" & me.txt116 & "'", acFormEdit

this wraps the value of me.txt116 with single quotes and would evaluate to:

[WorkOrderID] = 'FLOO0173-0000002'

Or, if it is easier for you to read and understand:

docmd.openform "WorkOrder",  , , "[WorkOrderID] = " & chr$(34) & me.txt116 & chr$(34), acFormEdit

Where the Chr$(34) character is a double quote, so the critieria would actually evaluate to:

[WorkOrderID] = "FLOO0173-0000002"

You could also use:

docmd.openform "WorkOrder",  , , "[WorkOrderID] = """ & me.txt116 & """", acFormEdit

But I find that difficult to read.  It embeds two double quotes right after the = and before the first ", then uses four consecutive double quotes, which access interprets as appending a single quote character, so it would also evaluate like:

[WorkOrderID] = "FLOO0173-0000002"

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question