I have a form with 4 dropdowns and/or listboxes which enables the user to quickly drill down to a quote to modify. Once they select the quote to modify, two subforms populate for that quote - the quote header and the quote details. The quote details subform is displayed as a datagrid because there are anywhere from 1-50 lines per quote and it makes it much simpler for the end user to find the line(s) they want to modify by looking at one screen.
The issue that I have now, the subform that modifies quote details has a couple of ID fields for which they now want to also see the descriptions displayed as well, but they are not part of the quote details table. They are referenced by DivisionID and ProductID to the Divisions and Products tables respectively. Theoretically, DLookup sounds like exactly what I need to use. However, I have tried several different ways of typing that formula out, but I continually get the #Name? message in the product description and division descriptions in the subform.
Here is the structure of the forms and tables.
Main form = frmModifyQuote
Header subform = frmModQuotes (tied directly to the Quotes table)
Details subform = frmModQuoteDetail (tied directly to the [Quotes Detail] table)
Tables
Quotes.QuoteID => [Quotes Detail].QuoteID
[Quotes Detail].ProductID => Products.ProductID (show [Product Name])
[Quotes Detail].DivisionID => Divisions.DivisionID (show [Division Name])
The formula I have currently in the frmModQuoteDetail subform to show the [Product Name] is:
=DLookUp([Products]![Product Name],[Products],[Products]![Product ID]=[Product ID])
I have also tried this statement:
=DLookUp([Products]![Product Name],[Products],[Products]![Product ID]= Forms![frmModQuoteDetail]![Product ID] )
Every iteration I've tried just gives me #Name? in the field that should display the [Product Name].
Any suggestions and/or tips would be greatly appreciated.
The issue that I have now, the subform that modifies quote details has a couple of ID fields for which they now want to also see the descriptions displayed as well, but they are not part of the quote details table. They are referenced by DivisionID and ProductID to the Divisions and Products tables respectively. Theoretically, DLookup sounds like exactly what I need to use. However, I have tried several different ways of typing that formula out, but I continually get the #Name? message in the product description and division descriptions in the subform.
Here is the structure of the forms and tables.
Main form = frmModifyQuote
Header subform = frmModQuotes (tied directly to the Quotes table)
Details subform = frmModQuoteDetail (tied directly to the [Quotes Detail] table)
Tables
Quotes.QuoteID => [Quotes Detail].QuoteID
[Quotes Detail].ProductID => Products.ProductID (show [Product Name])
[Quotes Detail].DivisionID => Divisions.DivisionID (show [Division Name])
The formula I have currently in the frmModQuoteDetail subform to show the [Product Name] is:
=DLookUp([Products]![Product Name],[Products],[Products]![Product ID]=[Product ID])
I have also tried this statement:
=DLookUp([Products]![Product Name],[Products],[Products]![Product ID]= Forms![frmModQuoteDetail]![Product ID] )
Every iteration I've tried just gives me #Name? in the field that should display the [Product Name].
Any suggestions and/or tips would be greatly appreciated.