Go to matching record from one subform to another

[Kurt]

I have an unbound main form (frmProjects) with several tabs: Home, Studies,
Manuscripts, etc. Each tab has a different subform. On the Studies tab is a
subform (fsubStudies) which shows studies; on the Manuscripts tab is a
subform (fsubManuscripts) which shows manuscripts, etc. On the Home tab is a
continuous subform (fsubProjectList) which shows a list of all Projects.

See screenshot: http://members.cox.net/kheisler6/frmProjects.jpg

I would like to click on a project listed on the Home tab’s ProjectList
subform and move the focus to the appropriate subform (one on of several
tabs) and the appropriate record on the subform. For example, when the user
clicks a project on the fsubProjectList:

- If ProjectType = “Study,†go to the Studies tab and go to the appropriate
record (i.e., with matching ProjectID) on the Study subform.

- If ProjectType = “Manuscript,†go to the Manuscript tab and go to the
appropriate record (i.e., with matching ProjectID) on the Manuscript subform.

And so forth.

I tried putting code in the On Click event of the ProjectTitle which sets
the focus to the appropriate tab/subform and then finds the matching record:

###

Private Sub ProjectTitle_Click()
If Me.ProjectTypeID.Value = "Study" Then
' set focus on subform/tab
Me.Parent!pgStudies.SetFocus
' go to matching record
Me.DoCmd.OpenForm "fsubStudies", , , "[ProjectID] = " &
Forms![fsubProjectList]![ProjectID]
ElseIf Me.ProjectTypeID.Value = "Manuscript" Then
'...
End If

###

But this gives me a Compile error: Method or data member not found, which
highlights the last [ProjectID] in the main SQL statement.

What am I doing wrong? Thanks.

- Kurt

 

[Graham Mandeno]

Hi Kurt

You are getting the error because DoCmd is a method of the Application
object, not "Me" (which in this scope is a Form object).

In any case, DoCmd.OpenForm is not what you need, because that would open a
new, independent instance of "fsubStudies" as a *main* form. Thi instance
that you want to refer to is already open, contained in the subform control.

Try this:

Dim strCtlName as string
Dim sbfrm as Form
' first identify the correct subform control
Select case Me.ProjectType
case "Study"
strCtlName = "fsubStudies"
' name of the subform *control* which contains the Studies subform
case "Manuscript"
strCtlName = "fsubManuscripts"
' etc...
End Select
' now set focus to the subform control and reference the contained form
With Me.Parent(strCtlName)
.SetFocus
Set sbfrm = .Form
End With
' now find the desired record
With sbfrm.RecordsetClone
.FindFirst "[ProjectID] = " & Me.ProjectID
If not .NoMatch then sbfrm.Bookmark = .Bookmark
End With

This assumes that the primary key field of each of the subforms' recordsets
is named "ProjectID". If not, you will need to set an additional variable
in the case statement to the name of the key field.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an unbound main form (frmProjects) with several tabs: Home, Studies,
Manuscripts, etc. Each tab has a different subform. On the Studies tab is
a
subform (fsubStudies) which shows studies; on the Manuscripts tab is a
subform (fsubManuscripts) which shows manuscripts, etc. On the Home tab is
a
continuous subform (fsubProjectList) which shows a list of all Projects.

See screenshot: http://members.cox.net/kheisler6/frmProjects.jpg

I would like to click on a project listed on the Home tab's ProjectList
subform and move the focus to the appropriate subform (one on of several
tabs) and the appropriate record on the subform. For example, when the
user
clicks a project on the fsubProjectList:

- If ProjectType = "Study," go to the Studies tab and go to the
appropriate
record (i.e., with matching ProjectID) on the Study subform.

- If ProjectType = "Manuscript," go to the Manuscript tab and go to the
appropriate record (i.e., with matching ProjectID) on the Manuscript
subform.

And so forth.

I tried putting code in the On Click event of the ProjectTitle which sets
the focus to the appropriate tab/subform and then finds the matching
record:

###

Private Sub ProjectTitle_Click()
If Me.ProjectTypeID.Value = "Study" Then
' set focus on subform/tab
Me.Parent!pgStudies.SetFocus
' go to matching record
Me.DoCmd.OpenForm "fsubStudies", , , "[ProjectID] = " &
Forms![fsubProjectList]![ProjectID]
ElseIf Me.ProjectTypeID.Value = "Manuscript" Then
'...
End If

###

But this gives me a Compile error: Method or data member not found, which
highlights the last [ProjectID] in the main SQL statement.

What am I doing wrong? Thanks.

- Kurt