match a subform field value to a field in a 2nd subform

[Guest]

I have a main form named user_Match.

It has three subform containers:
sub_BUSays with source object form named sub_match_BUSays
sub_RptSays with source object form named sub_match_ReportSays
sub_BTSays with source object form named sub_match_BTSays

sub_match_BUSays has a field called BU_BT_Portfolio_ID.
sub_match_ReportSays has a field called MRE_PortfolioID.
sub_match_BTSays has a field called BT_Portfolio_ID.

The portfolio id is a unique 10 digit number stored as a string.

I want to use the parent form to identify incorrect portfolio id's
in the BUSays and ReportSays subforms.

I would like to be able to doubleclick the portfolio id field
in any of the three subforms in order to display the record
with a matching portfolio id in the other two subforms.

However, I don't want to filter down to just that record.
Each subforms recordset needs to be available so I can search
or navigate to find the correct record in the case of no match
or an incorrect match.

The DoCmd.OpenForm with OnOpen Bookmark doesn't seem to work since
it will open separate instances of the subforms.

I have been try to adjust the code suggested by Wayne-I-M in
"how to select record in the subform and to show it in the main form"
which uses setfocus, findfirst, and bookmark within the initiating
event to change the record on the parent. But I can't seem to get
the references to the 2nd and 3rd subforms right when I try to modify it for
for this application.

With this code I get an error message that says:
"invalid method in an expression" but it doesn't tell me which
expression

Forms!user_Match!sub_RptSays.Form!MRE_ProjectID = _
Forms!user_Match!sub_BUsays.Form!BU_BT_Portfolio_ID
Forms!user_Match!sub_RptSays.Form.SetFocus

Set rst = Me("sub_RptSays").Form.RecordsetClone
rst.FindFirst "MRE_PortfolioID = " & _
Forms!user_Match!sub_BUsays.Form!BU_BT_Portfolio_ID
Me("sub_BUSays").Form.Bookmark = rst.Bookmark

If I use the referential syntax from
http://www.mvps.org/access/forms/frm0031.htm
so that the code looks like this:

Me!sub_RptSays.Form!MRE_ProjectID = Me.BU_BT_Portfolio_ID
Me!sub_RptSays.Form!MRE_ProjectID.SetFocus

Set rst = Me("sub_BUSays").Form.RecordsetClone
rst.FindFirst "sub_RptSays = " & Me.BU_BT_Portfolio_ID
Me("sub_BUSays").Form.Bookmark = rst.Bookmark


I get an error trap message that says it can't find the field
sub_RptSays

If I swap the container names for the actual names of the subforms
I get an error trap message that says it can't find the field
sub_match_ReportSays

I have tried adding square brackets and several other permutations
but nothing seems to work.

Does anyone have any ideas? What am I doing wrong?
Thanks,

 

[Guest]

If your respective Portfolio_ID fields are the first field in each subform,
then this will work:

Dim intMatchID As Integer

'This will store the ID number you want to search for

'Replace <ID Name> with the match ID from the subform you are
double-clicking in

intMatchID = Me.<ID Name>

'This will set the focus on each of the subforms 1st fields and then search
for the
'ID number stored in intMatchID

'Replace <Parent Form> and <Subform x> with the name of the form all 3
'subforms are in, and the respective subforms you would like to search in

Forms!<Parent Form>.Form.<Subform 1>.SetFocus
DoCmd.FindRecord intMatchID, acEntire, , acSearchAll

Forms!<Parent Form>.Form.<Subform 2>.SetFocus
DoCmd.FindRecord intMatchID, acEntire, , acSearchAll

 

[Guest]

The code I posted will also work if your respective ID fields are not the
first fields in the tab order, but first you will need to click in the
appropriate field to search through in each subform before your double-click
(this will set the focus on the appropriate field)...there is probably some
way to code in the setfocus for that specific field, but I'm not able to find
it right now.

 

[Guest]

Thanks Tim,
I have made some progress.
The setfocus lines don't error out anymore.
I can also push a value into a control on another form.
But I can't get the FindRecord to work.
I get an errormsg stating "command or action isn't available now"

Dim intMatchID As String
Dim intMatchID2 As String
intMatchID = Me.BU_BT_Portfolio_ID
intMatchID2 = Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID

'The next line works though it is not what I am trying to do.
'Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID = _
Me!BU_BT_Portfolio_ID

'Forms![user_Match].Form!HoldThatThought.SetFocus
'Forms![user_Match].Form!sf_ReportSays.SetFocus
'Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID.SetFocus
'OR
Me.Parent.HoldThatThought.SetFocus
Me.Parent.sf_ReportSays.SetFocus
Me.Parent.[sf_ReportSays].Form.MRE_PortfolioID.SetFocus
DoCmd.FindRecord intMatchID, acEntire, , acSearchAll, , acCurrent

I've also tried sticking code in the gotfocus events of the other subform
and its ID field but the event never fires (clicking in the target fields
before firing the code doesn't make a difference). GotFocus fires on the
main form, but...I'm not sure what I can do with that...

Any ideas what I should try next?

 

[Guest]

I finally got it to work.
I'm not sure if this is the best way or the right way, but...
All that matters is it works.

OK 2 recap the mainform holds 3 subforms with different control sources.
The 3 recordsets do have a common key. I want to be able to doubleclick on
the common key field in any of the 3 subforms at which point the other two
subforms will show the corresponding record for that keyvalue.

Solution:
Added a textbox in the mainform called holdthatthought to hold the keyvalue
and this code:
Private Sub Form_Load()
Public lookfor As String
Me.HoldThatThought = ""
lookfor = ""
Me.sub_RptSays.SetFocus
Me.sub_RptSays.Form.MRE_PortfolioID.SetFocus
Me.sub_BUsays.SetFocus
Me.sub_BTSays.Form.BT_Portfolio_ID.SetFocus
Me.sub_BTSays.SetFocus
Me.sub_BUsays.Form.BU_BT_Portfolio_ID.SetFocus
End Sub

Then in each of the subforms there are two modules like this:
Private Sub BT_Portfolio_ID_DblClick(Cancel As Integer)
On Error GoTo Err_BT_Portfolio_ID_DblClick
'put the keyvalue into the mainform holder
lookfor = Me.BT_Portfolio_ID
Me.Parent.HoldThatThought = lookfor
'set the focus to the keyvalue control on subform 2
Me.Parent.SetFocus
Me.Parent!sub_BUsays.SetFocus
Me.Parent!sub_BUsays.Form.BU_BT_Portfolio_ID.SetFocus
'set the focus to the keyvalue control on subform 3
Me.Parent.SetFocus
Me.Parent!sub_RptSays.SetFocus
Me.Parent!sub_RptSays.Form.MRE_PortfolioID.SetFocus
Exit_BT_Portfolio_ID_DblClick:
Exit Sub
Err_BT_Portfolio_ID_DblClick:
MsgBox Err.Description
Resume Exit_BT_Portfolio_ID_DblClick
End Sub

Private Sub BT_Portfolio_ID_GotFocus()
'get the keyvalue from the holder on the mainform
lookfor = Me.Parent.HoldThatThought
'find the first record with that keyvalue unless the keyvalue is empty
If Not lookfor = "" Then DoCmd.FindRecord lookfor, acEntire, ,
acSearchAll, , acAll, True
'move to the first record if the current record doesn't match the keyvalue
If Not Me.ActiveControl = lookfor Then DoCmd.GoToRecord , , acFirst
End Sub

I've spent about a week searching all over this forum, on the net, etc.
trying to figure this out and get it to work consistently. I am glad it
works but wonder if perhaps there was an easier/more elegant/more correct way
of accomplishing the goal?

I'd really appreciate some feedback on this.

Thanks,

owp^3

Thanks Tim,
I have made some progress.
The setfocus lines don't error out anymore.
I can also push a value into a control on another form.
But I can't get the FindRecord to work.
I get an errormsg stating "command or action isn't available now"

Dim intMatchID As String
Dim intMatchID2 As String
intMatchID = Me.BU_BT_Portfolio_ID
intMatchID2 = Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID

'The next line works though it is not what I am trying to do.
'Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID = _
Me!BU_BT_Portfolio_ID

'Forms![user_Match].Form!HoldThatThought.SetFocus
'Forms![user_Match].Form!sf_ReportSays.SetFocus
'Forms![user_Match].Form![sf_ReportSays].Form.MRE_PortfolioID.SetFocus
'OR
Me.Parent.HoldThatThought.SetFocus
Me.Parent.sf_ReportSays.SetFocus
Me.Parent.[sf_ReportSays].Form.MRE_PortfolioID.SetFocus
DoCmd.FindRecord intMatchID, acEntire, , acSearchAll, , acCurrent

I've also tried sticking code in the gotfocus events of the other subform
and its ID field but the event never fires (clicking in the target fields
before firing the code doesn't make a difference). GotFocus fires on the
main form, but...I'm not sure what I can do with that...

Any ideas what I should try next?

The code I posted will also work if your respective ID fields are not the
first fields in the tab order, but first you will need to click in the
appropriate field to search through in each subform before your double-click
(this will set the focus on the appropriate field)...there is probably some
way to code in the setfocus for that specific field, but I'm not able to find
it right now.