DLookup in subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kasab - 19 Sep 2007 05:40 GMT

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!

Reply to this Message

AccessVandal - 19 Sep 2007 06:52 GMT

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:
[quoted text clipped - 19 lines]
>
>Please help me fix this. Thanks!

Signature

Please Rate the posting if helps you

Reply to this Message

AccessVandal - 19 Sep 2007 07:00 GMT

Sorry, error there.

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

>kasab wrote:

Signature

Please Rate the posting if helps you

Reply to this Message

kasab - 19 Sep 2007 07:38 GMT

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:

Reply to this Message

AccessVandal - 19 Sep 2007 08:12 GMT

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?

Signature

Please Rate the posting if helps you

Reply to this Message

kasab - 19 Sep 2007 08:52 GMT

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?

Reply to this Message

AccessVandal - 19 Sep 2007 09:48 GMT

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.
[quoted text clipped - 30 lines]
>> >Thanks, but I now have #Name? displayed in unitCost field. Do you have any
>> >other suggestions please?

Signature

Please Rate the posting if helps you

Reply to this Message

kasab - 19 Sep 2007 10:14 GMT

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.
>
[quoted text clipped - 35 lines]
> >> >Thanks, but I now have #Name? displayed in unitCost field. Do you have any
> >> >other suggestions please?

Reply to this Message

BruceM - 19 Sep 2007 12:20 GMT

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
[quoted text clipped - 53 lines]
>> >> >have any
>> >> >other suggestions please?

Reply to this Message

AccessVandal - 19 Sep 2007 09:57 GMT

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.

Signature

Please Rate the posting if helps you

Reply to this Message

AccessVandal - 19 Sep 2007 10:06 GMT

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.

Signature

Please Rate the posting if helps you

Reply to this Message

kasab - 19 Sep 2007 10:36 GMT

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.

> 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.

Reply to this Message