DLookup in subform

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!

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!

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

Sorry, error there.

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


kasab wrote:

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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

"AccessVandal via AccessMonster.com" wrote:

Sorry, error there.

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


kasab wrote:

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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?

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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

"AccessVandal via AccessMonster.com" wrote:

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?

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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?

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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'")

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.

--
Please Rate the posting if helps you

Message posted via http://www.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".

kasab wrote:
Thanks again for staying with me.

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1

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.

"AccessVandal via AccessMonster.com" wrote:

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?

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200709/1