Dlookup in subform (Currency)

[nybaseball22]

Hello,

I have been working on this for some time and have tried many
different strings. I am trying to nest a DLookup in a subform that
will lookup an average calculated in a query. I want the string to
perform the DLookup on the subform when it is opened as a form and
when it is opened on the main form as a subform. Is there a code that
will make this work? So far, I have the below that will work when the
subform is opened as a subform, but not as a main form.

=DLookUp("[AvgOfPPG]","[AutosGasStats1Station]","[Station]=Forms!
[AutomobileGasolineSearch].[AutosGasData1sub]![Most Frequented]")

As always, Thanks for the help.

 

[J_Goddard via AccessMonster.com]

Hi -

It's not clear where you have this code - is it in one of the forms or
elsewhere?

You need to test whether or not the main form is loaded or not:

if currentproject.allforms("AutomobileGasolineSearch").isloaded then
'
' Reference as a subform
'
DLookUp("[AvgOfPPG]","[AutosGasStats1Station]","[Station]=Forms!
[AutomobileGasolineSearch].[AutosGasData1sub]![Most Frequented]")

else
'
' reference as a form
'
DLookUp("[AvgOfPPG]","[AutosGasStats1Station]","[Station]=Forms!
[AutosGasData1sub]![Most Frequented]")

endif

Just a note - when a form is used as a subform, it does not appear in the
Forms collection, nor is it considered to be loaded.

HTH

John

Hello,

I have been working on this for some time and have tried many
different strings. I am trying to nest a DLookup in a subform that
will lookup an average calculated in a query. I want the string to
perform the DLookup on the subform when it is opened as a form and
when it is opened on the main form as a subform. Is there a code that
will make this work? So far, I have the below that will work when the
subform is opened as a subform, but not as a main form.

=DLookUp("[AvgOfPPG]","[AutosGasStats1Station]","[Station]=Forms!
[AutomobileGasolineSearch].[AutosGasData1sub]![Most Frequented]")

As always, Thanks for the help.