1 Main for 2 Subforms

[channell]

I truly Apologize for this, but I can't find an answer for this at all.

Ok, I posted a similar question, but I am not sure I explained myself enough
in order to get the correct answer.

I have three forms I am working with. I have a main form with two subforms
in it. I have a combo box on the main form that will search through
employees and it pulls up all their records on the subforms. The two
subforms are based off the same information but not the same query (I may
need to do that though, please let me know). Subform (a) is a more detailed
form with all the information I need. It is in "single form view". Subform
(b) is an abbreviated version of Subform (a) and is in "continuous forms
view". They are on separate tabs. I want to place a button, or put code in
one of the text boxes (whichever is easier, but preferably a button) on each
record of subform (b) that when selected, returns that matching record on
subform (a).

I am using the code:

Private Sub Command72_Click()
DoCmd.OpenForm "f6135MAINENTRYEASY", , , "[6135 ID] = " & Me.[6135 ID] &
""
End Sub

But what that does is open another window, but it does pull up the correct
record. I need it NOT to open a separate record, but only pull up the
correct record. That's all.

Thank you very much in advance for any help that you assist me with. I
greatly appreciate it.

 

[Dirk Goldgar]

I truly Apologize for this, but I can't find an answer for this at all.

Ok, I posted a similar question, but I am not sure I explained myself
enough
in order to get the correct answer.

I have three forms I am working with. I have a main form with two
subforms
in it. I have a combo box on the main form that will search through
employees and it pulls up all their records on the subforms. The two
subforms are based off the same information but not the same query (I may
need to do that though, please let me know). Subform (a) is a more
detailed
form with all the information I need. It is in "single form view".
Subform
(b) is an abbreviated version of Subform (a) and is in "continuous forms
view". They are on separate tabs. I want to place a button, or put code
in
one of the text boxes (whichever is easier, but preferably a button) on
each
record of subform (b) that when selected, returns that matching record on
subform (a).

I am using the code:

Private Sub Command72_Click()
DoCmd.OpenForm "f6135MAINENTRYEASY", , , "[6135 ID] = " & Me.[6135 ID]
&
""
End Sub

But what that does is open another window, but it does pull up the correct
record. I need it NOT to open a separate record, but only pull up the
correct record. That's all.

Thank you very much in advance for any help that you assist me with. I
greatly appreciate it.

You don't actually need any code at all. You can set up the form and
subforms so that the detail form *always* shows the record that corresponds
to the selected record on the continuous form. It's just a matter of
setting up appropriate Link Master/Child Fields.

Is "6135 ID" the name of the primary key that both the subforms are based
on? What are the names of the subform controls (on the main form) that
display the subforms?

 

[channell]

6135 ID is the Primary Key for both subforms. I am not sure what you mean by
the buform controls on the main form that display the subforms, but they are
on tabs. You can call it tab 1 and tab 2.

what do I need to do to make it work? Thanks soooo much!

I truly Apologize for this, but I can't find an answer for this at all.

Ok, I posted a similar question, but I am not sure I explained myself
enough
in order to get the correct answer.

I have three forms I am working with. I have a main form with two
subforms
in it. I have a combo box on the main form that will search through
employees and it pulls up all their records on the subforms. The two
subforms are based off the same information but not the same query (I may
need to do that though, please let me know). Subform (a) is a more
detailed
form with all the information I need. It is in "single form view".
Subform
(b) is an abbreviated version of Subform (a) and is in "continuous forms
view". They are on separate tabs. I want to place a button, or put code
in
one of the text boxes (whichever is easier, but preferably a button) on
each
record of subform (b) that when selected, returns that matching record on
subform (a).

I am using the code:

Private Sub Command72_Click()
DoCmd.OpenForm "f6135MAINENTRYEASY", , , "[6135 ID] = " & Me.[6135 ID]
&
""
End Sub

But what that does is open another window, but it does pull up the correct
record. I need it NOT to open a separate record, but only pull up the
correct record. That's all.

Thank you very much in advance for any help that you assist me with. I
greatly appreciate it.

You don't actually need any code at all. You can set up the form and
subforms so that the detail form *always* shows the record that corresponds
to the selected record on the continuous form. It's just a matter of
setting up appropriate Link Master/Child Fields.

Is "6135 ID" the name of the primary key that both the subforms are based
on? What are the names of the subform controls (on the main form) that
display the subforms?


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

(please reply to the newsgroup)

 

[Dirk Goldgar]

6135 ID is the Primary Key for both subforms. I am not sure what you mean
by
the buform controls on the main form that display the subforms, but they
are
on tabs. You can call it tab 1 and tab 2.

I need to know the actual names of the subform controls, because to do this
right we need to make a controlsource reference that involves the name of
the subform control. For this purpose, the fact that the subforms are on
pages of a tab control is irrelevant.

This is what I mean by "the name of the subform control". There are three
form object here: the main form, and each of the two subforms. On the main
form, on the separate pages of the tab control, there are subform "window"
controls that display the subforms. Each of these "subform controls" is a
control, just like a text box or a combo box, but instead of displaying a
single field, a subform control displays a form. That form is the subform
control's "Source Object". The properties of a subform control specify how
it is linked to the main form.

It is often the case that a subform control will have the same name as the
form that is its Source Object. However, depending on how it was created,
it may have a completely different name. What we want to do is build a
reference to a control on one of the subforms, and such a reference must use
the name of the subform control, not the name of its Source Object.

That's why I asked for the names of the subform controls. Please open the
main form in design view and check the names of th tow subform controls.
Post back with their names, and indicate which one displays the continuous
form and which the detail form.

 

[channell]

Dirk, I do greatly appreciate your help very much. Please stick with me
until I get it working. I appreciate your patience.

Here is what I hope you are looking for:

This is the subform that is in single form view is as follows:

Name: f6135MAINENTRYEASY
Source Object: f6135MAINENTRYEASY
Link Child Fields: Employee ID
Link Master Fields: Employee ID


Next is the form in continuous forms view:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: Employee ID

Thanks again Dirk! If you need more info, please let me know.

-Scott Channell

 

[BruceM]

Does the main form have a record source? If so, what is it, and how does it
relate to the records in the subforms?

 

[Dirk Goldgar]

Dirk, I do greatly appreciate your help very much. Please stick with me
until I get it working. I appreciate your patience.

Here is what I hope you are looking for:

This is the subform that is in single form view is as follows:

Name: f6135MAINENTRYEASY
Source Object: f6135MAINENTRYEASY
Link Child Fields: Employee ID
Link Master Fields: Employee ID


Next is the form in continuous forms view:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: Employee ID

Very good, that's exactly what I needed. And the primary key for the table
the subforms are based on is "6135 ID", as you said before. I notice an
apparent misspelling in the name of the f6135QUICKVEIW subform control;
I'll assume that's the way it really is spelled, and you can fix the
references below if I'm wrong.

Here's what to do.

On the main form (not on either of the subforms), place a text box named
"txtSubformLink". Set these properties of the text box:

Visible: No
Control Source: =[f6135QUICKVEIW].[Form]![6135 ID]

With its Visible property set to No, the text box will be hidden from the
user. You can make it as small as you want, and put it anywhere on the main
form you like.

Now change the Link Master Fields property of the single-view subform,
f6135MAINENTRYEASY, to this:

txtSubformLink

At this point, f6135MAINENTRYEASY will always show the same record that is
currently selected on f6135QUICKVEIW.

If you don't intend to use f6135MAINENTRYEASY to add records, you're done --
though you will probably want to set the form's AllowAdditions property to
No, so that it doesn't offer a blank record to fill in.

If you do want to use the single-form subfom, f6135MAINENTRYEASY, to add new
records, then you will have to make some changes to the properties and add a
little bit of code. Also, if you want to allow that subform to delete
records, and have the deletions reflected immediately on the continuous
subform, you'll need a tiny bit of code.

I can help you with those things, but first get the forms and subforms set
up as I described above and make sure that it works for the purpose of
viewing and editing records.

 

[channell]

Dirk, I am experiencing something very odd with this. Initially, after I
have put in everything you instructed, I thought it did not work. Now, after
clicking on some records in the continuous forms Subform, as I click to the
tab witht he single form view, I am seeing some of the records displayed, but
even records that arne't associated with the particular person. I think I am
alomst there, but I am missing something.

For Example, I type in "Brent Smith". On the continuous form Subform, all
his records show up. Now, when I click on an individual record and tab over
to the single record Subform, no record is displayed. Other records I chose
will return a record not even associated with "Brent Smith" What do you think?

Dirk, I do greatly appreciate your help very much. Please stick with me
until I get it working. I appreciate your patience.

Here is what I hope you are looking for:

This is the subform that is in single form view is as follows:

Name: f6135MAINENTRYEASY
Source Object: f6135MAINENTRYEASY
Link Child Fields: Employee ID
Link Master Fields: Employee ID


Next is the form in continuous forms view:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: Employee ID

Very good, that's exactly what I needed. And the primary key for the table
the subforms are based on is "6135 ID", as you said before. I notice an
apparent misspelling in the name of the f6135QUICKVEIW subform control;
I'll assume that's the way it really is spelled, and you can fix the
references below if I'm wrong.

Here's what to do.

On the main form (not on either of the subforms), place a text box named
"txtSubformLink". Set these properties of the text box:

Visible: No
Control Source: =[f6135QUICKVEIW].[Form]![6135 ID]

With its Visible property set to No, the text box will be hidden from the
user. You can make it as small as you want, and put it anywhere on the main
form you like.

Now change the Link Master Fields property of the single-view subform,
f6135MAINENTRYEASY, to this:

txtSubformLink

At this point, f6135MAINENTRYEASY will always show the same record that is
currently selected on f6135QUICKVEIW.

If you don't intend to use f6135MAINENTRYEASY to add records, you're done --
though you will probably want to set the form's AllowAdditions property to
No, so that it doesn't offer a blank record to fill in.

If you do want to use the single-form subfom, f6135MAINENTRYEASY, to add new
records, then you will have to make some changes to the properties and add a
little bit of code. Also, if you want to allow that subform to delete
records, and have the deletions reflected immediately on the continuous
subform, you'll need a tiny bit of code.

I can help you with those things, but first get the forms and subforms set
up as I described above and make sure that it works for the purpose of
viewing and editing records.

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

(please reply to the newsgroup)

 

[BruceM]

PMFJI. I'm not quite following all of this, but as I understand the PK of
the subforms is 6135ID, yet the linking field is EmployeeID. As I
understand the main form is unbound, and is used only to select an employee
from an unbound combo box that presumably has EmployeeID as the PK. The
continuous subform shows all records for the selected employee, which I
expect would be records with EmployeeID as the FK. I would have thought
there would be a link between the combo box and the continuous subform.
However, the OP has stated that EmployeeID is the linking field, which I
would think means the main form is bound. If so, to what recordset?

I would have expected something like this:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: MainFormName!ComboBoxName

And then as you described for the single view form.

Please ignore if I have missed the boat here. It wouldn't be the first
time.

Dirk, I do greatly appreciate your help very much. Please stick with me
until I get it working. I appreciate your patience.

Here is what I hope you are looking for:

This is the subform that is in single form view is as follows:

Name: f6135MAINENTRYEASY
Source Object: f6135MAINENTRYEASY
Link Child Fields: Employee ID
Link Master Fields: Employee ID


Next is the form in continuous forms view:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: Employee ID

Very good, that's exactly what I needed. And the primary key for the
table the subforms are based on is "6135 ID", as you said before. I
notice an apparent misspelling in the name of the f6135QUICKVEIW subform
control; I'll assume that's the way it really is spelled, and you can fix
the references below if I'm wrong.

Here's what to do.

On the main form (not on either of the subforms), place a text box named
"txtSubformLink". Set these properties of the text box:

Visible: No
Control Source: =[f6135QUICKVEIW].[Form]![6135 ID]

With its Visible property set to No, the text box will be hidden from the
user. You can make it as small as you want, and put it anywhere on the
main form you like.

Now change the Link Master Fields property of the single-view subform,
f6135MAINENTRYEASY, to this:

txtSubformLink

At this point, f6135MAINENTRYEASY will always show the same record that is
currently selected on f6135QUICKVEIW.

If you don't intend to use f6135MAINENTRYEASY to add records, you're
done -- though you will probably want to set the form's AllowAdditions
property to No, so that it doesn't offer a blank record to fill in.

If you do want to use the single-form subfom, f6135MAINENTRYEASY, to add
new records, then you will have to make some changes to the properties and
add a little bit of code. Also, if you want to allow that subform to
delete records, and have the deletions reflected immediately on the
continuous subform, you'll need a tiny bit of code.

I can help you with those things, but first get the forms and subforms set
up as I described above and make sure that it works for the purpose of
viewing and editing records.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, I am experiencing something very odd with this. Initially, after I
have put in everything you instructed, I thought it did not work. Now,
after
clicking on some records in the continuous forms Subform, as I click to
the
tab witht he single form view, I am seeing some of the records displayed,
but
even records that arne't associated with the particular person. I think I
am
alomst there, but I am missing something.

For Example, I type in "Brent Smith". On the continuous form Subform, all
his records show up. Now, when I click on an individual record and tab
over
to the single record Subform, no record is displayed. Other records I
chose
will return a record not even associated with "Brent Smith" What do you
think?

I should also have written: change the Link Child Fields property of
f6135MAINENTRYEASY to

[6135 ID]

I'm not completely sure about the brackets; you may need to leave them off.

 

[Dirk Goldgar]

PMFJI. I'm not quite following all of this, but as I understand the PK of
the subforms is 6135ID, yet the linking field is EmployeeID. As I
understand the main form is unbound, and is used only to select an
employee from an unbound combo box that presumably has EmployeeID as the
PK. The continuous subform shows all records for the selected employee,
which I expect would be records with EmployeeID as the FK. I would have
thought there would be a link between the combo box and the continuous
subform. However, the OP has stated that EmployeeID is the linking field,
which I would think means the main form is bound. If so, to what
recordset?

I'm assuming that the combo box in question is either named EmployeeID, or
is bound to a field named EmployeeID. The subforms are currently working to
display records for the chosen employee, Scott says, so I think either one
or the other of those cases must be true.

I would have expected something like this:

Name: f6135QUICKVEIW
Source Object: f6135QUICKVIEW
Link Child Fields: Employee ID
Link Master Fields: MainFormName!ComboBoxName

Since the subform is on the main form, there's no need to reference the main
form in the Link Master Fields -- that's implied. "ComboBoxName" is
presumably "EmployeeID".

 

[BruceM]

Ah. It seems not to have occurred to me that somebody would name a combo
box other than with the convention I would use.

Thanks for the reply.

 

[channell]

Dirk, after some deliberation, I finally got it to work... I don't know what
the heck I was doing, but I have come to the conclusion that it is just
massive user error, and I apologize for my lack of knowledge. With the help
I am receiving, I am becoming better. Thanks. Now, one more question
regarding this. Since these subforms are on different tabs, I was wondering
how I could possibly, when I click on a record (on the continuous form), have
it automatically got to the other tab and display the record in the single
form. Thanks again

Dirk, I am experiencing something very odd with this. Initially, after I
have put in everything you instructed, I thought it did not work. Now,
after
clicking on some records in the continuous forms Subform, as I click to
the
tab witht he single form view, I am seeing some of the records displayed,
but
even records that arne't associated with the particular person. I think I
am
alomst there, but I am missing something.

For Example, I type in "Brent Smith". On the continuous form Subform, all
his records show up. Now, when I click on an individual record and tab
over
to the single record Subform, no record is displayed. Other records I
chose
will return a record not even associated with "Brent Smith" What do you
think?

I should also have written: change the Link Child Fields property of
f6135MAINENTRYEASY to

[6135 ID]

I'm not completely sure about the brackets; you may need to leave them off.

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

(please reply to the newsgroup)

 

[channell]

Also, I DO want the ability to add and delete records from the single view
form.

Thanks

Dirk, I am experiencing something very odd with this. Initially, after I
have put in everything you instructed, I thought it did not work. Now,
after
clicking on some records in the continuous forms Subform, as I click to
the
tab witht he single form view, I am seeing some of the records displayed,
but
even records that arne't associated with the particular person. I think I
am
alomst there, but I am missing something.

For Example, I type in "Brent Smith". On the continuous form Subform, all
his records show up. Now, when I click on an individual record and tab
over
to the single record Subform, no record is displayed. Other records I
chose
will return a record not even associated with "Brent Smith" What do you
think?

I should also have written: change the Link Child Fields property of
f6135MAINENTRYEASY to

[6135 ID]

I'm not completely sure about the brackets; you may need to leave them off.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, after some deliberation, I finally got it to work... I don't know
what
the heck I was doing, but I have come to the conclusion that it is just
massive user error, and I apologize for my lack of knowledge. With the
help
I am receiving, I am becoming better. Thanks.

You're welcome, and I apologize for my own oversight. Now, moving one ...

Now, one more question
regarding this. Since these subforms are on different tabs, I was
wondering
how I could possibly, when I click on a record (on the continuous form),
have
it automatically got to the other tab and display the record in the single
form.

There are several ways to change pages on a tab control. The way I usually
prefer is to set the value of the tab control to the page index of the page
I want to change to -- the first page has index 0, the second has index 1,
and so on. Or you can set the focus to a control on the page you want to
move to. In this case, since you particularly want to activate the detail
subform, I suggest you set the focus to it.

I would probably use a DblClick (double-click) event to trigger the action,
to make sure the user really wants to change views. I'll give instructions
for using the DblClick event, and we can adapt them if you really want to
use the Click event instead. The only complication is that the user might
double-click on various different form elements, and want to switch to the
detail view in all cases. To simplify this, we'll create a helper function
in the subform's module, and call that function from the DblClick event of
various form elements.

Open form f6135QUICKVIEW in design view. I'm talking about the form object
here, not the subform control. Now open its code module and add this
function outside of any existing procedure:

'----- start of code -----
Public Function DetailView()

Me.Parent!f6135MAINENTRYEASY.SetFocus

End Function
'----- end of suggested code -----

Now switch back to the form's design view and enter this in the On DblClick
event property line of the property sheet for the form itself, the Detail
section, and every control in the Detail section:

=DetailView()

Save and close the form. Now open the main form and try it out.
Double-clicking pretty much anywhere on the f6135QUICKVIEW subformn ought to
switch the focus to the detail form, displaying the record you
double-clicked. If it doesn't, or if you get an error, let me know.

 

[Dirk Goldgar]

Also, I DO want the ability to add and delete records from the single view
form.

If you want to add and delete records from the single-view form, you need to
add some code to requery the continuous form to reflect your changes. You'd
probably use the AfterInsert and AfterDelConfirm events of the single-view
form to do this. You probably also need to do something to position the
continuous form to record you just added. I haven't tested this, but the
code might look something like this:

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

Dim lngID As Long

lngID = Me![6135 ID]

With Me.Parent!f6135QUICKVIEW.Form
.Requery
.Recordset.FindFirst "[6135 ID] = " & lngID
End With

End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)

Me.Parent!f6135QUICKVIEW.Requery

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