DLookup in subform

  • Follow


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!
0
Reply Utf 9/19/2007 4:40:02 AM

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/Forums.aspx/access-forms/200709/1

0
Reply AccessVandal 9/19/2007 5:52:51 AM


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/Forums.aspx/access-forms/200709/1

0
Reply AccessVandal 9/19/2007 6:00:41 AM

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/Forums.aspx/access-forms/200709/1
> 
> 
0
Reply Utf 9/19/2007 6:38:01 AM

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/Forums.aspx/access-forms/200709/1

0
Reply AccessVandal 9/19/2007 7:12:13 AM

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/Forums.aspx/access-forms/200709/1
> 
> 
0
Reply Utf 9/19/2007 7:52:01 AM

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/Forums.aspx/access-forms/200709/1

0
Reply AccessVandal 9/19/2007 8:48:47 AM

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

0
Reply AccessVandal 9/19/2007 8:57:37 AM

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/Forums.aspx/access-forms/200709/1

0
Reply AccessVandal 9/19/2007 9:06:58 AM

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/Forums.aspx/access-forms/200709/1
> 
> 
0
Reply Utf 9/19/2007 9:14:01 AM

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.

"AccessVandal via AccessMonster.com" wrote:

> 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/Forums.aspx/access-forms/200709/1
> 
> 
0
Reply Utf 9/19/2007 9:36:01 AM

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.

"kasab" <kasab@discussions.microsoft.com> wrote in message 
news:193E6394-FFC3-42EE-998D-EDD4885EAF04@microsoft.com...
> 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/Forums.aspx/access-forms/200709/1
>>
>> 


0
Reply BruceM 9/19/2007 11:20:55 AM

11 Replies
841 Views

(page loaded in 0.127 seconds)


Reply: