Open form from a subform (1 Viewer)

[ro801]

Hi, I hope someone can help, as I find myself at an impasse. My database has two tables, UN is the PK for one (CRT_demographics) and it is related to the same entry in a second table (simply called CRT) with a field name CRT_UN.
Now, I have a form called list_planner in which is a subform procedure_day_planner_datasheet_form that is a continuous datasheet. I would simply like to be able to double click on the field CRT_UN in this subform and call the data entry form, current_crt_query_form so that I can edit in more detail. I can copy/paste the CRT_UN manually into a parameter box but that adds steps that, IMHO, makes the flow of the process, awkward, especially for others in my team. Surely this must be possible, right? :banghead:
Many thanks in advance.

 

[arnelgp]

you have a parameter query as a record source of your form current_crt_query_form.
copy the original query to another (say new_query).
modify the new_query so that the parameter comes from the subform ([Forms]![mainFormName]![crt_un].

 

[ro801]

Thanks for your reply, I think I kinda follow.
This is what I did, but it only partially works.

CURRENT_CRT_QUERY_FORM's record source is now CURRENT_CRT_QUERY with the criteria: [Forms]![PROCEDURE_DAY_PLANNER_DATASHEET_FORM]![CRT_UN]

On the double click event on the subform PROCEDURE_DAY_PLANNER_DATASHEET_FORM I entered:
DoCmd.OpenForm "CURRENT_CRT_QUERY_FORM"

This works perfectly when I only have that subform open, but like I said in my post, this subform actually resides on the mainform LIST_PLANNER with two other subforms (one of which also uses CRT_UN (?)).

When I either go to open the form manually or by double clicking the correct field on the subform from within LIST_PLANNER I get:

Enter Parameter Value
Forms!PROCEDURE_DAY_PLANNER_DATASHEET_FORM!CRT_UN

When I change the criteria to:
[Forms]![LIST_PLANNER]![CRT_UN] Instead I get only the details from the other subform on LIST_PLANNER. I have tried deleting this second subform, with no difference.

Do I have to refer to the subform through the main form? If so how do I write that? Or likewise I may have this completely wrong, I would be interested in hearing your thoughts. Many Thanks

 

[arnelgp]

ok instead of that use Tempvars as your parameter.
on double-click event of your control crt_un on subform:

private sub crt_un_dblclick(cancel as integer)
if isnull(tempvars("crt_un")) then tempvars.add "crt_un", me.crt_un
tempvars("crt_un")=me.crt_un
end sub

use this as your parameter Tempvars("crt_un"):

 

[ro801]

ok instead of that use Tempvars as your parameter.
on double-click event of your control crt_un on subform:

private sub crt_un_dblclick(cancel as integer)
if isnull(tempvars("crt_un")) then tempvars.add "crt_un", me.crt_un
tempvars("crt_un")=me.crt_un
end sub

use this as your parameter Tempvars("crt_un"):

Unfortunately that came back with an error. I took a screen dump of it for information, what are your thoughts?

 

[ro801]

Thank you, arnelgp for your help on this.
For those that are interested, I thought I would post my solution once I figured it out. It turned out the issue was that the field name was the same (CRT_UN) across all the subforms on the mainform. Once I changed the one I wanted, to something unique (PLANNER_CRT_UN) the following code work beautifully:

Private Sub PLANNER_CRT_UN_DblClick(Cancel As Integer)
    Const cstrForm As String = "CURRENT_CRT_QUERY_FORM"
    DoCmd.OpenForm cstrForm, WhereCondition:="[CRT_UN]='" & Me.PLANNER_CRT_UN & "'"
End Sub

Simple really, another Access nugget learnt for the future