click on a record in a subform and automatically open another form

[Tracy]

Is there a way to programmically open a form automatically when I click on a
record in a subform? I need the form to open up with filtered records based
on the record selected in my subform.

I have a form called BusinessUnitsCrimalJusticeYear. In that form I have a
subform named BU_CJ. The subform opens up all the records pertaining to
criminal justice. I need to be able to click in a record - say, "justice" is
the text of the record and to some how get it to automatically open up
another form named qryPrimaryEntry but only give me the records filtered
named "justice" which resides under the field CU_grp_name.

Is this possible?

Thanks a million for your help with this.

Tracy

 

[Dirk Goldgar]

Is there a way to programmically open a form automatically when I
click on a record in a subform? I need the form to open up with
filtered records based on the record selected in my subform.

I have a form called BusinessUnitsCrimalJusticeYear. In that form I
have a subform named BU_CJ. The subform opens up all the records
pertaining to criminal justice. I need to be able to click in a
record - say, "justice" is the text of the record and to some how get
it to automatically open up another form named qryPrimaryEntry but
only give me the records filtered named "justice" which resides under
the field CU_grp_name.

Is this possible?

Sure it's possible. The only question is whether you want the new form
to be opened by a click anywhere in the record, or just on the record
selector, or in a particular control on the subform. Also, bear in mind
that users often click on a record or control to begin editing it; is a
simple click what you want, or do you want to use a double-click?

Let's suppose you want to click -- once -- on the record selector in the
subform, and have that open up the other form. You'd create an event
procedure for the form's Click event, and it would look something like
this:

'----- start of example code -----
Private Sub Form_Click()

If Not IsNull(Me!SomeFieldOrOther) Then

DoCmd.OpenForm "qryPrimaryEntry ", _
WhereCondition:="CU_grp_name = " & _
Chr(34) & Me!SomeFieldOrOther & Chr(34)
End If

End Sub
'----- end of example code -----

You haven't told us what field on the subform has the value you want to
filter for, so I just called it "SomeFieldOrOther". I also assumed it
will never contain a double-quote character, so I can use that character
(Chr(34)) to delimit the value.

 

[Tracy]

I think I didn't explain things correctly or left out some info... I do
apologize and really appreciate your help with this. I don't want people
editing the subform but let's say they could click on the record selector and
choose the data they need. There are several selections but one of them is
"Section 10: Superior Courts". The control source name is UC_grp_name but
it's actually named Budget Unit. I would like to open the form
CriminalJusticeYear and click in the subform and select ever which record
selector I need and by doing so, open up in the same form CriminalJusticeYear
as a subform the form f_PrimaryEntry. The form f_PrimaryEntry has a text box
named GroupName and it's control source is UC_grp_name. (Should I name both
of them Budget Unit or is it okay to have different names on each?) I also
need the f_PrimaryEntry to show all the records for the GroupName but display
them by bp_num on each screen.

When I write the event procedure, I write it in the forms On Click - not the
subform, correct?

Thanks a ton!!!

 

[Dirk Goldgar]

I'm afaid I'm not understanding you, Tracy. See my questions inline:

I think I didn't explain things correctly or left out some info... I
do apologize and really appreciate your help with this. I don't want
people editing the subform but let's say they could click on the
record selector and choose the data they need.

If you don't want people editing the subform, but just want to use it to
display a list of choices, would it maybe be better to use a list box
instead?

There are several
selections but one of them is "Section 10: Superior Courts".

Do you mean, there are several records displayed by the subform, and for
one of them a text box will be displaying the value "Section 10:
Superior Courts"?

The
control source name is UC_grp_name but it's actually named Budget
Unit.

Do you mean that "Budget Unit" is the name of the text box, but
"UC_grp_name" is the name of the field to which the text box is bound?
Or is it the other way around? Or have I misunderstood completely?

I would like to open the form CriminalJusticeYear and click in
the subform and select ever which record selector I need

So, "CriminalJusticeYear" is the name of the main form that contains
this subform, the subform we're talking about above?

and by doing
so, open up in the same form CriminalJusticeYear as a subform the
form f_PrimaryEntry.

I'm not sure what you mean here. Before, I thought you were talking
about opening a separate form to show the details of whatever the user
clicked in the subform. Now you say you want to open it "as a subform".
Are we talking about two subforms on the main form, then? Subforms are
always open, so it's not a question of *opening* one, but maybe you're
asking how to get a second "details" subform to show the information
related to the item clicked in the first, "list" subform. That would be
easy enough to do.

The form f_PrimaryEntry has a text box named
GroupName and it's control source is UC_grp_name. (Should I name both
of them Budget Unit or is it okay to have different names on each?)

The names of the controls don't really matter.

I also need the f_PrimaryEntry to show all the records for the
GroupName but display them by bp_num on each screen.

If I understand you, that's just a matter of setting the form's
recordsource query to sort the records the way you want. But first,
let's figure out how you want the forms and subforms to work. I'm not
sure yet what you want.

When I write the event procedure, I write it in the forms On Click -
not the subform, correct?

No, it would be in the Click event of the subform; more exactly, of the
form object that is being displayed as a subform. It's important to
understand the relationships of these objects: there's a main form, and
on that main form there is a subform *control*, which acts as a window
to display a different form. So there are three different objects
involved: two form objects (one main form and one form that will be
serving as a subform), and one subform control. Each of these objects
has properties and events of its own. When you open the main form in
design view, you can see the details of both the main form and the
subform form. If you click on the subform once, you'll select the
subform *control*. Then if you click on it again, you'll be working
with the subform *form*. Sometimes this is confusing.

The event procedure I suggested in my previous post was intended for the
Click event of the subform *form*, not the main form or the subform
*control* on that form.

 

[Tracy]

I'm afaid I'm not understanding you, Tracy.

Dirk, I responded to your questions inline as well. THANKS SO MUCH!

See my questions inline:

If you don't want people editing the subform, but just want to use it to
display a list of choices, would it maybe be better to use a list box
instead? Yes, most definitely - however, the request is not to have a list box so I had to do it this way. The form that will open up by clicking on the data selected in the subform will be editable.


Do you mean, there are several records displayed by the subform, and for
one of them a text box will be displaying the value "Section 10:
Superior Courts"? Yes.


Do you mean that "Budget Unit" is the name of the text box, but
"UC_grp_name" is the name of the field to which the text box is bound? Yes, that's right.
Or is it the other way around? Or have I misunderstood completely?


So, "CriminalJusticeYear" is the name of the main form that contains
this subform, the subform we're talking about above? Yes.


I'm not sure what you mean here. Before, I thought you were talking
about opening a separate form to show the details of whatever the user
clicked in the subform. Now you say you want to open it "as a subform".
Are we talking about two subforms on the main form, then? YES, I would like it to display to the right of the first subform after a particular record is clicked on.

Subforms are

 

[Dirk Goldgar]

Yes, most definitely - however, the request is not to
have a list box so I had to do it this way. The form that will open
up by clicking on the data selected in the subform will be editable.

The editability of the detail form (or subform) isn't dependent on
whether the summary list is presented in a list box or a subform. It
would be simpler to do this with a list box, because a list box has a
value, while a subform doesn't; however, if you have been told you
can't use a list box, a subform can be made to work. On the other hand,
if the only requirement is that the *detail* subform must be editable,
let me know so we can do it the easy way.

Yes, that's right.

YES, I
would like it to display to the right of the first subform after a
particular record is clicked on.

Okay. That's a different arrangement from my original understanding,
but no big deal.

Okay, but is there a way to do that without the
f_PrimaryEntry showing until a record was selected?

I think so. It should just be a matter of hiding the details subform
until the user clicks on the list subform.

Here's the way I would do it, based on the idea that the list must be
presented in a subform. As I said above, if a list box can be used
instead, the whole thing is simpler, so get back to me on that.

Place both subforms on the main form. Verify the names of the subform
controls -- for now, I'll assume that the name of the details subform
control will be "f_PrimaryEntry", the same as the form object it will
display. Set the Visible property of the subform *control* to False.

Put a text box somewhere on the main form, and set its Visible property
to False. Set its ControlSource property to

=[BU_CJ].[Form]![Budget Unit]

Set the name of this text box to "txtBudgetUnit". This text box, though
unseen by the user because it isn't visible, will reflect whatever is in
the [Budget Unit] text box on the subform named "BU_CJ". Check the name
of that subform control, by the way, to make sure it's right. Youi're
the one who gave me that name for the list subform.

For the f_PrimaryEntry subform control, set the Link Master Fields
property to

txtBudgetUnit

Set the Link Child Fields property to

UC_grp_name

That's all you need to do to get the details subform (f_PrimaryEntry) to
track the current record in the list subform (BU_CJ). The only thing
left to do is make the details subform visible in the Click event of the
BU_CJ *form object*. Click on the subform control for BU_CJ, then click
on it again to select the form object itself, then bring up the property
sheet for that form, go to the Event tab, choose [Event Procedure] for
the On Click property, then click the build button at the end of the
line and build this event procedure:

Private Sub Form_Click()

Me.Parent!f_PrimaryEntry.Visible = True

End Sub

And that ought to do it (barring any name mixups).

 

[Tracy]

Dirk, Yes the details subform (f_PrimaryEntry) is the only one that is
editable. And the first subform, that I can't use a list box for is where
people should click on the record selector of their choice from the text box
and open up the f_PrimaryEntry subform. I just wanted to get back with you

Yes, most definitely - however, the request is not to
have a list box so I had to do it this way. The form that will open
up by clicking on the data selected in the subform will be editable.

The editability of the detail form (or subform) isn't dependent on
whether the summary list is presented in a list box or a subform. It
would be simpler to do this with a list box, because a list box has a
value, while a subform doesn't; however, if you have been told you
can't use a list box, a subform can be made to work. On the other hand,
if the only requirement is that the *detail* subform must be editable,
let me know so we can do it the easy way.

Yes, that's right.

YES, I
would like it to display to the right of the first subform after a
particular record is clicked on.

Okay. That's a different arrangement from my original understanding,
but no big deal.

Okay, but is there a way to do that without the
f_PrimaryEntry showing until a record was selected?

I think so. It should just be a matter of hiding the details subform
until the user clicks on the list subform.

Here's the way I would do it, based on the idea that the list must be
presented in a subform. As I said above, if a list box can be used
instead, the whole thing is simpler, so get back to me on that.

Place both subforms on the main form. Verify the names of the subform
controls -- for now, I'll assume that the name of the details subform
control will be "f_PrimaryEntry", the same as the form object it will
display. Set the Visible property of the subform *control* to False.

Put a text box somewhere on the main form, and set its Visible property
to False. Set its ControlSource property to

=[BU_CJ].[Form]![Budget Unit]

Set the name of this text box to "txtBudgetUnit". This text box, though
unseen by the user because it isn't visible, will reflect whatever is in
the [Budget Unit] text box on the subform named "BU_CJ". Check the name
of that subform control, by the way, to make sure it's right. Youi're
the one who gave me that name for the list subform.

For the f_PrimaryEntry subform control, set the Link Master Fields
property to

txtBudgetUnit

Set the Link Child Fields property to

UC_grp_name

That's all you need to do to get the details subform (f_PrimaryEntry) to
track the current record in the list subform (BU_CJ). The only thing
left to do is make the details subform visible in the Click event of the
BU_CJ *form object*. Click on the subform control for BU_CJ, then click
on it again to select the form object itself, then bring up the property
sheet for that form, go to the Event tab, choose [Event Procedure] for
the On Click property, then click the build button at the end of the
line and build this event procedure:

Private Sub Form_Click()

Me.Parent!f_PrimaryEntry.Visible = True

End Sub

And that ought to do it (barring any name mixups).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tracy]

Dirk, It's looking pretty good... The problem now is, I thought I did
everything right, but it's not bringing in any records in the detail form -
f_PrimaryEntry. What could be wrong??? Thanks. I'll be looking for your
answer.

Dirk, Yes the details subform (f_PrimaryEntry) is the only one that is
editable. And the first subform, that I can't use a list box for is where
people should click on the record selector of their choice from the text box
and open up the f_PrimaryEntry subform. I just wanted to get back with you

:
If you don't want people editing the subform, but just want to use
it to
display a list of choices, would it maybe be better to use a list box
instead?

Yes, most definitely - however, the request is not to
have a list box so I had to do it this way. The form that will open
up by clicking on the data selected in the subform will be editable.

The editability of the detail form (or subform) isn't dependent on
whether the summary list is presented in a list box or a subform. It
would be simpler to do this with a list box, because a list box has a
value, while a subform doesn't; however, if you have been told you
can't use a list box, a subform can be made to work. On the other hand,
if the only requirement is that the *detail* subform must be editable,
let me know so we can do it the easy way.

Do you mean, there are several records displayed by the subform, and
for
one of them a text box will be displaying the value "Section 10:
Superior Courts"?

Do you mean that "Budget Unit" is the name of the text box, but
"UC_grp_name" is the name of the field to which the text box is
bound?

Yes, that's right.

So, "CriminalJusticeYear" is the name of the main form that contains
this subform, the subform we're talking about above?

Are we talking about two subforms on the main form, then?

YES, I
would like it to display to the right of the first subform after a
particular record is clicked on.

Okay. That's a different arrangement from my original understanding,
but no big deal.

Subforms are
always open, so it's not a question of *opening* one, but maybe
you're
asking how to get a second "details" subform to show the information
related to the item clicked in the first, "list" subform. That
would be
easy enough to do.

Okay, but is there a way to do that without the
f_PrimaryEntry showing until a record was selected?

I think so. It should just be a matter of hiding the details subform
until the user clicks on the list subform.

Here's the way I would do it, based on the idea that the list must be
presented in a subform. As I said above, if a list box can be used
instead, the whole thing is simpler, so get back to me on that.

Place both subforms on the main form. Verify the names of the subform
controls -- for now, I'll assume that the name of the details subform
control will be "f_PrimaryEntry", the same as the form object it will
display. Set the Visible property of the subform *control* to False.

Put a text box somewhere on the main form, and set its Visible property
to False. Set its ControlSource property to

=[BU_CJ].[Form]![Budget Unit]

Set the name of this text box to "txtBudgetUnit". This text box, though
unseen by the user because it isn't visible, will reflect whatever is in
the [Budget Unit] text box on the subform named "BU_CJ". Check the name
of that subform control, by the way, to make sure it's right. Youi're
the one who gave me that name for the list subform.

For the f_PrimaryEntry subform control, set the Link Master Fields
property to

txtBudgetUnit

Set the Link Child Fields property to

UC_grp_name

That's all you need to do to get the details subform (f_PrimaryEntry) to
track the current record in the list subform (BU_CJ). The only thing
left to do is make the details subform visible in the Click event of the
BU_CJ *form object*. Click on the subform control for BU_CJ, then click
on it again to select the form object itself, then bring up the property
sheet for that form, go to the Event tab, choose [Event Procedure] for
the On Click property, then click the build button at the end of the
line and build this event procedure:

Private Sub Form_Click()

Me.Parent!f_PrimaryEntry.Visible = True

End Sub

And that ought to do it (barring any name mixups).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, It's looking pretty good... The problem now is, I thought I did
everything right, but it's not bringing in any records in the detail
form - f_PrimaryEntry. What could be wrong??? Thanks. I'll be
looking for your answer.

I can think of two main areas to investigate. The first is to make sure
that all the linking properties and controls are set up properly. You
may want to "unhide" the txtBudgetUnit control on the main form, so you
can see whether it correctly shows the budget unit that is current on
the BU_CJ subform. Also, make sure that the Link Master Fields and Link
Child Fields properties on the f_PrimaryEntry subform control are set as
I instructed.

The second area to investigate is that the subforms' underlying
recordsources can actually be related by these fields. Please post the
details of the recordsources of each of the subforms. If the
recordsource is a table, list the fields; if it's a query, post the SQL
of the query.

 

[Tracy]

Dirk, You are a incredible! Thank you so much for your help! It's working
and I love it! THANKS!!!

 

[Dirk Goldgar]

Dirk, You are a incredible! Thank you so much for your help! It's
working and I love it! THANKS!!!

I guess you found the problem, then. <g> You're welcome.

 

[Tracy]

Yes, it was exactly what you said - a problem with the text field - one of
the brackets had came off - go figure and then also, for whatever reason,
though I had the master and child links worded correcly, it wasn't
recognizing it so I copied from the other subform and pasted it and it worked!

Thanks again though. I appreciate you!