Selecting values with VBA

[benkingery]

I have a form that opens up when you double click a button on another form. The purpose of opening the form is that the form that opens is intended to act as a selection form. The idea is you find the record on the form that pops up, double click on the value you choose and two things happen:

1) The value you double clicked on gets written back to the original form in a specified field
2) The form that popped up closes.

I can figure out # 2 easy enough. anyone have any ideas on #1?

Thanks

 

[pbaldy]

From the second form:

Forms!OriginalFormName.ControlName = Me.ControlName

 

[CEH]

Private Sub cmdInsert_DoubleClick()
Forms![frmFormName]![FieldName] = Me.FieldName


DoCmd.Close

End Sub

Stick your close in there too

 

[benkingery]

Thank you both for your reply. I find that it works just fine, however a detail I neglected to mention is that the original form is actually a SUBFORM. When I now try to run this as you both have detailed above it indicates that it can't find the form I'm referencing on the pop-up form.

Any ideas on how to navigate this?

 

[pbaldy]

http://www.mvps.org/access/forms/frm0031.htm

 

[benkingery]

Thanks pbaldy for your reply, however I think I'm still a little hazy after seeing the reference sheet you indicated. My cursor is in the subform on the main form, but I need to put the code on the pop-up form. Here is what I've tried so far and can't get either of them to work.

On pop-up form:
Private Sub Subcategory_ID_DblClick(Cancel As Integer)
Me!frm_OS_Build_subform_M.frm_OS_Build!category_id = Me.Subcategory_ID
DoCmd.Close
End Sub

Private Sub Subcategory_ID_DblClick(Cancel As Integer)
Forms!frm_OS_Build_subform_M.frm_OS_Build!category_id = Me.Subcategory_ID
DoCmd.Close
End Sub


Private Sub Subcategory_ID_DblClick(Cancel As Integer)
Forms!frm_OS_Build.frm_OS_Build_subform_M!category_id = Me.Subcategory_ID
DoCmd.Close
End Sub


As I mentioned, none of these are working, so I know i have some syntax wrong. I just need to refer to the proper control on the subform which is located on the main form. I'll be most appreciative of any help you are able to offer.

Thanks

 

[pbaldy]

Well, you said the code would be running from the pop-up form, so the Me would be referencing that (Me references the object containing the code). You might eliminate the Me and use the full reference for everything. What are the names of the forms/subforms involved?

 

[benkingery]

The main form is called frm_OS_Build. On that form there is a subform called frm_OS_Build_subform_M. Within frm_OS_Build_subform_M there is a control called category_id. When the label for category_id is double clicked, a pop-up form called frm_OS_Taxonomy opens up with the following code:

Private Sub category_id_Label_DblClick(Cancel As Integer)
DoCmd.OpenForm "frm_OS_Taxonomy", acNormal, , , acFormEdit, acWindowNormal
End Sub

There is a control on frm_OS_Taxonomy called Subcategory_ID. Written on that control is where I need to place the code that will "write back" the values of the selected field to frm_OS_Build_subform_M. The code I was working with above works when I have open frm_OS_Build_subform_M only. However, when I have open frm_OS_Build which has the previous form in a subform relationship, the code written on frm_OS_Taxonomy does not know to which form/control I am referring. Therein lies my problem. I need to know how to refer to frm_OS_Build_subform_M.category_id while it is displayed as a subform on frm_OS_Build.

I hope you're able to wade through all that. Made my bloodpressure go up just writing it.

 

[pbaldy]

From the link:

Forms!Mainform!Subform1.Form!ControlName

so

Forms!frm_OS_Build!frm_OS_Build_subform_M.Form!category_id

 

[benkingery]

Hmmmm. Still not working.

Here is what I have. I copied it directly from the VB editor.

Private Sub Subcategory_ID_DblClick(Cancel As Integer)
Forms!frm_OS_Build!frm_OS_Build_subform_M.Form!category_id = Me.Subcategory_ID
DoCmd.Close
End Sub


Any thoughts? The error message reads:

Run-time error '2465':

Microsoft Office Access can't find teh field 'frm_OS_Build_subform_M' reffered to in your expression.

 

[CEH]

One little trick I sometimes use is the expression builder.. Then you will have the path written correctly by Access... No typos or errors... might give it a try.

 

[SOS]

Any thoughts?

Yeah, is frm_OS_Build_subform_M actually the name of the subform container control that houses the subform on the main form. If not, you need to refer to THAT instead of the subform name (like Paul already had mentioned to you). The error message leads me to believe that the container may be called something other than frm_OS_Build_subform_M and in fact might be named CHILD227 or something like that.

If you aren't sure what the subform container control is, see the first screenshot here for an explanation of what it is and how to select it.

 

[benkingery]

You got it SOS. Thank you. This was exactly the problem. I learn more each day.

 

[SOS]

Kudos go to Paul - I get credit for the assist