Solved

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

Posted on 2016-08-21
5
33 Views
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]

[Forms]![WorkOrder]![WorkorderID]=[Text116]
0
Comment
Question by:thao-nhi
  • 2
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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.
0
 
LVL 30

Expert Comment

by:hnasr
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
0
 

Author Comment

by:thao-nhi
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.
Capture.JPG
0
 
LVL 30

Assisted Solution

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

Upload a sample database to apply solutions.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 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"
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now