Referring to Control on a Subform held in Tabs (Pages) on Form

[Guest]

I've asked about this yesterday, but maybe I wasn't clear. Here's my dilemma:
I have a Form containing (3) separate tabs (Access calls them "pages"). In
each tab are subforms. I need to be able to drill-down to a specific record
on a subform on the same form, but different tab. Example:

Company Record has subform displaying (5) contacts for that client. I want
to click a button associated to the contact in the subform and have it switch
the tab (page) view to the appropriate tab, and display the filtered result
within the tabbed form. The current code only opens the subform as a form
(outside of the tabbed form format).

I think I'm simply overlooking something obvious! Thank you in advance for
your assistance!

Scott

 

[Allen Browne]

You have a subform, with a command button that repeats on each row.
You want to click this button, and have Access apply a filter to another
subform in one of the other pages of the tab control on the main form.

To achieve that, you will need to understand how to write some VBA code for
the Click event procedure of the form. The basic logic will be:
- Save any edits in progress in this subform.
- Make sure it is not the NewRecord.
- Construct a string to use to filter or find the record in the other
subform.
- FindFirst in the RecordsetClone of the form in the other subform, test
NoMatch, and set the form's Bookmark to the found record. Alternatively, set
the Filter and FilterOn of the other subform.
- Set the Value of the tab control to the page that contains the other
subform.

This kind of thing:

Dim frm As Form
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Pick a record."
Else
strWhere = "ID = " & [ID]
Set frm = Me.Parent![Sub2].Form
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = .Bookmark
Me.Parent![Tab0] = Me.Parent![Page2].PageIndex
End If
End With
End If
Set frm = Nothing

 

[Guest]

Dear Mr. Browne,

Thank you for your response.

I have a better-than-novice understanding on the VBA code, however, when I
attempted to implement the solution suggested, I am still receiving errors
indicating that the action "cannot find the control 'controlname' referred to
in your expression". Where I'm having the difficulty is in the Else
statement in the identification of Parent fields (you reference [Tab0] &
[Page2]). I don't think I'm passing the proper form and/or field names in
the proper convention. Is the [Tab0] the Main Form holding the tab
references and [Page2] the tab I wish to be my destination with the filtered
result?

Best Regards,
Scott

You have a subform, with a command button that repeats on each row.
You want to click this button, and have Access apply a filter to another
subform in one of the other pages of the tab control on the main form.

To achieve that, you will need to understand how to write some VBA code for
the Click event procedure of the form. The basic logic will be:
- Save any edits in progress in this subform.
- Make sure it is not the NewRecord.
- Construct a string to use to filter or find the record in the other
subform.
- FindFirst in the RecordsetClone of the form in the other subform, test
NoMatch, and set the form's Bookmark to the found record. Alternatively, set
the Filter and FilterOn of the other subform.
- Set the Value of the tab control to the page that contains the other
subform.

This kind of thing:

Dim frm As Form
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Pick a record."
Else
strWhere = "ID = " & [ID]
Set frm = Me.Parent![Sub2].Form
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = .Bookmark
Me.Parent![Tab0] = Me.Parent![Page2].PageIndex
End If
End With
End If
Set frm = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I've asked about this yesterday, but maybe I wasn't clear. Here's my
dilemma:
I have a Form containing (3) separate tabs (Access calls them "pages").
In
each tab are subforms. I need to be able to drill-down to a specific
record
on a subform on the same form, but different tab. Example:

Company Record has subform displaying (5) contacts for that client. I
want
to click a button associated to the contact in the subform and have it
switch
the tab (page) view to the appropriate tab, and display the filtered
result
within the tabbed form. The current code only opens the subform as a form
(outside of the tabbed form format).

I think I'm simply overlooking something obvious! Thank you in advance
for
your assistance!

Scott

 

[Allen Browne]

Okay, I'm expecting that this code is executing in the subform's module.

Presumably this is the line that is failing:
Me.Parent![Tab0] = Me.Parent![Page2].PageIndex

The example assumes a tab control named "Tab0", with the 2nd page of the tab
control is named "Page2". Substitute your actually names for these. The
purpose of the line is to set the Value of the tab control to the PageIndex
of the page you want. That's how you show a particular page of a tab
control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a better-than-novice understanding on the VBA code, however, when I
attempted to implement the solution suggested, I am still receiving errors
indicating that the action "cannot find the control 'controlname' referred
to
in your expression". Where I'm having the difficulty is in the Else
statement in the identification of Parent fields (you reference [Tab0] &
[Page2]). I don't think I'm passing the proper form and/or field names in
the proper convention. Is the [Tab0] the Main Form holding the tab
references and [Page2] the tab I wish to be my destination with the
filtered
result?

Best Regards,
Scott

You have a subform, with a command button that repeats on each row.
You want to click this button, and have Access apply a filter to another
subform in one of the other pages of the tab control on the main form.

To achieve that, you will need to understand how to write some VBA code
for
the Click event procedure of the form. The basic logic will be:
- Save any edits in progress in this subform.
- Make sure it is not the NewRecord.
- Construct a string to use to filter or find the record in the other
subform.
- FindFirst in the RecordsetClone of the form in the other subform, test
NoMatch, and set the form's Bookmark to the found record. Alternatively,
set
the Filter and FilterOn of the other subform.
- Set the Value of the tab control to the page that contains the other
subform.

This kind of thing:

Dim frm As Form
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Pick a record."
Else
strWhere = "ID = " & [ID]
Set frm = Me.Parent![Sub2].Form
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = .Bookmark
Me.Parent![Tab0] = Me.Parent![Page2].PageIndex
End If
End With
End If
Set frm = Nothing

I've asked about this yesterday, but maybe I wasn't clear. Here's my
dilemma:
I have a Form containing (3) separate tabs (Access calls them "pages").
In
each tab are subforms. I need to be able to drill-down to a specific
record
on a subform on the same form, but different tab. Example:

Company Record has subform displaying (5) contacts for that client. I
want
to click a button associated to the contact in the subform and have it
switch
the tab (page) view to the appropriate tab, and display the filtered
result
within the tabbed form. The current code only opens the subform as a
form
(outside of the tabbed form format).

I think I'm simply overlooking something obvious! Thank you in advance
for
your assistance!

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George