Using DLookup in Subform

[camromere]

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.

 

[theDBguy]

Hi. Welcome to AWF!

The DLookup() syntax requires double-quotes as delimiters for each argument. For example:

=DLookup("FieldNameToLookup", "TableNameForLookingUp", "CriteriaFieldName=" & [CriteriaValueFieldHere])

However, it might also work if you simply JOIN the other tables to the subform record source.

Hope that helps...

 

[camromere]

Ok I tried that and here is what I have now.
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![frmModQuoteDetail]![Product ID])

However, I'm still getting the #Name? in the field.

I had hoped not to have to change the datasource for the subform because the updates happen automatically if they change anything on the screen. But, I'm beginning to think if they want to see the additional fields for reference, I may have to build out my own update record code. Grrrr.

 

[theDBguy]

Ok I tried that and here is what I have now.
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![frmModQuoteDetail]![Product ID])

However, I'm still getting the #Name? in the field.

I had hoped not to have to change the datasource for the subform because the updates happen automatically if they change anything on the screen. But, I'm beginning to think if they want to see the additional fields for reference, I may have to build out my own update record code. Grrrr.

A #Name? error is usually caused by a typo. Maybe double-check to make sure all object references are correct?

 

[Gasman]

If it is subform, I'd expect to see mainform.subformcontrol.form in there somewhere ?

 

[camromere]

If it is subform, I'd expect to see mainform.subformcontrol.form in there somewhere ?

I've tried all the following:

=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Me]![Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![frmModQuoteDetail]![Product ID])

 

[theDBguy]

I've tried all the following:

=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Me]![Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![frmModQuoteDetail]![Product ID])

Could you post a sample db with test data?

 

[Gasman]

I've tried all the following:

=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Me]![Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Product ID])
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![frmModQuoteDetail]![Product ID])

I would have thought your last attempt would be good for a mainform?
Perhaps

=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![MainFormName]![SubformControlName].Form.[Product ID])

 

[camromere]

Could you post a sample db with test data?

It's a SQL Server back end - let me give it a try.

 

[camromere]

It is not cooperating putting the database online to connect to it.

 

[theDBguy]

It is not cooperating putting the database online to connect to it.

How about creating a mockup db (data and forms in one file) for now? We just wanted to get a better idea of what you're trying to do and to help see where you're having difficulty with using DLookup().

 

[gemma-the-husky]

Is ProductID a number or a string.

If the latter then the syntax will be incorrect.

Try hardcoding a value in to see what happens.

msgbox DLookUp("[Product Name]","[Products]","[Product ID]=" & 12)

 

[camromere]

Ok the [Product ID] field is an Integer and if I replace the DLookUp formula criteria with a hard-coded number, it pulls the Product Name correctly for that Product ID.

=DLookUp("[Product Name]","[Products]","[Product ID]=" & 498)

I did manage to get the data extracted to local tables and anonymized. Oddly enough, when I went to the tables in Access, the DLookup works as I would expect it to. I'm attaching the reduced file with anonymous data and the form that has the issue. I didn't tweak the controls placement on here just because I wanted to show you what it's doing. Select a company, then select a freight type, then select a specific quote. Then the quote header and the quote details subforms show up displaying the data for your selected quote. I couldn't get the file to upload because it's still too large.

I can't put the link here because it says my message is spam-like so if you want the Access file DM me and I'll send it to you.

 

[Gasman]

You need 10 posts to be able to attach a file.

 

[Pat Hartman]

Rather than using domain functions, you can use a left join to bring in the lookup fields. Be sure to set the locked property for these fields to yes on the form though to prevent accidental updates.

 

[camromere]

@Pat Hartman I'm not sure where you mean to add the lookup fields exactly. The Control Source for the subform is the table [Quotes Detail]. If I change that to a query to include the other tables, I suspect it would lose the ease of updating from within that subform. I did try this code for the [Product Name] textbox, but it comes back with #Name? also.

=(SELECT [Product Name] FROM [Products WHERE [Product ID] = [altProductID] )

where [altProductID] is the textbox name in the subform.

 

[camromere]

I've been doing more "googling" and it appears that DLookup may not work at all with a SQL Back end. That is quite troublesome. I may have to figure out another way to accomplish this.

 

[theDBguy]

I've been doing more "googling" and it appears that DLookup may not work at all with a SQL Back end. That is quite troublesome. I may have to figure out another way to accomplish this.

Just to clarify for others who may read this thread in the future. DLookup() may not be working for your specific situation, but it definitely should work with a SQL Server back end. If you have a link showing that DLookup() doesn't work with SQL Server, please consider posting it here for reference. Cheers!

 

[camromere]

I tried the same exact situation with a purely Access database to test it and DLookup worked exactly as I expected it to. However, my application has a SQL back end and I've tried every combination of syntax I can find and cannot get it to work with the SQL back end. I get #Name? every single time. I would dearly love to get it to work. The subform is tied to a single table for ease in editing. I'm just trying to display two extra read-only fields from different tables in the same subform.

 

[theDBguy]

I tried the same exact situation with a purely Access database to test it and DLookup worked exactly as I expected it to. However, my application has a SQL back end and I've tried every combination of syntax I can find and cannot get it to work with the SQL back end. I get #Name? every single time. I would dearly love to get it to work. The subform is tied to a single table for ease in editing. I'm just trying to display two extra read-only fields from different tables in the same subform.

Hi. As I was saying, I can appreciate that DLookup() seems to be failing in your specific situation. What I was trying to clarify for others is the statement that you found evidence DLookup() doesn't work with SQL Server back end.

So, just as an experiment, I created the following simple test.

In the above image, I created a small table in SQL Server called "dbo.test" (1). I then inserted some sample data and displayed it (2) and (3).

Then, I created a linked table in Access called "dbo_test" (4). Then, I created a continuous form showing the two columns and added a DLookup() column using the following expression (5).

=DLookUp("prodname","dbo_test","id=" & Nz([id],0))

Here's the result.

Hope that clarifies what I am trying to say that DLookup() should work with SQL Server back ends. Cheers!