Subforms question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 17 Nov 2005 22:04 GMT

Hello,

I have a main Form that is unbound, called frmContracts.
On frmContracts, I have 2 sub forms:

subfrmContracts - where it's record source is a Cross Tab Query
and
subfrmAmendments - where it's record source is a Table

So basically for each Contract (which is listed in subfrmContracts) there
can be many Amendments which are listed in subfrmAmendments.

What I would like to do, is when a user selects a Row in subfrmContracts for
the subfrmAmendments to automatically upadate with only the records for the
Contract select in subfrmContracts. They both have a Field called
ContractNumber (which is a Text field). I can not get this to work.

I tried in the subfrmContracts Current Event...

Private Sub Form_Current()

Form_subfrmAmendments.FilterOn = True
Form_subfrmAmendments.Filter = "ContractNumber = '" & txtContractNumber & "'"
Form_subfrmAmendments.Requery
End Sub

I also tried adding to the subfrmAmendments
Link Child Field = ContractNumber
Link Master Field = Forms!frmContracts!subfrmContracts!ContractNumber

I tried many other things and still cant get this to work.

Any help would be greatly aprreciated.

Thank you,
Jeff

Reply to this Message

tina - 18 Nov 2005 02:08 GMT

one way to synchronize two subforms, when the underlying tables have a
relationship, is to use an unbound textbox control on the main form as an
"intermediary". try this:

add an unbound textbox to frmContracts, i'll call it txtContractNumber.

in subfrmContracts, on the subform's Current event, add the following code,
as

Private Sub Form_Current()

Me.Parent!txtContractNumber = Me!ContractNumber

End Sub

set the following properties of the subfrmAmendments "container" subform
control, as

LinkChildFields: ContractNumber
LinkMasterFields: [txtContractNumber]

hth

> Hello,
>
[quoted text clipped - 32 lines]
> Thank you,
> Jeff

Reply to this Message

Jeff - 18 Nov 2005 02:56 GMT

Hi Tina,

Thank you for the response. However I tried excatly what you wrote & it
doesnt work, the subfrmAmendments always shows no records no matter what
Contract you click on in the subfrmContracts and I made sure to check some
that defintley have Amendments.

I also tried for the LinkMasterFields:
Forms!frmContracys![txtContractNumber]
which also doesnt work.

Could it have something to do with the fact that the subfrmContracts Record
Source is a Cross Tab Query rather than a Table ?

Thank you,
Jeff

> one way to synchronize two subforms, when the underlying tables have a
> relationship, is to use an unbound textbox control on the main form as an
[quoted text clipped - 58 lines]
> > Thank you,
> > Jeff

Reply to this Message

tina - 18 Nov 2005 03:44 GMT

> Could it have something to do with the fact that the subfrmContracts Record
> Source is a Cross Tab Query rather than a Table ?

hmm, i don't know. possibly. open subfrmContractsRecord in design view. on
the menu bar, click View | Field List. is ContractNumber listed there? if
not, is there another field in the list that contains the value of
ContractNumber? what is that field's name?

double check to make sure that the Current event code is running in
subfrmContracts, *not* in the main form's Current event.

is txtContractNumber visible in the main form? normally i would hide it, but
if you did, set its' Visible property back to Yes. then open the main form
in form view and try moving from record to record in subfrmContracts. do you
see a "current" value being assigned to txtContractNumber each time?

hth

> Hi Tina,
>
[quoted text clipped - 75 lines]
> > > Thank you,
> > > Jeff

Reply to this Message

Jeff - 18 Nov 2005 05:19 GMT

Hi Tina,

Thank you again for writing back. I figured it out. Dont ask me why but
the Text box must have gotten currupt or something. I deleted it & added a
new one & now everything works. Your way worked great.

Thnx again,
Jeff

> > Could it have something to do with the fact that the subfrmContracts
> Record
[quoted text clipped - 101 lines]
> > > > Thank you,
> > > > Jeff

Reply to this Message

tina - 18 Nov 2005 06:08 GMT

oh, good, glad you got it solved - you really had me stumped! :)

> Hi Tina,
>
[quoted text clipped - 110 lines]
> > > > > Thank you,
> > > > > Jeff

Reply to this Message