but as I open the orderf there appears a warning

You misspelled "OrderDetailF" - you're missing the "l"

Previous Thread: Trademark casing

Oooo ...reckless indeed

Great eye Donald!  John

unfortunatly,IT only shows up the current price and current casing all products

how to show only the order,if only one product do not show up the rest

Should we still use here

Sorry...it showed up all products the same specprice and speccasing
How come

I meant specprice (or price-default) and speccasing (or trademarcasing-default) were the same for all products

Hi Liem, Can you show us the code that you are using. It is impossible to analyze without seeing the vba that is behind, forms, buttons, queries and modules.  John

Should we still use IIF (ISNULL....

I wonder why the query showed currentprice and the currentcasing to all products

using dlookup it worked correctly

currentcasing subquery... Your criteria is missing something....

You have custid=[FORMINFORMATION] and [MOREFORMINFORMATION]. You don't have the 2nd field name (i.e. prodid = ) or whatever you are trying to lookup

If in the currentprice I add prodid=forms!orderf!orderdetailf.form!productnamecombo)
The result is blank and the currentcasing is filled the same casingname as above

The same to all products

also in this subform,it shows the same price/specprice and trademarkcasing/speccasing
previously as we used iif(isnull... there appeared precisely for their price or casingname (specprice or price and speccasing or tramdemarkcasing)

here if the customer no specprice or speccasing the result are blank

Si DON-ALex-john could i continue asking, it is said "the same question cannot be twice"
Thanks

Liem First, thank you for being a Gold Member and for supporting the site. I appreciate it, and I'm glad to hear that the videos have helped you improve your database.

I wanted to jump in with a couple of general comments that might help both you and anyone else reading this thread.

First, regarding the forums: this is exactly where technical questions belong. I receive far too many technical support emails every day to answer them individually, so if you have questions about building your database, please post them here in the forums. We have a fantastic group of moderators and experienced members who volunteer their time to help, and you'll often get several different perspectives on a problem.

Please remember, though, that the forums are staffed entirely by volunteers. Your membership gives you access to my videos and other membership benefits, but it doesn't include one-on-one consulting or guaranteed technical support. Everyone here helps because they enjoy helping others learn, and I'm very grateful for everything they do.

Another suggestion: try to narrow each question down to one specific problem. Over the course of these threads you've posted a very large number of screenshots. While I appreciate that you're trying to provide enough information, too much information can actually make it harder for people to find the real issue. A single screenshot of the relevant query, SQL statement, form, or VBA code is usually much more helpful than twenty screenshots of the entire database.

Also, if your example can be simplified into something everyone understands, it becomes much easier for people to help. For example, instead of using your actual business with trademark casings, try to reduce the problem to something like Customers, Orders, Products, and Order Details. That's why I use those examples in my videos. Everyone understands those relationships, so we can focus on the database problem instead of trying to understand the business rules first.

As for the technical side of your question, I think you've become focused on replacing every DLookup with a subquery. That's probably not the right goal. In many cases, the best solution isn't a subquery at all. It's a properly designed query using joins. Without knowing your complete database structure and exactly what your business rules are, it's difficult to say which approach is best.

I also believe the TechHelp video I made, DLookup Slow, covers the concept you're trying to learn. The important lesson isn't simply "replace DLookup with a subquery." It's understanding how to retrieve related data efficiently, and in many situations that means using joins instead of either DLookups or subqueries.

Finally, if what you're really need is someone to work directly with your database one-on-one, you might consider hiring an Access developer. I don't do consulting myself anymore, but there are several experienced developers listed on my Access Developer Network who may be able to help.

Keep asking questions, but try to make each one as focused as possible. That will make it much easier for everyone here to help you, and you'll get better answers much more quickly.

Thanks, and keep learning!