Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-21
5
Medium Priority
?
73 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 49

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.
0
 
LVL 31

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 31

Assisted Solution

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

Accepted Solution

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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

972 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