How to reference selections in a subform w/ a button

[nharrison]

I have a form, Marketing Targets, which pulls records from a simple table that contains a field called Contact ID (key field). This form has a subform, Action Items subform, which also has the field Contact ID. The subform displays records that share the same Contact ID with the current record in the master form.

What I want to do is create a button that will open a separate form, Action Items, to the record that is currently selected in the Action Items subform.

I have tried creating such a button within the subform, but I haven't had any luck with that. Attached is a screenshot that helps clarify the situation.

What I am attempting may not even be possible, but I have faith in you guys

 

[pbaldy]

Does this help?

http://www.baldyweb.com/wherecondition.htm

 

[nharrison]

No, not really. :/

I already know how to do that, and have implemented it several times in this database. What is stumping me is how to reference a highlighted record in a subform.

So, using your code,

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

would work, except "Me.ControlName" would need to be replaced with some way to reference the selected record.

If you'll look at the screenshot again, you'll notice that the subform is essentially a datasheet list displaying multiple records. I want the second form to open to the currently selected record from that list.

 

[SOS]

If you just put a button on your subform (say, on the right) you can simply use the

DoCmd.OpenForm "SecondFormName", acViewNormal, , "[PrimaryKeyFieldInNewForm] = " & Me!YourPrimaryKeyFieldOnCurrentForm

Will work just fine. If your primary key field is not numeric, but text then you would need:

DoCmd.OpenForm "SecondFormName", acViewNormal, , "[PrimaryKeyFieldInNewForm] = '" & Me!YourPrimaryKeyFieldOnCurrentForm & "'"

 

[pbaldy]

Well, you said you'd tried it with a button on the subform, presumably in the detail section. The linked code would work in that situation, as "Me.ControlName" will reference the record clicked on.

 

[nharrison]

Correct, however when I put a button in the details section, it doesn't display in the datasheet subform on my main form

 

[SOS]

Correct, however when I put a button in the details section, it doesn't display in the datasheet subform on my main form

You would need to use a continuous form instead of datasheet. However, you can format a continuous form to LOOK very much like a datasheet, while keeping the other functionality.

 

[pbaldy]

If you want to keep the datasheet (I prefer continuous myself), you can use the double-click event of one of the textboxes instead of a button.

 

[SOS]

you can use the double-click event of one of the textboxes instead of a button.

I forgot about that. I have done that in the past myself (boy it sure is a Monday... )

 

[nharrison]

Thanks a lot guys. Its been ages since I used continuous forms, I had completely forgotten about them. Using that method will also open up a few other functionality items I was grappling with. Appreciate the help.