Problem referencing from subform

[Leslie Isaacs]

Hello All

I have a main form called [frm x main], which has a subform called [qry x
main table subform] (representing a set of people). The subform opens in
datasheet view, and includes a field called [name] (yes - I know the naming
convention is terrible - but I inherited this and so far it all works!).
There is another (main) form called [staffs subform new] (based on all the
people in the database), which also contains the same [name] field as [qry x
main table subform]. I would like to be able to open [staffs subform new] by
double-clicking the [name] fields in one of the records of [qry x main table
subform] so that [staffs subform new] opens at the record corresponding to
the selected record in [qry x main table subform]. The code I have as the
doubleclick event for [name] in [qry x main table subform] is below, which
does open the form but not at the selected record. I have used this code
before from a main form and it works perfectly, so I am assuming that the
problem is to do with trying to reference the required field from a subform.

Hope someone an help.
Many thanks
Les

My code:

Private Sub Name_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "staffs subform new"

stLinkCriteria = "[name]=" & "'" & Me![name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[NetworkTrade]

I believe as a sanity check you would want to open that subform directly (not
as a subform to the main form) and double check that it can open the other
main form with that code.... there won't be any subform referencing issue
because it isn't a subform....

then if the issue really is only referencing ; then you could try replacing:
& Me![name] &
with the complete formal subform control reference syntax:
& Forms!MainFormName.SubFormName.Form![SubFormControlName] &

( the .Form! is literal ...just like Forms!)

although Me.SubFormName.form![subformControlName] should work also....

 

[Larry Linson]

Long-distance debugging is difficult enough when everything is "according to
the rules" but when the issue is confused by things like using Access
reserved words for Field Names, it's just hardly worth anyone's effort. I
don't know if that's causing a problem, but if "everything was OK", you
wouldn't be asking for assitance, would you?

Using the "Me" shortcut in Code in the Module of the Form embedded in the
Subform Control should not cause any "referencing problem". Set a stop on
the DoCmd.OpenForm and check the values to see if they are what you expect.

Maybe someone else will spend more time, if that doesn't help, even if you
don't change the Access reserved word used for fieldname to a word that
isn't reserved.

Larry Linson
Microsoft Office Access MVP

 

[Leslie Isaacs]

Hello NTC'

Thanks for your reply. I tried your suggestion of running the event from the
form directly (not as a subform), and it still didn't work! Can't see why -
this syntax has worked for me in the past. I've got round the problem simply
by doing an 'apply filter' after opening the form, and that works fine, so
I'll stick with that.

Thanks again for the help.
Les

I believe as a sanity check you would want to open that subform directly (not
as a subform to the main form) and double check that it can open the other
main form with that code.... there won't be any subform referencing issue
because it isn't a subform....

then if the issue really is only referencing ; then you could try replacing:
& Me![name] &
with the complete formal subform control reference syntax:
& Forms!MainFormName.SubFormName.Form![SubFormControlName] &

( the .Form! is literal ...just like Forms!)

although Me.SubFormName.form![subformControlName] should work also....
--
NTC

Hello All

I have a main form called [frm x main], which has a subform called [qry x
main table subform] (representing a set of people). The subform opens in
datasheet view, and includes a field called [name] (yes - I know the naming
convention is terrible - but I inherited this and so far it all works!).
There is another (main) form called [staffs subform new] (based on all the
people in the database), which also contains the same [name] field as [qry x
main table subform]. I would like to be able to open [staffs subform new] by
double-clicking the [name] fields in one of the records of [qry x main table
subform] so that [staffs subform new] opens at the record corresponding to
the selected record in [qry x main table subform]. The code I have as the
doubleclick event for [name] in [qry x main table subform] is below, which
does open the form but not at the selected record. I have used this code
before from a main form and it works perfectly, so I am assuming that the
problem is to do with trying to reference the required field from a subform.

Hope someone an help.
Many thanks
Les

My code:

Private Sub Name_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "staffs subform new"

stLinkCriteria = "[name]=" & "'" & Me![name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[Leslie Isaacs]

Hello Lary

Thanks for your reply.

When you say 'but if "everything was OK", you wouldn't be asking for
assitance, would you?', that's not quite true: I was only asking for help
with the filtering - everything else (which is a lot!) is working. When I
get time I will amend the reserved words, but in the short term the
filtering is more of a priority - and not something for which that the
reserved word issue is significant.

Thanks anyway.
Les

Long-distance debugging is difficult enough when everything is "according to
the rules" but when the issue is confused by things like using Access
reserved words for Field Names, it's just hardly worth anyone's effort. I
don't know if that's causing a problem, but if "everything was OK", you
wouldn't be asking for assitance, would you?

Using the "Me" shortcut in Code in the Module of the Form embedded in the
Subform Control should not cause any "referencing problem". Set a stop on
the DoCmd.OpenForm and check the values to see if they are what you expect.

Maybe someone else will spend more time, if that doesn't help, even if you
don't change the Access reserved word used for fieldname to a word that
isn't reserved.

Larry Linson
Microsoft Office Access MVP

Hello All

I have a main form called [frm x main], which has a subform called [qry x
main table subform] (representing a set of people). The subform opens in
datasheet view, and includes a field called [name] (yes - I know the
naming
convention is terrible - but I inherited this and so far it all works!).
There is another (main) form called [staffs subform new] (based on all the
people in the database), which also contains the same [name] field as [qry
x
main table subform]. I would like to be able to open [staffs subform new]
by
double-clicking the [name] fields in one of the records of [qry x main
table
subform] so that [staffs subform new] opens at the record corresponding to
the selected record in [qry x main table subform]. The code I have as the
doubleclick event for [name] in [qry x main table subform] is below, which
does open the form but not at the selected record. I have used this code
before from a main form and it works perfectly, so I am assuming that the
problem is to do with trying to reference the required field from a
subform.

Hope someone an help.
Many thanks
Les

My code:

Private Sub Name_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "staffs subform new"

stLinkCriteria = "[name]=" & "'" & Me![name] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub