DLookup in subform - microsoft.public.access.forms

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

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

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

Similiar Articles:

DLookup in subform - microsoft.public.access.forms
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 ...

DLookup in Conditional Formatting? - microsoft.public.access.forms ...
DLookup in subform - microsoft.public.access.forms DLookup in subform - microsoft.public.access.forms Hi, I have a subform in datasheet view and need 4 conditional formats ...

subform displays no records when its recordsource is a query ...
DLookup in subform - microsoft.public.access.forms (Or, even worse, the first record entered will display the price, but subsequent ... fails, use the DlookUp to find a ...

Subform Textbox Properties - microsoft.public.access
DLookup in subform - microsoft.public.access.forms Dlookup from subform Hi Experts, I created a new textbox named it txtbal in Main Form and click textbox properties and ...

Calculated a field in a subform. - microsoft.public.access.forms ...
DLookup in subform - microsoft.public.access.forms Thanks again for staying with me. If I read you correctly, materials may be missing from the subform's fields?

DLookup fails in Form's Text Box Control Source when using ...
DLookup in subform - microsoft.public.access.forms DLookup fails in Form's Text Box Control Source when using ... DLookup in subform - microsoft.public.access.forms If all ...

using dlookup in a query - microsoft.public.access
DLookup in subform - microsoft.public.access.forms using dlookup in a query - microsoft.public.access DLookup in subform - microsoft.public.access.forms If all else fails ...

Testing Subform Records to make button visible - microsoft.public ...
Use the Current event of the main form, to DLookup() the primary key value of the related table in the subform where the yes/no field is true.

VBA to page down in a subform - microsoft.public.access ...
DLookup in subform - microsoft.public.access.forms VBA to page down in a subform - microsoft.public.access ... Passing a value from subform or main form to subform query ...

Criteria for opening a form based on a value in another form's ...
Groups About ... for another calculation, so that's not really an option ... price from value ... forms DLookup in subform Form's ... based on subform ... form ...

DLookup - why this syntax? - microsoft.public.access.forms ...
Note that neither a DLookUp nor a text literal will be updateable... If you're having trouble post the SQL view of the form and subform's recordsource and a bit more ...

If query/subform is empty set calculation value to 0 - microsoft ...
DLookup in subform - microsoft.public.access.forms 0 ... lookup another field for another calculation ... Passing a value from subform or main form to subform query ...

MS Access 2007: Calculated field based on subform: - microsoft ...
DLookup in subform - microsoft.public.access.forms Using Access 2007: Form - frmNewQuote Continuous (Tabular) Subform ... in the calculated field ... subform that needs to ...

validation rule (for a record in a table) with dlookup - microsoft ...
DLookup in subform - microsoft.public.access.forms validation rule (for a record in a table) with dlookup - microsoft ... DLookup in subform - microsoft.public.access ...

Label Caption on Subform from Textbox Value on Main Form ...
Every time txtColumnCaption1 changes, the value of Label1 on the subform needs ... How to Show Form ComboBox value into other Form Label caption ... ... DLookup in subform ...

Passing a value from subform or main form to subform query ...
DLookup in subform - microsoft.public.access.forms VBA to page down in a subform - microsoft.public.access ... Passing a value from subform or main form to subform query ...

ms access form validation - microsoft.public.access.formscoding ...
validation rule (for a record in a table) with dlookup - microsoft ... DLookup in subform - microsoft.public.access.forms validation rule (for a record in a ...

Pass name of control to a form - microsoft.public.access ...
DLookup in subform - microsoft.public.access.forms Pass name of control to a form - microsoft.public.access ... How do I pass the name of a control on Form_A to Form_B?

Flash on Access form - microsoft.public.access.formscoding ...
DLookup in subform - microsoft.public.access.forms Using Access 2007: Form - frmNewQuote Continuous (Tabular) Subform ... materials]") in the subform when embedded in the ...

Sum value from Dlookup function - microsoft.public.access.forms ...
How to sum DLookup values in a form in MS Access 2003: forms, MS ... I have a datasheet subform with some controls that have a calculated value utilizing the dlookup ...

Excel - Dlookup In Subform - I have in my subForm... - Free Excel Help
Dlookup In Subform - I have in my subForm named frmMySub a control with... - Free Excel Help

DLookup in subform DataBase - DataBase Discussion List Tuesday ...
Using Access 2007: Form - frmNewQuote Continuous (Tabular) Subform - subfrmOptions with unitPrice textbox calculating price from value entered into mater

Dlookup not working in subform - dBforums
Dlookup works in form, but not in subform. In the form called Item Subform , I have a combo box that uses both fields from the table L-Property-ClassLi

DLookup in a subform: dlookup, subform - Experts Exchange - Your ...
I have a subform embedded in a form. I want to use the DLookup function on a field contained in the subform. I'm getting various errors that I believe are ...

DLOOKUP in Access 2007 subform unbound control getting Name error ...
I want to look up the cost field in the drugs table when the drug id being selected for order is the same as the same as the drug id in the drugs table. I am getting ...

Dlookup from subform
Hi Experts, I created a new textbox named it txtbal in Main Form and click textbox properties and place below Dlookup in ControlSource, but it is error ...

DLookup Criteria In A Subform - Microsoft Access / VBA
DLookup Criteria In A Subform. Microsoft Access / VBA Forums on Bytes.

Subform Dlookup
I have successfully written a DLookup in the control source Properties table of a subform when I open the subform directly from the Forms Objects site,

Microsoft Access: Dlookup in a subform - database.itags.org
database.itags.org: Microsoft Access question: Dlookup in a subform, created at:Mon, 26 May 2008 17:33:00 GMT with 189 bytes, last updated: Monday, July 16, 2012, 1 ...

DLookup in subform - microsoft.public.access.forms | Microsoft ...
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 ...

7/20/2012 4:31:53 AM