Using DLookup in Subform

[The_Doc_Man]

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]

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?

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

 

[camromere]

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:

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]

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]

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:

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!

 

[gemma-the-husky]

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.