Help with Dlookup problem in subform/main form

[nybaseball22]

Hello,

I am trying to make a subform that opens on a main form. The code
works when I open the subform, but when the subform opens on the main
form, it gives me an error message. Details:

I have 2 subforms called
"AutosGasData1sub" and "AutosGasData2sub". There is a field on each
subform called "Best MPG" that pulls the best MPG from queries called
"AutosGasStats1" and "AutosGasStats2" respectively. The subforms are
attached to a form called "AutomobileGasolineSearch".

I have added lookup fields to find the date of the Best MPG, and it
works when I open the subforms in form view. However, when I open
the
subforms on the Main form, I get an #Error.

This is the code:

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
AutosGasData1sub![Best MPG] & "'")

Thanks

 

[Wolfgang Kais]

Hello .

Hello,

I am trying to make a subform that opens on a main form. The code
works when I open the subform, but when the subform opens on the
main form, it gives me an error message. Details:

I have 2 subforms called
"AutosGasData1sub" and "AutosGasData2sub". There is a field on
each subform called "Best MPG" that pulls the best MPG from queries
called "AutosGasStats1" and "AutosGasStats2" respectively. The
subforms are attached to a form called "AutomobileGasolineSearch".

I have added lookup fields to find the date of the Best MPG, and it
works when I open the subforms in form view. However, when I open
the subforms on the Main form, I get an #Error.

This is the code:

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
AutosGasData1sub![Best MPG] & "'")

Thanks

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
AutosGasData1sub![Best MPG] & "'")

I guess that [Best MPG] is a number? And the subform controls have
the same name as the subforms? Then try this:

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]=" & Forms!
AutomobileGasolineSearch!AutosGasData1sub.Form![Best MPG])

When the main form is opened, the two subforms are opened "within"
the main form and are not contained in the forms collection. To
access values of a subform, use the syntax:
Forms![MainFormName]![SubformControlName].Form![ControlName]
MainFormName is the element of the forms collection.
SubformControlName is the subform control's name on the main form.
..Form refers to the form that is the source object of the subform
control.
ControlName is the name of the control on the subform.