DLookup in subform

[Guest]

Using Access 2007:
Form - frmNewQuote
Continuous (Tabular) Subform - subfrmOptions with unitPrice textbox
calculating price from value entered into materials field in the subform
Table taskMaterials with description field matching materials combobox in
subfrmOptions (datatype is text)
DLookup entered as control source for unitPrice textbox
=DLookUp("[price]","[taskMaterials]","[description]=" & " [materials]")

This works OK (ie the correct price is displayed after material is selected
from a combo) in the subform when it is displayed as just a form, but when
attached as a subform to the main form, there is no display in the calculated
field textbox. (Or, even worse, the first record entered will display the
price, but subsequent fields don't)

I have also tried
=DLookUp("[price]","[taskMaterials]","[description]=" & "
[Forms]![frmNewQuote]![subformOptions].[Form]![materials]")
in the subform when embedded in the main form, and the result is a flashing
#Error

Please help me fix this. Thanks!

 

[AccessVandal via AccessMonster.com]

Hi,

Try adding a single qoute in the Dlookup..

=DLookUp("[price]","[taskMaterials]","[description]= ' " & "
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " ")

I spaced the qoutes so that you it better.

kasab wrote:
Using Access 2007:
Form - frmNewQuote
Continuous (Tabular) Subform - subfrmOptions with unitPrice textbox
calculating price from value entered into materials field in the subform
Table taskMaterials with description field matching materials combobox in
subfrmOptions (datatype is text)
DLookup entered as control source for unitPrice textbox
=DLookUp("[price]","[taskMaterials]","[description]=" & " [materials]")

This works OK (ie the correct price is displayed after material is selected
from a combo) in the subform when it is displayed as just a form, but when
attached as a subform to the main form, there is no display in the calculated
field textbox. (Or, even worse, the first record entered will display the
price, but subsequent fields don't)

I have also tried
=DLookUp("[price]","[taskMaterials]","[description]=" & "
[Forms]![frmNewQuote]![subformOptions].[Form]![materials]")
in the subform when embedded in the main form, and the result is a flashing
#Error

Please help me fix this. Thanks!

 

[AccessVandal via AccessMonster.com]

Sorry, error there.

=DLookUp("[price]","[taskMaterials]","[description]= ' " &
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )

 

[Guest]

Thanks, but I now have #Name? displayed in unitCost field. Do you have any
other suggestions please?

Sorry, error there.

=DLookUp("[price]","[taskMaterials]","[description]= ' " &
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )

kasab wrote:

 

[AccessVandal via AccessMonster.com]

Hi,

This error indicates Access cannot find the Control in your sub-form or the
Form's RecordSource Query is missing a field.

Check the RecordSource Field and the Control name. Else you may want to refer
the Dlookup to another method.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )

or

=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " )

Note: Assuming the DLookup is in the Sub-Form. Me!materials is the subform
control name.

or

Forms!subformOption.material to refer to a control if the Dlookup is in the
subform.

 

[Guest]

Thanks again for staying with me.

If I read you correctly, materials may be missing from the subform's fields?
No. This is taken directly from another table, not a query. Also, both fields
(description in taskMaterials table and materials in subfrmOptions) and the
table have correct spelling in the DLookup.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " )
isn't working.

Both materials and the DLookup textbox are in the subform.

Also, no difference with
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " ) or
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Forms!subformOption.materials & " ' " )

If I use a (sub)subform based on a query instead of a DLookup, I'll need to
lookup another field for another calculation, so that's not really an option
either.

Any suggestions on where else to look? This punctuation thing has got me beat.

Thanks

Hi,

This error indicates Access cannot find the Control in your sub-form or the
Form's RecordSource Query is missing a field.

Check the RecordSource Field and the Control name. Else you may want to refer
the Dlookup to another method.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )

or

=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " )

Note: Assuming the DLookup is in the Sub-Form. Me!materials is the subform
control name.

or

Forms!subformOption.material to refer to a control if the Dlookup is in the
subform.

kasab wrote:
Thanks, but I now have #Name? displayed in unitCost field. Do you have any
other suggestions please?

 

[AccessVandal via AccessMonster.com]

So, your’re saying the subform RecordSource is not part of the DlookUp.

Does the control “material†is the name of the control? Check the "material"
properties under the Tab "Other" and look the Name field.

kasab wrote:
Thanks again for staying with me.

If I read you correctly, materials may be missing from the subform's fields?
No. This is taken directly from another table, not a query. Also, both fields
(description in taskMaterials table and materials in subfrmOptions) and the
table have correct spelling in the DLookup.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " )
isn't working.

Both materials and the DLookup textbox are in the subform.

Also, no difference with
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " ) or
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Forms!subformOption.materials & " ' " )

If I use a (sub)subform based on a query instead of a DLookup, I'll need to
lookup another field for another calculation, so that's not really an option
either.

Any suggestions on where else to look? This punctuation thing has got me beat.

Thanks

Hi,

[quoted text clipped - 24 lines]

 

[AccessVandal via AccessMonster.com]

The other option that I can suggest is to use the "taskMaterials" table with
a materialID instead of using the "description" field as a lookup. Using the
"description" is a very bad idea to begin with. What if you have more than
one with the same description?

If all else fails, use the DlookUp to find a single item first, than with
that we'll try to narrow it down.

=DLookUp("[price]","[taskMaterials]","[description]= 'the description of the
material here'")

 

[AccessVandal via AccessMonster.com]

I like to add further, if the textbox "material" and if it is blank or empty
it will produce an error. Like what you have posted "#Error".

 

[Guest]

Yes, the Name property of the control that contains the data on the subform
that is used in the DLookup is "materials" as used in the DLookup.

The subform RecordSource is the quotes table that contains the field
"materials" (text datatype).

And the DLookup works fine in the (sub)form displayed in form view on its
own.

So, your’re saying the subform RecordSource is not part of the DlookUp.

Does the control “material†is the name of the control? Check the "material"
properties under the Tab "Other" and look the Name field.

kasab wrote:
Thanks again for staying with me.

If I read you correctly, materials may be missing from the subform's fields?
No. This is taken directly from another table, not a query. Also, both fields
(description in taskMaterials table and materials in subfrmOptions) and the
table have correct spelling in the DLookup.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " )
isn't working.

Both materials and the DLookup textbox are in the subform.

Also, no difference with
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " ) or
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Forms!subformOption.materials & " ' " )

If I use a (sub)subform based on a query instead of a DLookup, I'll need to
lookup another field for another calculation, so that's not really an option
either.

Any suggestions on where else to look? This punctuation thing has got me beat.

Thanks

Hi,

[quoted text clipped - 24 lines]

Thanks, but I now have #Name? displayed in unitCost field. Do you have any
other suggestions please?

 

[Guest]

Thanks. I'll split the taskMaterials table and see what happens then - if
this was the problem, I'll post in a day or 2. Thanks again.

 

[BruceM]

I think this line needs to be changed:
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials]

It should be:
[Forms]![frmNewQuote]![subfrmOptions].Form![materials]

"Form" after [subfrmOptions] should not have the square brackets, since it
is a property and not a field or control.

Yes, the Name property of the control that contains the data on the
subform
that is used in the DLookup is "materials" as used in the DLookup.

The subform RecordSource is the quotes table that contains the field
"materials" (text datatype).

And the DLookup works fine in the (sub)form displayed in form view on its
own.

So, your're saying the subform RecordSource is not part of the DlookUp.

Does the control "material" is the name of the control? Check the
"material"
properties under the Tab "Other" and look the Name field.

kasab wrote:
Thanks again for staying with me.

If I read you correctly, materials may be missing from the subform's
fields?
No. This is taken directly from another table, not a query. Also, both
fields
(description in taskMaterials table and materials in subfrmOptions) and
the
table have correct spelling in the DLookup.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " )
isn't working.

Both materials and the DLookup textbox are in the subform.

Also, no difference with
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Me!materials & " ' " ) or
=DLookUp("[price]","[taskMaterials]","[description]= ' " &
Forms!subformOption.materials & " ' " )

If I use a (sub)subform based on a query instead of a DLookup, I'll need
to
lookup another field for another calculation, so that's not really an
option
either.

Any suggestions on where else to look? This punctuation thing has got me
beat.

Thanks

Hi,

[quoted text clipped - 24 lines]
Thanks, but I now have #Name? displayed in unitCost field. Do you
have any
other suggestions please?