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

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]

[Forms]![WorkOrder]![WorkorderID]=[Text116]
thao-nhiAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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"
0
 
Dale FyeCommented:
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.
0
 
hnasrCommented:
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
0
 
thao-nhiAuthor Commented:
The first approach gets it going but still need some tweaking. It does not take the whole value in text116 Please see attachment.
Capture.JPG
0
 
hnasrConnect With a Mentor Commented:
Both an event procedure or a macro should work, although I prefer procedures.

Upload a sample database to apply solutions.
0
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.