Criteria for opening form from a different subform

Form: frmCamPledgeList
Subform: frmCamPledgeListSub
Double click on field in subform to open
Form: frmViewDonor

DoCmd.OpenForm "frmViewDonor", , , "tblContributors.[ContributorID] =" &
Forms!frmCamPledgeList!frmCamPledgeListSub.idContr ibutorID - gives me a 438
error, object doesn't support this property or method; and

DoCmd.OpenForm "frmViewDonor", , , "tblContributors.[ContributorID] =" &
Forms!frmCamPledgeList!frmCamPledgeListSub.Form.id ContributorID - gives me
the filtered result on the first record and a blank form on all subsequent
records.

Does anyone have an idea of what is wrong?

Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Hi Scott,

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around
the subform object.
*black squares in the corners and the middle of each size --
resizing handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it
directly from the database window and it has the same
properties of the main form. It is only called a subform
because of the way it is being used.

To summarize, when you are in the design view of the main
form, the first click on the subform is the subform control
-- you will see the handles around the edges -- and the
second click gets you INTO it -- you will see a black square
where the rulers intersect in the upper left of the "form"
you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname -- the subform control
me.subform.controlname.form -- the form inside the subform
control

Personally, I edit subforms directly, not from inside the
main form -- I have had trouble with Access putting property
changes in the wrong place for RowSources and RecordSource.
Since it happens there occassionally, for major changes, I
go to the design view of the "sub"form directly from the
databse window when the main form is closed.
the subform itself is an independent form -- you can open it
directly from the database window and it has the same
properties of the main form. It is only called a subform
because of the way it is being used.

To summarize, when you are in the design view of the main
form, the first click on the subform is the subform control
-- you will see the handles around the edges -- and the
second click gets you INTO it -- you will see a black square
where the rulers intersect in the upper left of the "form"
you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname -- the subform control
me.subform.controlname.form -- the form inside the subform
control

Personally, I edit subforms directly, not from inside the
main form -- I have had trouble with Access putting property
changes in the wrong place for RowSources and RecordSource.
Since it happens there occassionally, for major changes, I
go to the design view of the "sub"form directly from the
databse window when the main form is closed.

~~~

that said, it looks like you may need this:

Forms!frmCamPledgeList!frmCamPledgeListSub.form.id ContributorID

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Form: frmCamPledgeList
Subform: frmCamPledgeListSub
Double click on field in subform to open
Form: frmViewDonor

DoCmd.OpenForm "frmViewDonor", , , "tblContributors.[ContributorID] =" &
Forms!frmCamPledgeList!frmCamPledgeListSub.idContr ibutorID - gives me a 438
error, object doesn't support this property or method; and

DoCmd.OpenForm "frmViewDonor", , , "tblContributors.[ContributorID] =" &
Forms!frmCamPledgeList!frmCamPledgeListSub.Form.id ContributorID - gives me
the filtered result on the first record and a blank form on all subsequent
records.

Does anyone have an idea of what is wrong?

Thanks,
Scott

Hi,

Thanks for the primer on subforms. I also edit them directly as forms. I took
that code from a reply to an earlier post to see if it would help. I put your
code in and I still get a correct form for the first record and blanks for
the rest.

Scott

Crystal (strive4peace) wrote:
Hi Scott,

*** Difference between Subform Control and Subform ***

The first click on a subform control puts handles* around
the subform object.
*black squares in the corners and the middle of each size --
resizing handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it
directly from the database window and it has the same
properties of the main form. It is only called a subform
because of the way it is being used.

To summarize, when you are in the design view of the main
form, the first click on the subform is the subform control
-- you will see the handles around the edges -- and the
second click gets you INTO it -- you will see a black square
where the rulers intersect in the upper left of the "form"
you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname -- the subform control
me.subform.controlname.form -- the form inside the subform
control

Personally, I edit subforms directly, not from inside the
main form -- I have had trouble with Access putting property
changes in the wrong place for RowSources and RecordSource.
Since it happens there occassionally, for major changes, I
go to the design view of the "sub"form directly from the
databse window when the main form is closed.
the subform itself is an independent form -- you can open it
directly from the database window and it has the same
properties of the main form. It is only called a subform
because of the way it is being used.

To summarize, when you are in the design view of the main
form, the first click on the subform is the subform control
-- you will see the handles around the edges -- and the
second click gets you INTO it -- you will see a black square
where the rulers intersect in the upper left of the "form"
you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname -- the subform control
me.subform.controlname.form -- the form inside the subform
control

Personally, I edit subforms directly, not from inside the
main form -- I have had trouble with Access putting property
changes in the wrong place for RowSources and RecordSource.
Since it happens there occassionally, for major changes, I
go to the design view of the "sub"form directly from the
databse window when the main form is closed.

~~~

that said, it looks like you may need this:

Forms!frmCamPledgeList!frmCamPledgeListSub.form.i dContributorID

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Form: frmCamPledgeList
Subform: frmCamPledgeListSub
[quoted text clipped - 14 lines]
Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Hi Scott,

perhaps the problem is something else ... what is the
RecordSource for the form you are opening. If it is a
query, then post the SQL statement

Is the field you are using as a filter ON the form in a control?

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Hi,

Thanks for the primer on subforms. I also edit them directly as forms. I took
that code from a reply to an earlier post to see if it would help. I put your
code in and I still get a correct form for the first record and blanks for
the rest.

Scott

Hello again,

Yes, ContirbutorID field is on form as hidden field in the detail section.

The source for the main form is a table (tblDonationCampaignSetup). The
source for the subform is a query. Here is the SQL for the query:
SELECT tblContributors.*, tblPledges.PledgeID, tblPledges.Campaign_ID,
tblPledges.Contribution_CategoryID, tblPledges.PaymentTypeID, tblPledges.
DatePledged, tblPledges.AmountPledged, tblPledges.Paid, tblPledges.
PaymentDate
FROM tblContributors INNER JOIN tblPledges ON tblContributors.ContributorID =
tblPledges.ContributorID;

Thanks,
Scott


Crystal (strive4peace) wrote:
Hi Scott,

perhaps the problem is something else ... what is the
RecordSource for the form you are opening. If it is a
query, then post the SQL statement

Is the field you are using as a filter ON the form in a control?

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Hi,

[quoted text clipped - 4 lines]

Scott

--
Message posted via http://www.accessmonster.com

On Sun, 29 Nov 2009 04:48:15 GMT, "Scott_Brasted via AccessMonster.com"
u56211@uwe wrote:

Hello again,

Yes, ContirbutorID field is on form as hidden field in the detail section.

The source for the main form is a table (tblDonationCampaignSetup). The
source for the subform is a query. Here is the SQL for the query:
SELECT tblContributors.*, tblPledges.PledgeID, tblPledges.Campaign_ID,
tblPledges.Contribution_CategoryID, tblPledges.PaymentTypeID, tblPledges.
DatePledged, tblPledges.AmountPledged, tblPledges.Paid, tblPledges.
PaymentDate
FROM tblContributors INNER JOIN tblPledges ON tblContributors.ContributorID =
tblPledges.ContributorID;

This query will return no records for contributors who have no Pledges.

Ordinarily a Form or Subform would be based on a single-table query. I'm
guessing that tblContributors is related one to many to tblPledges; how would
your form handle the case where a contributor has many pledges, or no pledges
at all?
--

John W. Vinson [MVP]

Hi Scott,

Perhaps the problem is that the RecordSource of your form
uses tables that are in a 1:many relationship -- it is best
to base each form and subform on only one table.

Does tblDonationCampaignSetup have ContributorID? Is that
what you are using for LinkMasterFields and LinkChildFields?

Is ContributorID on the subform as well?

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Hello again,

Yes, ContirbutorID field is on form as hidden field in the detail section.

The source for the main form is a table (tblDonationCampaignSetup). The
source for the subform is a query. Here is the SQL for the query:
SELECT tblContributors.*, tblPledges.PledgeID, tblPledges.Campaign_ID,
tblPledges.Contribution_CategoryID, tblPledges.PaymentTypeID, tblPledges.
DatePledged, tblPledges.AmountPledged, tblPledges.Paid, tblPledges.
PaymentDate
FROM tblContributors INNER JOIN tblPledges ON tblContributors.ContributorID =
tblPledges.ContributorID;

Thanks,
Scott

Good morning Crystal,

Form recordsource = tblDonationCampaignSetup. It is is in a one 2 many
relationship with tblPledges and tblPledges is in a one 2 many with
tblContributors. tblPledges and tblContributors make up qryContirbutorPledges
which is the rcordsource for the subform.

tblDonationCampaignSetup linked to tblPledges through SetupID (primary key
one) to CampaignID (foreign key many). tblPledges linked to tblContributors
through PledgeID (primary key many) to ContributorID (foreign key one).

The answers a
1 Yes
2 SourceObject: frmCamPledgeListSub
Link Child Field: Campaign_ID
Link Master Field: SetupID
3 Yes

Thanks,
Scott

Crystal (strive4peace) wrote:
Hi Scott,

Perhaps the problem is that the RecordSource of your form
uses tables that are in a 1:many relationship -- it is best
to base each form and subform on only one table.

Does tblDonationCampaignSetup have ContributorID? Is that
what you are using for LinkMasterFields and LinkChildFields?

Is ContributorID on the subform as well?

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Hello again,

[quoted text clipped - 11 lines]
Thanks,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Hell John,

Your surmise is correct. The query returns names and pledges for contributors
to campaigns. No pledge, no name. All I want thecode to do is open a form
with donor details when I double click the donor name in the subform. See my
next answer to Crystal for detals on structure.

Best,
Sott

John W. Vinson wrote:
Hello again,

[quoted text clipped - 8 lines]
FROM tblContributors INNER JOIN tblPledges ON tblContributors.ContributorID =
tblPledges.ContributorID;

This query will return no records for contributors who have no Pledges.

Ordinarily a Form or Subform would be based on a single-table query. I'm
guessing that tblContributors is related one to many to tblPledges; how would
your form handle the case where a contributor has many pledges, or no pledges
at all?

--
Message posted via http://www.accessmonster.com

Hi Scott,

I think John (Hi John!) hit the nail on the head when he
said that blank records are probably due to contributors
having no pledge info.

I recommend you redesign your forms to use just ONE table
for each form. This will alleviate a lot of problems

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Good morning Crystal,

Form recordsource = tblDonationCampaignSetup. It is is in a one 2 many
relationship with tblPledges and tblPledges is in a one 2 many with
tblContributors. tblPledges and tblContributors make up qryContirbutorPledges
which is the rcordsource for the subform.

tblDonationCampaignSetup linked to tblPledges through SetupID (primary key
one) to CampaignID (foreign key many). tblPledges linked to tblContributors
through PledgeID (primary key many) to ContributorID (foreign key one).

The answers a
1 Yes
2 SourceObject: frmCamPledgeListSub
Link Child Field: Campaign_ID
Link Master Field: SetupID
3 Yes

Thanks,
Scott

Crystal (strive4peace) wrote:
Hi Scott,

Perhaps the problem is that the RecordSource of your form
uses tables that are in a 1:many relationship -- it is best
to base each form and subform on only one table.

Does tblDonationCampaignSetup have ContributorID? Is that
what you are using for LinkMasterFields and LinkChildFields?

Is ContributorID on the subform as well?

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Hello again,

[quoted text clipped - 11 lines]
Thanks,
Scott