Using DLookup in Subform (1 Viewer)

camromere

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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

I’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

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

Code:
=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

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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

I’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 00:23
Joined
Sep 21, 2011
Messages
14,048
If it is subform, I'd expect to see mainform.subformcontrol.form in there somewhere ?
 

camromere

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
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

Enthusiastic Amateur
Local time
Today, 00:23
Joined
Sep 21, 2011
Messages
14,048
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
Code:
=DLookUp("[Product Name]","[Products]","[Product ID]=" & [Forms]![MainFormName]![SubformControlName].Form.[Product ID])
 

camromere

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
It is not cooperating putting the database online to connect to it.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 00:23
Joined
Sep 12, 2006
Messages
15,614
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)
 
Last edited:

camromere

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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.

Code:
=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

Enthusiastic Amateur
Local time
Today, 00:23
Joined
Sep 21, 2011
Messages
14,048
You need 10 posts to be able to attach a file.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:23
Joined
Feb 19, 2002
Messages
42,981
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

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
@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.

Code:
=(SELECT [Product Name] FROM [Products WHERE [Product ID] = [altProductID] )
where [altProductID] is the textbox name in the subform.
 

camromere

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Yesterday, 20:23
Joined
Jul 20, 2021
Messages
15
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’m here to help
Staff member
Local time
Yesterday, 17:23
Joined
Oct 29, 2018
Messages
21,358
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.

1633050287297.png


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

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

Here's the result.

1633050420062.png


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

Users who are viewing this thread

Top Bottom