How to connect two subforms

[Zeeshan Iqbal via AccessMonster.com]

hi,
im trying to design a form with two subforms. subforms no.1 (orders) is connected to the main form by customer ID and i dont know how i can connect another subform (order details)to the first subform(orders), so that when i enter a customer id i get the orders related to that customer in subform no.1 and order details related to that order in subform no.2. please message me if some one knows how to do that

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...3a020421f80e27
*****************************************

[PC Datasheet]

Treat subform1 as a main form and make subform2 a subform of subform1. Then
click on Windows - Tile Vertically and click and drag subform1 onto your
main form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Zeeshan Iqbal via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:3e******************************@AccessMonste r.com...

hi,
im trying to design a form with two subforms. subforms no.1 (orders) is connected to the main form by customer ID and i dont know how i can connect
another subform (order details)to the first subform(orders), so that when i
enter a customer id i get the orders related to that customer in subform
no.1 and order details related to that order in subform no.2. please message
me if some one knows how to do that
*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...148c3a020421f8
0e27 *****************************************

[Jack MacDonald]

Create an invisible, unbound text box on the main form. Use the
OnCurrent event of the Orders subform to populate the textbox with the
value of the PrimaryKey of the Orders subform. Use the textbox as the
Link to your OrderDetail subform.

On Sun, 14 Nov 2004 21:54:14 GMT, "Zeeshan Iqbal via
AccessMonster.com" <fo***@AccessMonster.com> wrote:

hi,
im trying to design a form with two subforms. subforms no.1 (orders) is connected to the main form by customer ID and i dont know how i can connect another subform (order details)to the first subform(orders), so that when i enter a customer id i get the orders related to that customer in subform no.1 and order details related to that order in subform no.2. please message me if some one knows how to do that

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...3a020421f80e27
*****************************************

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

[Steve Jorgensen]

On Sun, 14 Nov 2004 21:54:14 GMT, "Zeeshan Iqbal via AccessMonster.com"
<fo***@AccessMonster.com> wrote:

hi,
im trying to design a form with two subforms. subforms no.1 (orders) is connected to the main form by customer ID and i dont know how i can connect another subform (order details)to the first subform(orders), so that when i enter a customer id i get the orders related to that customer in subform no.1 and order details related to that order in subform no.2. please message me if some one knows how to do that

*****************************************
* This message was posted via http://www.accessmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.accessmonster.com/Uwe/Abu...3a020421f80e27
*****************************************

First, if there's some way you can get your news interface to wrap text, that
would be really helpful. What most of us see is one veeryyyyyy looong line
with your entire question in it.

What your asking for is a pattern I use often because it's the only way to
display a one-to-many relationship on a form where both tables are shown as
continuous, multi-row. It's not hard to do, there's just a little trick to
making it work.

First, you need to know that it's possible to use a control name as the master
in the master/child link. It doesn't need to be a field in a table. That
means you can place an unbound control on the container form, and link the
second subform based on the name of that controls.

Next, you need some way to get the value of the control on the container form
to be the id of the current record in the first subform. You do that using
the OnCurrent event handler of the first subform, and copying the value of the
id to the field on the container (its parent).

There's one final issue, though, and that is that the subforms are initialized
-before- the parent, so the first time the Current event fires, it will fail.
You can use On Error Resume next to ignore the error, but then the id control
in the container won't be initialized until the user moves to a new record.

The way I get around this is that the OnOpen handler of the first subform
copies its RecordSource to a variable, and sets the Recordsource to a blank
string. The OnCurrent handler checks the length of RecordSource, and exists
without doing anything if it's zero. The subform also has a procedure the
container form can call to restore the RecordSoruce property from the
variable, and the container calls that method of the subform in its own OnOpen
handler. That causes the subform's OnCurrent to fire for the first row, that
sets the value of the Id control on the container, and that causes the correct
related records to appear in the child second subform.

[Albert D. Kallal]

>The way I get around this is that the OnOpen handler of the first subform
copies its RecordSource to a variable, and sets the Recordsource to a blank
string. The OnCurrent handler checks the length of RecordSource, and exists
without doing anything if it's zero

That is a interesting solution. I in the past usually just use a bolVar in
the sub-form

Then, in on current, I go:
if bolAllowOnCurrent = False then
exit sub
end if

I then in my main form, at the right time (say on-load) I go:

me.MySubForm.Form.SetCurrentOn

Of couse, in every sub-form, I have:

Public Function SetCurrentOn

bolAllowOnCurrent = True

end function.

Anyway...your approach is different and interesting....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

[Steve Jorgensen]

On Mon, 15 Nov 2004 08:03:32 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

The way I get around this is that the OnOpen handler of the first subform

copies its RecordSource to a variable, and sets the Recordsource to a blank
string. The OnCurrent handler checks the length of RecordSource, and exists
without doing anything if it's zero

That is a interesting solution. I in the past usually just use a bolVar in
the sub-form

Then, in on current, I go:
if bolAllowOnCurrent = False then
exit sub
end if

I then in my main form, at the right time (say on-load) I go:

me.MySubForm.Form.SetCurrentOn

Of couse, in every sub-form, I have:

Public Function SetCurrentOn

bolAllowOnCurrent = True

end function.

Anyway...your approach is different and interesting....

With your approach, I don't see how you get the first record ID since the
first Current event has already fired when SetCurrentOn is called. I use the
RecordSource property because setting it causes a Current even to fire. I
could just call Form_Current directly from the method to force it, but it
seems ugly to add new pathways to event vectors.

[rkc]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:rg********************************@4ax.com...

On Mon, 15 Nov 2004 08:03:32 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

Anyway...your approach is different and interesting....

With your approach, I don't see how you get the first record ID since the
first Current event has already fired when SetCurrentOn is called. I use

the RecordSource property because setting it causes a Current even to fire. I
could just call Form_Current directly from the method to force it, but it
seems ugly to add new pathways to event vectors.

What makes it not a new pathway to the Form_Current event?

[Steve Jorgensen]

On Mon, 15 Nov 2004 12:07:05 GMT, "rkc" <rk*@yabba.dabba.do.rochester.rr.bomb>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:rg********************************@4ax.com.. .

On Mon, 15 Nov 2004 08:03:32 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

>Anyway...your approach is different and interesting....

With your approach, I don't see how you get the first record ID since the
first Current event has already fired when SetCurrentOn is called. I use

the

RecordSource property because setting it causes a Current even to fire. I
could just call Form_Current directly from the method to force it, but it
seems ugly to add new pathways to event vectors.

What makes it not a new pathway to the Form_Current event?

I said Event Vector. I'm forcing the event to occur, not calling the event
handler when the event did not occur. In any case, I'm not saying it would be
wrong to do it the other way, but I was wondering if/how the id gets to the
container form when doing neither of these things.

[rkc]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ag********************************@4ax.com...

On Mon, 15 Nov 2004 12:07:05 GMT, "rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:rg********************************@4ax.com.. .

On Mon, 15 Nov 2004 08:03:32 GMT, "Albert D. Kallal"
<Pl*******************@msn.com> wrote:

>Anyway...your approach is different and interesting....

With your approach, I don't see how you get the first record ID since the first Current event has already fired when SetCurrentOn is called. I use

the

RecordSource property because setting it causes a Current even to fire.

I could just call Form_Current directly from the method to force it, but it seems ugly to add new pathways to event vectors.

What makes it not a new pathway to the Form_Current event?

I said Event Vector. I'm forcing the event to occur, not calling the

event handler when the event did not occur. In any case, I'm not saying it would be wrong to do it the other way, but I was wondering if/how the id gets to the container form when doing neither of these things.

To me a vector is an array, so I guess don't understand the term event
vector.

[Steve Jorgensen]

On Mon, 15 Nov 2004 22:07:30 GMT, "rkc" <rk*@yabba.dabba.do.rochester.rr.bomb>
wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ag********************************@4ax.com.. .

On Mon, 15 Nov 2004 12:07:05 GMT, "rkc"

<rk*@yabba.dabba.do.rochester.rr.bomb>

wrote:

>
>"Steve Jorgensen" <no****@nospam.nospam> wrote in message
>news:rg********************************@4ax.com.. .
>> On Mon, 15 Nov 2004 08:03:32 GMT, "Albert D. Kallal"
>> <Pl*******************@msn.com> wrote:
>
>> >Anyway...your approach is different and interesting....
>>
>> With your approach, I don't see how you get the first record ID sincethe >> first Current event has already fired when SetCurrentOn is called. Iuse >the
>> RecordSource property because setting it causes a Current even to fire.I >> could just call Form_Current directly from the method to force it, butit >> seems ugly to add new pathways to event vectors.
>
>What makes it not a new pathway to the Form_Current event?

I said Event Vector. I'm forcing the event to occur, not calling the

event

handler when the event did not occur. In any case, I'm not saying it

would be

wrong to do it the other way, but I was wondering if/how the id gets to

the

container form when doing neither of these things.

To me a vector is an array, so I guess don't understand the term event
vector.

Sorry - vector has multiple meanings. When talking about events, a vector is
what triggers the event.