Urgent Help Needed: Referencing a control on a subform's subform

[Guest]

I have a form (frmFindPerson) which originally referenced a control on a form
(NewEvent) and would open the NewEvent form filtering by PersonID (a number)
using the following code:

Private Sub cmdOpenByPerson_Click()
On Error GoTo Err_cmdOpenByPerson_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NewEvent"
stLinkCriteria = "[PersonID]=" & Me![PersonID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "FrmFindPerson"

Exit_cmdOpenByPerson_Click:
Exit Sub

Err_cmdOpenByPerson_Click:
MsgBox Err.Description
Resume Exit_cmdOpenByPerson_Click

End Sub


Due to a rather profound change to the design of the database, PersonID now
resides on a subform frmPerson which is a subform of FrmPersonEvent which is
a subform of NewEvent.

I'm going out for surgery in a couple of days and will be out for two months
and perhaps the stress of that is interfering with my syntax writing skills,
but this database has to be completed today or the sky will fall, birds will
lose the ability to fly, and all humanity will lose its hair (sorry, I'm
having a bit of a challenge coping).

If anyone would be kind enough to post the correct syntax for the link
criteria, I would truly be grateful.

TIA,
Trisha

 

[PC Datasheet]

Forms!NewEvent!FrmPersonEvent.Form!FrmPerson.Form!PersonID

Best of luck with your surgery!

 

[Guest]

Here ya go...

Private Sub cmdOpenByPerson_Click()
On Error GoTo Err_cmdOpenByPerson_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NewEvent"
stLinkCriteria = "frm.[New
Event]![FrmPersonEvent]![frmPerson].[PersonID]=" & Me![PersonID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "FrmFindPerson"

Exit_cmdOpenByPerson_Click:
Exit Sub

Err_cmdOpenByPerson_Click:
MsgBox Err.Description
Resume Exit_cmdOpenByPerson_Click

End Sub


Good luck!

-- Sandy

 

[Guest]

Thank you for your time and responses.

Both of these solutions result in a parameter prompt for the criteria.

I'm sure these are both very close, I'm just not quite there.

Any additional thoughts/suggestions will be gratefully accepted.

Thanks again,
Trisha

 

[PC Datasheet]

stLinkCriteria = "[PersonID]=" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID

The above presumes PersonID is a number data type. If it is a text data
type, use this:
stLinkCriteria = "[PersonID]= '" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID & "'"

Steve
PC Datasheet

 

[Guest]

Steve,

Thank you for persevering with my problem, I appreciate your time and
efforts very much.

I tried your changes, which resulted in an error message (sic) could not
find field frmPersonEvent...so I think you had the order right the first time.

I'm wondering if there's an issue with linking to a sub-subform (as it
were), and if links can only be connected on forms and subforms.

I wrote a report for the database owner so they could print a list of events
associated with a person so they can search the EventID field to tie them
over until I get back from surgery in January.

I want to thank you and Sandy for your efforts today and for your good
wishes reguarding my surgery. I very much appreciate the generous helpful
folks who post to these newsgroups.

Thanks again,
Trisha

stLinkCriteria = "[PersonID]=" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID

The above presumes PersonID is a number data type. If it is a text data
type, use this:
stLinkCriteria = "[PersonID]= '" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID & "'"

Steve
PC Datasheet

Thank you for your time and responses.

Both of these solutions result in a parameter prompt for the criteria.

I'm sure these are both very close, I'm just not quite there.

Any additional thoughts/suggestions will be gratefully accepted.

Thanks again,
Trisha

 

[PC Datasheet]

Trisha,

In Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID, FrmPersonEvent and
FrmPerson must be the name of the subform controls NOT the name of the
subform itself! Perhaps this is your problem. Open your form NewEvent in
design view. Select the subform control. Go to properties and the Other tab
and see what the subform control's name is. If it is not FrmPersonEvent,
chnage the name to that. Close everything. Open your form FrmPersonEvent in
design view. Select the subform control. Go to properties and the Other tab
and see what the subform control's name is. If it is not FrmPerson, chnage
the name to that.

Now try clicking on your button.

Steve
PC Datasheet

Steve,

Thank you for persevering with my problem, I appreciate your time and
efforts very much.

I tried your changes, which resulted in an error message (sic) could not
find field frmPersonEvent...so I think you had the order right the first time.

I'm wondering if there's an issue with linking to a sub-subform (as it
were), and if links can only be connected on forms and subforms.

I wrote a report for the database owner so they could print a list of events
associated with a person so they can search the EventID field to tie them
over until I get back from surgery in January.

I want to thank you and Sandy for your efforts today and for your good
wishes reguarding my surgery. I very much appreciate the generous helpful
folks who post to these newsgroups.

Thanks again,
Trisha

stLinkCriteria = "[PersonID]=" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID

The above presumes PersonID is a number data type. If it is a text data
type, use this:
stLinkCriteria = "[PersonID]= '" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID & "'"

Steve
PC Datasheet

Thank you for your time and responses.

Both of these solutions result in a parameter prompt for the criteria.

I'm sure these are both very close, I'm just not quite there.

Any additional thoughts/suggestions will be gratefully accepted.

Thanks again,
Trisha

 

[Guest]

Steve,

The controls are named FrmPersonEvent and FrmPerson respectively (I did
verify that as you suggested just in case I was wrong). Also, PersonID is a
number.

In your first response you wrote the criteria as:
Forms!NewEvent!FrmPersonEvent.Form!FrmPerson.Form!PersonID

to which I added
"Forms!NewEvent!FrmPersonEvent.Form!FrmPerson.Form!PersonID=" & Me.[PersonID]
in an attempt to match the syntax of link criteria I have used successfully
in the past. This is where I was prompted for the parameter.

In case I have completely confused the issue, Me in this case, is
frmFindPerson. I'm trying to open NewEvent where the PersonID on the subform
frmPerson on the subform FrmPersonEvent on the form NewEvent is equal to
frmFindPerson's control PersonID.

I've juggled with the syntax getting errors of can't find form NewEvent,
can't find frmPerson and the aforementioned prompt for the parameter of
Forms!NewEvent!FrmPersonEvent.Form!FrmPerson.Form!PersonID.

I'm stumped.

Again, I thank you for your efforts. I truly appreciate how hard you have
tried to help me make this work. I know how difficult it is to try to assist
a person when the forms and controls are not accessible, and I'm grateful for
your tenacity.

All my best,
Trisha

Trisha,

In Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID, FrmPersonEvent and
FrmPerson must be the name of the subform controls NOT the name of the
subform itself! Perhaps this is your problem. Open your form NewEvent in
design view. Select the subform control. Go to properties and the Other tab
and see what the subform control's name is. If it is not FrmPersonEvent,
chnage the name to that. Close everything. Open your form FrmPersonEvent in
design view. Select the subform control. Go to properties and the Other tab
and see what the subform control's name is. If it is not FrmPerson, chnage
the name to that.

Now try clicking on your button.

Steve
PC Datasheet

Steve,

Thank you for persevering with my problem, I appreciate your time and
efforts very much.

I tried your changes, which resulted in an error message (sic) could not
find field frmPersonEvent...so I think you had the order right the first time.

I'm wondering if there's an issue with linking to a sub-subform (as it
were), and if links can only be connected on forms and subforms.

I wrote a report for the database owner so they could print a list of events
associated with a person so they can search the EventID field to tie them
over until I get back from surgery in January.

I want to thank you and Sandy for your efforts today and for your good
wishes reguarding my surgery. I very much appreciate the generous helpful
folks who post to these newsgroups.

Thanks again,
Trisha

stLinkCriteria = "[PersonID]=" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID

The above presumes PersonID is a number data type. If it is a text data
type, use this:
stLinkCriteria = "[PersonID]= '" &
Me!FrmPersonEvent.Form!FrmPerson.Form!PersonID & "'"

Steve
PC Datasheet

Thank you for your time and responses.

Both of these solutions result in a parameter prompt for the criteria.

I'm sure these are both very close, I'm just not quite there.

Any additional thoughts/suggestions will be gratefully accepted.

Thanks again,
Trisha