Using DLookup in Subform (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:36
Joined
May 7, 2009
Messages
19,169
If you can't get it work with Dlookup(), Listen to other suggestions.

Use a Query for your subform's Recordsource.
Join Product table and Division table with your Quotation detail table.

Query1:
Code:
SELECT [Quote Detail].*,
    Divisions.[Division Name],
    Products.[Product Name]
FROM ([Quote Detail]
    LEFT JOIN Divisions ON [Quote Detail].DivisionID = Divisions.DivisionID)
        LEFT JOIN Products ON [Quote Detail].ProductID = Products.ProductID;

use the Query as Recordsource of your subform.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:36
Joined
Feb 28, 2001
Messages
26,996
OK, I don't use SQL Server, but I remember some prior forum issues about not having unique row IDs for SQL Server tables causing some other problems with lookups. It's part of a hazy memory, but could that idea perhaps apply here?
 

camromere

New member
Local time
Today, 06:36
Joined
Jul 20, 2021
Messages
15
So I stand corrected. I looked at your formula for the DLookup and duplicated it exactly as far as I can tell, substituting my table/field names, and I'm still getting the #Name? error. Can anyone help me understand why this is not working?

Code:
=DLookUp("[Product Name]","Products","[Product ID]=" & [altProductID])
 

camromere

New member
Local time
Today, 06:36
Joined
Jul 20, 2021
Messages
15
If you can't get it work with Dlookup(), Listen to other suggestions.

Use a Query for your subform's Recordsource.
Join Product table and Division table with your Quotation detail table.

Query1:
Code:
SELECT [Quote Detail].*,
    Divisions.[Division Name],
    Products.[Product Name]
FROM ([Quote Detail]
    LEFT JOIN Divisions ON [Quote Detail].DivisionID = Divisions.DivisionID)
        LEFT JOIN Products ON [Quote Detail].ProductID = Products.ProductID;

use the Query as Recordsource of your subform.
If I use a query for the subform, would it still be editable in the datagridview like it is with a table control source? I didn't think it would, which is the only reason I haven't switched to a query for the control source.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:36
Joined
May 7, 2009
Messages
19,169
would it still be editable in the datagridview like
Yes it will be editable, I am using Left Join (not Inner Join).
just don't Edit the product name or division name.
 

camromere

New member
Local time
Today, 06:36
Joined
Jul 20, 2021
Messages
15
If you can't get it work with Dlookup(), Listen to other suggestions.

Use a Query for your subform's Recordsource.
Join Product table and Division table with your Quotation detail table.

Query1:
Code:
SELECT [Quote Detail].*,
    Divisions.[Division Name],
    Products.[Product Name]
FROM ([Quote Detail]
    LEFT JOIN Divisions ON [Quote Detail].DivisionID = Divisions.DivisionID)
        LEFT JOIN Products ON [Quote Detail].ProductID = Products.ProductID;

use the Query as Recordsource of your subform.
This ended up resolving my issue. I never got DLookup to work, but others here have proven that it does. I don't know what was causing it to fail on my application. I appreciate everyone's help to get this working!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:36
Joined
Feb 19, 2002
Messages
42,970
The join is a more efficient option. Just make sure that the controls displaying the lookup data have their locked property set to yes to avoid accidental updates to the lookup table.

I use SQL almost exclusively and have never had a problem with dLookup(). Just don't use domain functions in queries or VBA loops. Each domain function runs a separate query and that gets expensive if your recordset includes more than a few hundred records.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:36
Joined
Sep 12, 2006
Messages
15,613
maybe the fields you are trying to use are defined as a string format in the control (blank in the format column)
change the format property to a number format (maybe fixed)

sometimes access can't or doesn't automatically convert a string to a number, and then you will get issues.
The fact that the dlookup works with hard coding the number shows the problem is to do with the field/control.
 

Users who are viewing this thread

Top Bottom